SQL Sequence Group Aggregate Sum and Avg Functions

The seq_group_agg_sum() and seq_group_agg_avg() functions compute the sum and average for the groups determined by the group_by argument.

Following is an example script demonstrating the seq_group_agg_sum() and seq_group_agg_avg() functions:

 
    SELECT symbol,
        seq_search(day, 20130101, 20130131) as Jan_13,
        seq_group_agg_sum(volume@Jan_13, Jan_13/7) as "week_sum",
        seq_group_agg_avg(volume@Jan_13, Jan_13/7) as "week_avg"
    FROM Quote WHERE symbol = 'SYM0';
     
    symbol
    Jan_13{}
    week_sum{}
    week_avg{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
    {335, 853, 402, 732, 853}
    {335.000000, 426.500000, 402.000000, 732.000000, 426.500000}
     

The following table illustrates how the first and last values correspond to the 5 groups (weeks):

Volume_Jan_13	sum	avg	Week
335	335	335.000000	1
808	853	426.500000	2
45		(=853/2)	2
402	402	402.000000	3
732	732	732.000000	4
48	853	426.500000	5
805		(=853/2)	5

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 6