Grid Aggregate SQL Functions

All Grid Aggregate functions take an integer interval argument and produce a result sequence containing the calculated aggregate for each interval.

seq_grid_agg_max( input, interval )	Returns the sequence with the maximum value for each interval of elements
seq_grid_agg_min( input, interval )	Returns the sequence with the minimum value for each interval of elements
seq_grid_agg_sum( input, interval )	Returns the sequence with the sum of each interval of elements
seq_grid_agg_avg( input, interval )	Returns the sequence with the average of each interval of elements
seq_grid_agg_var( input, interval )	Returns the sequence with the variance of each interval of elements
seq_grid_agg_var_samp( input, interval )	Returns the sequence with the sample variance of each interval of elements
seq_grid_agg_dev( input, interval )	Returns the sequence with the standard deviation of each interval of elements
seq_grid_agg_dev_samp( input, interval )	Returns the sequence with the sample standard deviation of each interval of 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 7. For instance, with scripts/financial as the current working directory, simply type:

     
    g 7
     

Following is an example code snippet demonstrating some of the grid_agg()functions for the first quarter of 2013. Note that the interval argument is specified to give intervals of 7. Since the number of Quote records in Q1 is 12, this produces result sequences of two elements (12 / 7 = 2).

         
    -- Show volumes for Q1 2013
    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
            
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_grid_agg_max(volume@Q1_13, 7) as "max_Q1_13",
        seq_grid_agg_min(volume@Q1_13, 7) as "min_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol  
    Q1_13{} 
      Volume_Q1_13{} 
       max_Q1_13{} 
          min_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}  
    {808, 450}      
    {45, 69}
     
 
    -- _sum, _avg
            
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_grid_agg_sum(volume@Q1_13, 7) as "sum_Q1_13",
        seq_grid_agg_avg(volume@Q1_13, 7) as "avg_Q1_13"
    FROM Quote WHERE symbol='SYM0';
 
    symbol 
     Q1_13{}   
    Volume_Q1_13{}   
    sum_Q1_13{} 
          avg_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}  
    {3175, 1691}    
    {453.571428571429, 338.2}
     
     
    -- _var, _dev
            
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_grid_agg_var(volume@Q1_13, 7) as "var_Q1_13",
        seq_grid_agg_dev(volume@Q1_13, 7) as "dev_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol 
     Q1_13{}  
    Volume_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} 
     {96397.387755102, 18935.76}
    {310.47928715955, 137.607267249953}