Window Aggregate SQL Functions

Like Grid Aggregate functions, all Window Aggregate functions take an integer interval argument and produce a result sequence containing the calculated aggregate for each interval. The difference in how Window Aggregate functions determine their sliding window interval is described in the Window versus Grid Aggregate page. The result of all Window Aggregate functions is returned in the result sequence as the calculated aggregate for each interval. As with Grid Aggregate functions, the object's sequence is split into intervals based on the value of the interval argument which determines the maximum number of elements in the group, though there may be fewer. So the input sequence will be divided into blocks of interval elements on which the operation is performed.

seq_window_agg_max( input, interval )	Returns the sequence with the maximum value for each window of elements
seq_window_agg_min( input, interval )	Returns the sequence with the minimum value for each window of elements
seq_window_agg_sum( input, interval )	Returns the sequence with the sum of each window of elements
seq_window_agg_avg( input, interval )	Returns the sequence with the average of each window of elements
seq_window_agg_var( input, interval )	Returns the sequence with the variance of each window of elements
seq_window_agg_var_samp( input, interval)	Returns the sequence with the sample variance of each window of elements
seq_window_agg_dev( input, interval)	Returns the sequence with the standard deviation of each window of elements
seq_window_agg_dev_samp( input, interval)	Returns the sequence with the sample standard deviation of each window of elements
seq_window_agg_ema( input, interval)	Returns the sequence with the exponential moving average of each window of elements
seq_window_agg_atr(...)	Returns the sequence with the average true range for each window 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 8. For instance, with scripts/financial as the current working directory, simply type:

     
    g 8
     

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 12 elements due to the "sliding blocks of 7" (please see the Window v Grid Aggregate Functions page for further details).

         
    -- 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, _sum, _avg, _var, _dev
            
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_window_agg_max(volume@Q1_13, 7) as "max_Q1_13",
        seq_window_agg_min(volume@Q1_13, 7) as "min_Q1_13",
        seq_window_agg_sum(volume@Q1_13, 7) as "sum_Q1_13",
        seq_window_agg_avg(volume@Q1_13, 7) as "avg_Q1_13",
        seq_window_agg_var(volume@Q1_13, 7) as "var_Q1_13",
        seq_window_agg_dev(volume@Q1_13, 7) as "dev_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol  
    Q1_13{} 
      Volume_Q1_13{} 
       max_Q1_13{} 
          min_Q1_13{} 
          sum_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, 805, 805, 805, 805} 
           {0, 0, 0, 0, 0, ...<2 element(s)>..., 45, 45, 48, 48, 48}       
    {335, 1143, 1188, 1590, 2322, ...<2 element(s)>..., 3290, 2867, 3242, 3207, 2544}       
    {47.8571428571429, 163.285714285714, 169.714285714286, 227.142857142857, 331.714285714286, 
      ...<2 element(s)>..., 470, 409.571428571429, 463.142857142857, 458.142857142857, 363.428571428571}   
    {13741.8367346939, 82636.2040816327, 80784.7755102041, 81080.1224489796, 99185.9183673469, 
      ...<2 element(s)>..., 94120.8571428571, 75180.8163265306, 53338.9795918367, 54100.4081632653, 56048.8163265306}
    {117.225580547481, 287.465135419293, 284.226627025344, 284.745715418125, 314.937959552905, 
      ...<2 element(s)>..., 306.791227291227, 274.191203955434, 230.952331860574, 232.594944406075, 236.74631217092}
     
 
    -- _ema (Exponential Moving Average)
            
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        volume@Q1_13 as "Volume_Q1_13",
        seq_window_agg_ema(volume@Q1_13, 7) as "ema_Q1_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol  
    Q1_13{} 
      Volume_Q1_13{} 
       ema_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, 453.25, 351.1875, 363.890625, 455.91796875, ...<2 element(s)>..., 
      462.527893066406, 443.145919799805, 437.359439849854, 419.76957988739, 332.077184915543}