SQL Sequence Group Aggregate First and Last Functions

The seq_group_agg_first() and seq_group_agg_last() functions extract the values of the first and last elements for the groups determined by the group_by argument.

Following is an example script demonstrating the seq_group_agg_first() and seq_group_agg_last() functions:

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

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

Volume_Jan_13	first	last	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