Analytics Functions Example 10: Group Aggregate

Group Aggregate Sum

Get the total volume for each month of the first quarter of 2013:

     
    SELECT symbol, 
        seq_search(day, 20130101, 20130331) as trade_date,
        volume@trade_date as "volume",
        seq_group_agg_sum(volume@trade_date, trade_date/100) as "month_vol"
    FROM Quote WHERE symbol='IBM';
     
    symbol
    trade_date{}
    volume{}
    month_vol{}
    ------------------------------------------------------------------------------
    IBM
    {20130102, 20130103, 20130104, 20130107, 20130108,
    20130109, 20130110, 20130111, 20130114, 20130115,
    20130116, 20130117, 20130118, 20130122, 20130123,
    20130124, 20130125, 20130128, 20130129, 20130130,
    20130131,
    20130201, 20130204, 20130205, 20130206, 20130207,
            
    20130208, 20130211, 20130212, 20130213, 20130214,
            
    20130215, 20130219, 20130220, 20130221, 20130222,
            
    20130225, 20130226, 20130227, 20130228,
            
    20130301, 20130304, 20130305, 20130306, 20130307,
    20130308, 20130311, 20130312, 20130313, 20130314,
    20130315, 20130318, 20130319, 20130320, 20130321,
    20130322, 20130325, 20130326, 20130327, 20130328}
    {4234226, 3644876, 3380159, 2862276, 3028071,
    3211997, 3608049, 3880567, 4172922, 4172034,
    2962820, 3884254, 4559773, 7159219, 12530410,
    4447108, 3358887, 2823615, 3617058, 2998524,
    3091406,
    3371154, 3188819, 3636837, 3624103, 3076793,
            
    2893254, 2944651, 2461779, 2169757, 3294126,
            
    3627887, 2998317, 3715311, 3923051, 3107976,
            
    3845476, 3391562, 4185545, 4689998,
            
    3309434, 3693365, 3807706, 3594899, 3884317,
    3700786, 3049701, 3592096, 3356946, 5505584,
    7936544, 3006125, 3198577, 3020648, 5830566,
    3031457, 3242463, 2300240, 3223381, 3752999}
    {87628251, 64146396, 76037834}
     

We can analyze the output above as follows:

• The output values for sequences trade_date and volume are divided by month for convenience.
• The sequence month_vol shows the “group_aggregate sum“ for each of the months (calculated as trade_date/100, eg. 20130101/100 = 201301).
• The volumes for the month of February are totaled as follows to produce the corresponding value 64146396 in sequence month_vol:

Trade Date	Volume
20130201	3371154
20130204	3188819
20130205	3636837
20130206	3624103
20130207	3076793
20130208	2893254
20130211	2944651
20130212	2461779
20130213	2169757
20130214	3294126
20130215	3627887
20130219	2998317
20130220	3715311
20130221	3923051
20130222	3107976
20130225	3845476
20130226	3391562
20130227	4185545
20130228	4689998
Feb. Total	64146396

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 10