Analytics Functions Example 9: Grid Aggregate

Grid Aggregate

Get the minimum close value for 5-day intervals in March 2013:

     
    SELECT symbol, 
        seq_search(day, 20130301, 20130331) as Mar_2013,
        close@Mar_2013 as "close",
        seq_grid_agg_min(close@Mar_2013, 5) as "min"
    FROM Quote WHERE symbol='IBM';
     
    symbol
    Mar_2013{}
    close{}
    min{}
    ------------------------------------------------------------------------------
    IBM
    {20130301, 20130304, 20130305, 20130306, 20130307,
    20130308, 20130311, 20130312, 20130313, 20130314,
    20130315, 20130318, 20130319, 20130320, 20130321,
    20130322, 20130325, 20130326, 20130327, 20130328}
    {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}
    {202.910004, 210.080002, 212.259995, 210.740005}
     

Note that, whereas the window aggregate functions produce values for a “sliding” block of sequence elements, the grid aggregate functions produce output for successive blocks of elements. We can analyze the output above as follows:

• the sequences Mar_2013 and close are formatted in groups of 5 for convenience.
• note that there are 4 values (highlighted) in the sequence min that correspond to the 4 blocks of 5 trades (20 trade dates divided by 5).
• these min values correspond to the highlighted values in sequence close, which are the lowest values for each block of five trades.

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 9