SQL Sequence Cross Function

The seq_cross() function returns a sequence of values indicating where the input sequence crosses zero. The second argument specifies whether to indicate the position where the sequence crosses zero in the positive or negative direction: a negative value indicates that zero-crossings in the downward direction are desired; a positive value indicates that zero-crossings in the upward direction are desired; a value of 0 indicates zero-crossings in either direction are desired.

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

 
    INSERT INTO SimpleSequence(testNumber,iVal1,iVal2)
    VALUES(8,'{-1,0,1,-3,2}');
     
    SELECT iVal1,seq_cross(iVal1,-1) AS "cross-neg",seq_cross(iVal1,1) AS "cross-pos",seq_cross(iVal1,0) AS "cross-0"
    FROM SimpleSequence WHERE testNumber=8;
     
    iVal1{}
    cross-neg{}
    cross-pos{}
    cross-0{}
    --------------------------------------------------
    {-1, 0, 1, -3, 2}
    {3}
    {1, 4}
    {1, 3, 4}
     

So, in the example above the result sequences are obtained as follows:

• cross-neg{}: The first three elements of iVal1 (-1,0,1) are increasing values, but the element 3 (-3) crosses zero in the negative direction. So the position 3 is copied to the result sequence.
• cross-pos{}: The first two elements of iVal1 (-1,0) show that at element 1 (0) is the first zero crossing in the positive direction. So the position 1 is copied to the result sequence.The elements 3 and 4 of iVal1 (-3,2) show that at element 4 (2) is the next zero crossing in the positive direction. So the position 4 is copied to the result sequence.
• cross-0{}: The result sequence when the second argument to seq_cross() is 0 are simply the positions of both negative and positive zero-crossings. So the positive crossing at element 1 and 4 are combined with the negative crossing at element 3 (as obtained above) are copied in ascending order (1,3,4) to 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