Analytics Functions Example 5: Filter

Filter Sequence Elements

Filter elements of a sequence - extract the days of February 2013 when the close value was greater than open:

 
    SELECT symbol, seq_search(day, 20130201, 20130228) as Feb_2013,
        close@Feb_2013 as Feb_Close, open@Feb_2013 as Feb_Open,
        seq_filter(Feb_Close > Feb_Open, Feb_2013) as "up_Feb",
        seq_filter(Feb_Close > Feb_Open, Feb_Open) as "up_Open",
        seq_filter(Feb_Close > Feb_Open, Feb_Close) as "up_Close"
    FROM Quote WHERE symbol='IBM';
     
    symbol
    Feb_2013{}
    Feb_Close{}
    Feb_Open{}
    up_Feb{}
    up_Open{}
    up_Close{}
    ------------------------------------------------------------------------------
    IBM
    {20130201, 20130204, 20130205, 20130206, 20130207, 20130208,
    20130211, 20130212, 20130213, 20130214, 20130215, 20130219,
    20130220, 20130221, 20130222, 20130225, 20130226, 20130227,
    20130228}
    {205.179993, 203.789993, 202.789993, 201.020004, 199.740005, 201.679993,
    200.160004, 200.039993, 200.089996, 199.649994, 200.979996, 200.320007,
    199.309998, 198.330002, 201.089996, 197.509995, 199.139999, 202.330002,
    200.830002}
    {204.649994, 204.190002, 204.309998, 200.389999, 200.619995, 199.970001,
    200.979996, 200.009995, 200.649994, 199.729996, 199.979996, 200.600006,
    200.619995, 198.630005, 199.229996, 201.669998, 198.630005, 198.889999,
    202.179993}
    {20130201, 20130206, 20130208, 20130212, 20130215, 20130222,
    20130226, 20130227}
    {204.649994, 200.389999, 199.970001, 200.009995, 199.979996, 199.229996,
    198.630005, 198.889999}
    {205.179993, 201.020004, 201.679993, 200.039993, 200.979996, 201.089996,
    199.139999, 202.330002}
     

The up_Feb sequence contains the dates when the close value was greater than the open for February of 2013. To illustrate we have highlighted the date value for February 8, 2013 in the up_Feb sequence which corresponds with the 3rd element of the up_Open and up_Close sequences and that satisfy the filter condition; i.e. 201.68 > 199.97.

(Note that the repeated calls of function seq_filter() are necessary to produce the filtered open and close sequences which make the output more easily understandable, as the Feb_2013, Feb_Open and Feb_Close sequences (which cannot be suppressed) somewhat obscure the essential information.)

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 5