SQL Sequence Diff Function

The seq_diff() function returns a sequence of values representing the difference of element n minus element n-1.

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

 
    INSERT INTO SimpleSequence(testNumber,iVal1)
    VALUES(2,'{4,9,2,9,1,4,9,7,5,3}');
     
    SELECT iVal1, seq_diff(iVal1) AS "diff" FROM SimpleSequence WHERE testNumber = 2;
     
    iVal1{}
    diff{}
    ----------------------------------------------------------------------
    {4, 9, 2, 9, 1, 4, 9, 7, 5, 3}
    {0, 5, -7, 7, -8, 3, 5, -2, -2, -2}
     

The elements in the result sequence are obtained as follows:

n	n-1	result
4	-	0
9	4	9 - 4 = 5
2	9	2 - 9 = -7
9	2	9 - 2 = 7
1	9	1 - 9 = -8
4	1	4 - 1 = 3
9	4	9 - 4 = 5
7	9	7 - 9 = -2
5	7	5 - 7 = -2
3	5	3 - 5 = -2

(Note that the first element in the return sequence is just the place holder value of 0 since there is not n-1 value to compare it to.)

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