SQL Sequence Group Aggregate Var and Dev Functions

The seq_group_agg_var() and seq_group_agg_dev() functions compute the variance and standard deviation for the groups determined by the group_by argument.

Following is an example script demonstrating the seq_group_agg_var() and seq_group_agg_dev() functions:

 
    SELECT symbol,
        seq_search(day, 20130101, 20130131) as Jan_13,
        seq_group_agg_var(volume@Jan_13, Jan_13/7) as "week_var",
        seq_group_agg_dev(volume@Jan_13, Jan_13/7) as "week_dev"
    FROM Quote WHERE symbol = 'SYM0';
     
    symbol
    Jan_13{}
    week_var{}
    week_dev{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
    {0.000000, 145542.250000, 0.000000, 0.000000, 143262.250000}
    {0.000000, 381.500000, 0.000000, 0.000000, 378.500000}
     

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

Volume_Jan_13	var	dev	Week
335	0.000000	0.000000	1
808	145542.250000	381.500000	2
45			2
402	0.000000	0.000000	3
732	0.000000	0.000000	4
48	143262.250000	378.500000	5
805			5

Note that the huge variance and standard deviation values for weeks 2 and 5 indicate the great difference between the volume for the two trade dates in that week:

week 2: {808,45} -> 145542.25, 381.5

week 5: {48,805} -> 143262.25, 378.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