Analytics Functions Example 8: Window Aggregate

Window Aggregate

Compute the 5 day moving average close for March 2013:

 
    SELECT symbol, 
        seq_search(day, 20130225, 20130331) as Mar_2013,
        close@Mar_2013 as "close",
        seq_window_agg_avg(close@Mar_2013, 5) as "5-day-average"
    FROM Quote WHERE symbol='IBM';
     
    symbol
    Mar_2013{}
    close{}
    5-day-average{}
    ------------------------------------------------------------------------------
    IBM
    {20130225, 20130226, 20130227, 20130228,
    20130301, 20130304, 20130305, 20130306, 20130307,
    20130308, 20130311, 20130312, 20130313, 20130314,
    20130315, 20130318, 20130319, 20130320, 20130321,
    20130322, 20130325, 20130326, 20130327, 20130328}
    {197.509995, 199.139999, 202.330002, 200.830002,
    202.910004, 205.190002, 206.529999, 208.380005, 209.419998,
    210.380005, 210.080002, 210.550003, 212.059998, 215.800003,
            
    214.919998, 213.210007, 213.440002, 215.059998, 212.259995,
    212.080002, 210.740005, 212.360001, 210.889999, 213.300003}
    {39.501999, 79.329999, 119.795999, 159.962000,
    200.544000, 202.080002, 203.558002, 204.768002, 206.486002,
    207.980002, 208.958002, 209.762003, 210.498001, 211.774002,
    212.682001, 213.308002, 213.886002, 214.486002, 213.778000,
    213.210001, 212.716000, 212.500000, 211.666000, 211.874002}
     

Note that the first 4 average values are invalid because there are not 5 values to average, so the following computations do not give true 5-day averages:

 
    197.509995 / 5 =  39.501999
    (197.509995 + 199.139999) / 5 = 79.329999
    (197.509995 + 199.139999 + 202.330002) / 5 = 119.795999
    (197.509995 + 199.139999 + 202.330002 + 200.830002) / 5 = 159.962000
     

(See page Window versus Grid Aggregate Functions for an explanation of “incomplete intervals”.) This is why we defined the date range as 20130225 thru 20130331 so that the last 4 days of February could be discarded. The 5-day average for March 1 and all successive days of March are however valid. For example, we can verify that the 5 values up to and including the trade date of March 14 compute the average as follows:

• The date 20130314 in sequence Mar_2013 is the 14th element.
• The five close values (highlighted) that comprise the 5-day window are for the five trade dates preceding and including March 14th: 20130308, 20130311, 20130312, 20130313, and 20130314.
• The value in sequence 5-day-average (highlighted) that corresponds to March 14th (the 14th element) is then calculated as follows:

 
    (210.380005 + 210.080002 + 210.550003 + 212.059998 + 215.800003) / 5 = 211.774002
     

To run this example

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    x 8