Group Aggregate SQL Functions

All Group Aggregate functions take an input and group_by sequence argument and produce a result sequence containing the calculated aggregate for each group. The object's sequence is split into groups based on the values in the group_by sequence which is expected to be ordered.

seq_group_agg_count( input, group_by )	Returns a sequence with the count of the number of elements in each group
seq_group_agg_max( input, group_by )	Returns a sequence with the maximum value for each group of elements
seq_group_agg_min( input, group_by )	Returns a sequence with the minimum value for each group of elements
seq_group_agg_first( input group_by )	Returns a sequence with the first element of each group
seq_group_agg_last( input, group_by )	Returns a sequence with the last element of each group
seq_group_agg_sum( input, group_by )	Returns a sequence with the sum of each group of elements
seq_group_agg_avg( input, group_by )	Returns a sequence with the average of each group of elements
seq_group_agg_var( input, group_by )	Returns a sequence with the variance of each group of elements
seq_group_agg_var_samp( input, group_by )	Returns a sequence with the sample variance of each group of elements
seq_group_agg_dev( input, group_by )	Returns a sequence with the Standard Deviation of each group of elements
seq_group_agg_dev_samp( input, group_by )	Returns a sequence with the Sample Standard Deviation of each group of elements
seq_group_agg_approxdc( group_by )	Returns a sequence with the approximate count of distinct values for each group

Example

As explained in Analytics Functions Library Examples page, the data in the following example is taken from historical values from 2013 for IBM. The data is loaded into xSQL by running various scripts in directory samples/xsql/scripts/financial. To reproduce the example below run the g.bat (or g.sh on Linux systems) specifying the example number 6. For instance, with scripts/financial as the current working directory, simply type:

     
    g 6
     

Following is an example code snippet demonstrating some of the group_agg()functions for January 2013 (note that the group_by argument is the sequence of trade dates divided by 7 to give grouping by 7-day intervals):

         
    -- Show volumes for January 2013
    SELECT symbol, seq_search(day, 20130101, 20130131) as Jan_13,
        volume@Jan_13 as "Volume_Jan_13"
    FROM Quote WHERE symbol = 'SYM0';
     
    symbol  
    Jan_13{}  
    Volume_Jan_13{}
    -------------------------------------------------------------------------
    SYM0    
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129}  
    {335, 808, 45, 402, 732, 48, 805}
     
     
    -- _max, _min
            
    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}
     
 
    -- _first, _last
            
    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}
     
 
    -- _sum, _avg
            
    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, 426.5, 402, 732, 426.5}
     
 
    -- _var, _dev
            
    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, 145542.25, 0, 0, 143262.25} 
    {0, 381.5, 0, 0, 378.5}
     
     
    -- _count, _approxdc
            
    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}