SQL Sequence Stretch Function

The seq_stretch() function is often used when calculating “split” adjusted prices, seq_stretch() “stretches” a sequence by copying elements from a third sequence dVal3 into the result sequence based on a comparison of the values of the two input time series ts1 and ts2. It repeats elements of dVal3 while the value in ts2 is greater than that of ts1, until the result sequence has the same number of elements as ts1.

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

 
    INSERT INTO SimpleSequence(testNumber,ts1,ts2,dVal3)
    VALUES(5,'{1,2,3,4,5}','{2,4}','{1.1,2.2}');
     
    SELECT ts1,ts2,dVal3,seq_stretch(ts1,ts2,dVal3) AS "stretch" FROM SimpleSequence WHERE testNumber=5;
     
    ts1{}
    ts2{}
    dVal3{}
    stretch{}
    ------------------------------------------------------------------------------
    {1, 2, 3, 4, 5}
    {2, 4}
    {1.100000, 2.200000}
    {1.100000, 2.200000, 2.200000, 1.000000, 1.000000}
     

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

0. The first element of ts2 is greater than the corresponding element of ts1 (2>1), so the first element from dVal3 (1.1) is copied into the result sequence.

1. The second element of ts1 is 2 which is equal to that of the first element of ts2. So the next element of ts2 is compared. Now the value in ts2 is greater than ts1 (4>2) so the second element of dVal3 (2.2) is copied into the result sequence.

2. The second element of ts2 is greater than the third element of ts1 (4>3), so the second element of dVal3 is repeated in the result sequence.

3. The second element in ts2 is equal to the fourth element in ts1. But there are no more elements in ts2 to compare, so the “filler” value (1.0) is copied into the result sequence.

4. Likewise, since there are no more elements in ts2 to compare to the remaining elements in ts1, the filler value of (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