SQL Sequence Aggregate Hash Aggregate Max, Min, Sum and Avg Functions

The SQL seq_hash_agg_max(), seq_hash_agg_min(), seq_hash_agg_sum(), seq_hash_agg_avg() functions return sequences with the max, min, sum and average for each group.

Following is an example script demonstrating these functions:

 
    SELECT symbol, volume, close,
        seq_hash_agg_max(close, volume/10) as max_by_volume,
        seq_hash_agg_min(close, volume/10) as "min",
        seq_hash_agg_sum(close, volume/10) as "sum",
        seq_hash_agg_avg(close, volume/10) as "avg",
        seq_hash_group_by(max_by_volume) as "group_by"
    FROM Quote WHERE symbol='SYM0';
     
    symbol  volume  close   max_by_volume   min     sum     avg     group_by
    ------------------------------------------------------------------------------
    SYM0    {335, 808, 45, 402, 732, 48, 805, 450, 385, 420, 367, 69, 77}
    {62.830002, 45.790001, 74.730003, 53.459999, 67.870003, 50.369999, 80.320000,
      29.940001, 25.920000, 37.279999, 80.919998, 57.730000, 43.849998}
    {80.320000, 67.870003, 43.849998, 25.920000, 74.730003, 53.459999, 57.730000,
      29.940001, 62.830002, 37.279999, 80.919998}
    {45.790001, 67.870003, 43.849998, 25.920000, 50.369999,53.459999, 57.730000,
      29.940001, 62.830002, 37.279999, 80.919998}
    {126.110001, 67.870003, 43.849998, 25.920000, 125.100002, 53.459999, 57.730000,
      29.940001, 62.830002, 37.279999, 80.919998}
    {63.055000, 67.870003, 43.849998, 25.920000, 62.550001, 53.459999, 57.730000,
      29.940001, 62.830002, 37.279999, 80.919998}
    {80, 73, 7, 38, 4, 40, 6, 45, 33, 42, 36}
     

Sample script

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    g 9