SQL Sequence Asof_Join Function

The seq_asof_join() function copies elements of a sequence that are “close to” specified values that represent “as of” timestamps. So elements of the third argument dVal3 are copied into the result sequence which correspond with the value in ts2 that are closest (while still being less or equal) to a value in ts1. So argument ts1 represents the timestamps of interest, ts2 contains the available timestamps corresponding to the data values in dVal3. The number of elements in dVal3 must be the same as in ts2 and the number of elements in the result sequence will be equal to the number of timestamps in ts1.

Following is an example script demonstrating the seq_asof_join() function:

 
    INSERT INTO SimpleSequence(testNumber,ts1,ts2,dVal3)
    VALUES(7,'{4,9}','{1,3,6,10}','{0.1,0.3,0.6,1.0}');
     
    SELECT ts1,ts2,dVal3,seq_asof_join(ts1,ts2,dVal3) AS "asof_join" FROM SimpleSequence WHERE testNumber=7;
     
    ts1{}
    ts2{}
    dVal3{}
    asof_join{}
    ------------------------------------------------------------------------------
    {4, 9}
    {1, 3, 6, 10}
    {0.100000, 0.300000, 0.600000, 1.000000}
    {0.300000, 1.000000}
     

So, in the example above the result sequence is obtained as follows:

0. The first element of ts1 (4) is closest to element 1 in ts2 (3), so element 1 of dVal3 (0.3) is copied into the result sequence.

1. The next element of ts1 (9) is closest to element 3 in ts2 (10), so element 3 of dVal3 (1.0) is copied into the result sequence.

Sample script

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

     
    f 5