SQL Sequence Group Aggregate Count and Approxdc Functions

The seq_group_agg_count() and seq_group_agg_approxdc() functions compute the count of elements and the approximate number of distinct values for the groups determined by the group_by argument.

Following is an example script demonstrating the seq_group_agg_count() and seq_group_agg_approxdc() functions:

 
    SELECT symbol,
        seq_search(day, 20130101, 20130131) as Jan_13,
        seq_group_agg_count(Jan_13/7) as "week_count",
        seq_group_agg_approxdc(volume@Jan_13, Jan_13/7) as "week_approxdc"
    FROM Quote WHERE symbol = 'SYM0';
     
    symbol
    Jan_13{}
    week_count{}
    week_approxdc{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}
    {1, 2, 1, 1, 2}
    {1, 2, 1, 1, 2}
     

As has been seen in the other Group_Agg functions, weeks 1, 3 and 4 have a single element, while weeks 2 and 5 have two distinct elements as the results above show.

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