SQL Sequence Group Aggregate Max and Min Functions

The seq_group_agg_max() and seq_group_agg_min() functions extract the maximum and minimum values for the groups determined by the group_by argument.

Following is an example script demonstrating the seq_group_agg_max() and seq_group_agg_min() functions:

 
    SELECT symbol,
        seq_search(day, 20130101, 20130131) as Jan_13,
        seq_group_agg_max(volume@Jan_13, Jan_13/7) as "week_max",
        seq_group_agg_min(volume@Jan_13, Jan_13/7) as "week_min"
    FROM Quote WHERE symbol = 'SYM0';
     
    symbol
    Jan_13
    week_max
    week_min
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
    {335, 808, 402, 732, 805}
    {335, 45, 402, 732, 48}
     

In the above example the boolean sequence has ‘true’ values in positions 1 and 3. So the values -13 and -19 in the corresponding positions in the input values sequence are copied to the result sequence

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

Volume_Jan_13	max	min	Week
335	335	335	1
808	808		2
45		45	2
402	402	402	3
732	732	732	4
48		48	5
805	805		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