SQL Sequence Grid Aggregate Sum and Avg Functions

The seq_grid_agg_sum() and seq_grid_agg_avg() functions return sequences with two 7-element blocks whose elements correspond with the values in the Sample Volume Blocks.

Following is an example script demonstrating the seq_grid_agg_sum() and seq_grid_agg_avg() functions:

 
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_grid_agg_sum(volume@Q1_13, 7) as "sum_Q1_13",
        seq_grid_agg_avg(volume@Q1_13, 7) as "avg_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol
    Q1_13{}
    Volume_Q1_13{}
    sum_Q1_13{}
    avg_Q1_13{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129,
    20130213, 20130214, 20130216, 20130311, 20130326}
    {335, 808, 45, 402, 732, 48, 805,
    450, 385, 420, 367, 69}
    {3175, 1691}
    {453.571429, 338.200000}
     

To verify:

• seq_grid_agg_sum:

block 1: 335 + 808 + 45 + 402 + 732 + 48 + 805 = 3175

block 2: 450 + 385 + 420 + 367 + 69 = 1691

• seq_grid_agg_avg:

block 1: 3175 / 7 = 453.571429

block 2: 1691 / 5 = 338.200000

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 7