Cumulative Aggregate SQL Functions

All Cumulative Aggregate functions produce a result sequence each element of which is the result of the specified operation on all of the preceding elements.

seq_cum_agg_max( input )	Returns a sequence with the cumulative maximum: each element is the maximum value of all the preceding elements
seq_cum_agg_min( input )	Returns a sequence with the cumulative minimum: each element is the minimum value of all the preceding elements
seq_cum_agg_sum( input )	Returns a sequence with the cumulative sum: each element is the sum of all the preceding elements
seq_cum_agg_prd( input )	Returns a sequence with the cumulative product: each element is the product of all the preceding elements
seq_cum_agg_avg( input )	Returns a sequence with the cumulative average: each element is the average of all the preceding elements
seq_cum_agg_var( input )	Returns a sequence with the cumulative variance: each element is the variance of all the preceding elements
seq_cum_agg_var_samp( input )	Returns a sequence with the cumulative sample variance: each element is the cumulative sample variance of all the preceding elements
seq_cum_agg_dev( input )	Returns a sequence with the cumulative standard deviation: each element is the cumulative standard deviation of all the preceding elements
seq_cum_agg_dev_samp( input )	Returns a sequence with the sample standard deviation: each element is the cumulative sample standard deviation of all the preceding elements

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 10. For instance, with scripts/financial as the current working directory, simply type:

     
    g 10
     

Following is an example code snippet demonstrating some of the cum_agg()functions for the first quarter of 2013.

 
    -- Show Q1 Volumes
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13"
    FROM Quote WHERE symbol = 'SYM0';
     
    symbol 
     Q1_13{}   
    Volume_Q1_13{}
    -------------------------------------------------------------------------
    SYM0    
    {20130101, 20130104, 20130106, 20130110, 20130123, ...<2 element(s)>..., 
      20130213, 20130214, 20130216, 20130311, 20130326}      
    {335, 808, 45, 402, 732, ...<2 element(s)>..., 450, 385, 420, 367, 69}
     
     
    -- -- _max, _min, _sum, _prd, _avg, _var, _dev
            
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_cum_agg_max(volume@Q1_13) as "max_Q1_13",
        seq_cum_agg_min(volume@Q1_13) as "min_Q1_13",
        seq_cum_agg_sum(volume@Q1_13) as "sum_Q1_13",
        seq_cum_agg_prd(volume@Q1_13) as "prd_Q1_13",
        seq_cum_agg_avg(volume@Q1_13) as "avg_Q1_13",
        seq_cum_agg_var(volume@Q1_13) as "var_Q1_13",
        seq_cum_agg_dev(volume@Q1_13) as "dev_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol 
     Q1_13{}
      Volume_Q1_13{} 
       max_Q1_13{} 
          min_Q1_13{}       
    sum_Q1_13{}       
    prd_Q1_13{}       
    avg_Q1_13{}       
    var_Q1_13{}       
    dev_Q1_13{}
    -------------------------------------------------------------------------
    SYM0  
      {20130101, 20130104, 20130106, 20130110, 20130123, ...<2 element(s)>..., 
      20130213, 20130214, 20130216, 20130311, 20130326}      
    {335, 808, 45, 402, 732, ...<2 element(s)>..., 450, 385, 420, 367, 69} 
     {335, 808, 808, 808, 808, ...<2 element(s)>..., 808, 808, 808, 808, 808}        
    {335, 335, 45, 45, 45, ...<2 element(s)>..., 45, 45, 45, 45, 45}
    {335, 1143, 1188, 1590, 2322, ...<2 element(s)>..., 3625, 4010, 4430, 4797, 4866} 
          {335, 270680, 12180600, 4896601200, 3584312078400, ...<2 element(s)>..., 
      6983786198090545152, -4466948496734652416, 5449526889820250112, 7728008603400216576, -1722984502962053120} 
        {335, 492.666666666667, 380.75, 385, 442.833333333333, ...<2 element(s)>..., 
      440, 434.5, 433.181818181818, 427.666666666667, 400.076923076923} 
     {0, 109868, 103569, 84619, 94321, ...<2 element(s)>..., 95255, 85758, 77992, 71590, 73851}      
    {0, 331, 321, 290, 307, ...<2 element(s)>..., 308, 292, 279, 267, 271}