SQL Sequence Trend Function

The seq_trend()function compares pairs of element values to determine if the values are trending up or down. For a sequence of values V: if Vn-1 < Vn then the result sequence element n will be 1 to indicate a positive trend; if Vn-1 > Vn then the result sequence element n will be -1 to indicate a downward trend; if Vn-1 = Vn then Vn is compared to Vn-2, Vn-3 etc. until an inequality is found and element n of the result sequence will be 1 or -1 accordingly. Note that the first element in the result sequence is just the place holder value of 0 since there is no Vn-1 to compare it to.

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

 
    INSERT INTO SimpleSequence(testNumber,iVal1)
    VALUES(6,'{1,2,3,3,2,2,4,5,6,5}');
     
    SELECT iVal1, seq_trend(iVal1) AS "trend" FROM SimpleSequence WHERE testNumber = 6;
     
    iVal1{}
    trend{}
    ------------------------------------------------------------------------------
    {1, 2, 3, 3, 2, 2, 4, 5, 6, 5}
    {0, 1, 1, 1, -1, -1, 1, 1, 1, -1}
     

So the result values for this dataset are obtained as follows:

1) Element 0 is 0 since there is no element n-1 to compare to.

2) Element 1 is 1 indicating a positive trend because element 1 of the input sequence is greater than element 0; i.e. 2 > 1.

3) Element 2 is 1 indicating a positive trend because element 2 of the input sequence is greater than element 1; i.e. 3 > 2.

4) Element 3 is 1 indicating a positive trend because element 3 of the input sequence is equal to element 2; i.e. 3 = 3. So we compare element 3 to element 1 which is again 3 > 2.

5) Element 4 is -1 indicating a negative trend because element 4 of the input sequence is less than element 3; i.e. 2 < 3.

6) Element 5 is -1 indicating a negative trend because element 5 of the input sequence is equal to element 4; i.e. 2 = 2. So we compare element 4 to element 1 which is again 2 < 3.

7) Element 6 is 1 indicating a positive trend because element 6 of the input sequence is greater that element 5; i.e. 4 > 2.

8) Element 7 is 1 indicating a positive trend because element 7 of the input sequence is greater than element 6; i.e. 5 > 4.

9) Element 8 is 1 indicating a positive trend because element 8 of the input sequence is greater than element 7; i.e. 6 > 5.

10) Element 9 is -1 indicating a negative trend because element 9 of the input sequence is less than element 8; i.e. 5 < 6.

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 2