SQL Sequence Window Aggregate Max, Min, Sum, Avg, Var and Dev Functions

The seq_window_agg_max(), seq_window_agg_min(), seq_window_agg_sum(), seq_window_agg_avg(), seq_window_agg_var() and seq_window_agg_dev() functions return sequences with 12 elements which comprise the sliding blocks of 7 elements whose elements correspond with the values in the sample volume blocks. Note that the first 6 elements of the result sequences are invalid. (Please see the Window versus Grid Aggregate page for a more detailed explanation.)

Following is an example script demonstrating these functions:

 
    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",
        seq_window_agg_min(volume@Q1_13, 7) as "min"
        seq_window_agg_sum(close, 7) as "sum",
        seq_window_agg_avg(close, 7) as "avg",
        seq_window_agg_var(close, 7) as "var",
        seq_window_agg_dev(close, 7) as "dev"
    FROM Quote WHERE symbol='SYM0';
     
    symbol
    Q1_13{}
    Volume_Q1_13{}
    max_Q1_13{}
    min_Q1_13{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106, 20130110, 20130123, 20130125, 20130129,
      20130213, 20130214, 20130216, 20130311, 20130326}
    {335, 808, 45, 402, 732, 48, 805, 450, 385, 420, 367, 69}
 
    {335, 808, 808, 808, 808, 808,
      808, 808, 805, 805, 805, 805}
    {0, 0, 0, 0, 0, 0,
      45, 45, 45, 48, 48, 48}
    {335, 1143, 1188, 1590, 2322, 2370,
      3175, 3290, 2867, 3242, 3207, 2544}
    {47.857143, 163.285714, 169.714286, 227.142857, 331.714286, 338.571429,
      453.571429, 470.000000, 409.571429, 463.142857, 458.142857, 363.428571}
    {13741.836735, 82636.204082, 80784.775510, 81080.122449, 99185.918367, 94918.816327,
      96397.387755, 94120.857143, 75180.816327, 53338.979592, 54100.408163, 56048.816327} 
           {117.225581, 287.465135, 284.226627, 284.745715, 314.937960, 308.088975,
      310.479287, 306.791227, 274.191204, 230.952332, 232.594944, 236.746312}
     

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 7