Analytics Functions Example 3: Intervals

Sequence Interval

Extract data belonging to a specified interval (January 2013). The ordered sequence day is used to select the interval and then this interval is projected to the other (unordered) sequences open and close :

 
    SELECT flattened symbol, seq_search(day, 20130101, 20130131) as Jan_2013,
        open@Jan_2013 as "open", 
        close@Jan_2013 as "close"
    FROM Quote WHERE symbol='IBM';
     
    symbol  Jan_2013        open            close
    --------------------------------------------------
    IBM     20130102        194.089996      196.350006
    IBM     20130103        195.669998      195.270004
    IBM     20130104        194.190002      193.990005
    IBM     20130107        193.399994      193.139999
    IBM     20130108        192.919998      192.869995
    IBM     20130109        193.479996      192.320007
    IBM     20130110        192.649994      192.880005
    IBM     20130111        194.149994      194.449997
    IBM     20130114        192.820007      192.619995
    IBM     20130115        191.309998      192.500000
    IBM     20130116        192.000000      192.589996
    IBM     20130117        193.850006      193.649994
    IBM     20130118        194.029999      194.470001
    IBM     20130122        194.360001      196.080002
    IBM     20130123        203.500000      204.720001
    IBM     20130124        203.910004      204.419998
    IBM     20130125        204.449997      204.970001
    IBM     20130128        204.850006      204.929993
    IBM     20130129        204.339996      203.899994
    IBM     20130130        203.690002      203.520004
    IBM     20130131        203.320007      203.070007
     

Note the use of key word flattened to transform the sequence fields into a table so that the result set appears as individual rows for each set of corresponding sequence elements with the scalar field symbol value repeated on each row. (Also note that the bounds specified for seq_search() are “inclusive”.)

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 3