SQL Sequence Covariance and Correlation Functions

The seq_cov(), seq_corr() standard statistical functions calculate a scalar result of type double that indicates the strength of correlation between the two sequences. Covariance results of 0 indicate that there is no correlation between the two sequences; if seq_cov() returns a positive value (as in the example above) it indicates that sequence 2 tends to increase when sequence 1 increases; a negative value indicates that sequence 2 tends to decrease when sequence 1 increases.

Likewise a correlation result of 0 indicates that the two sequences are completely uncorrelated; if seq_corr() returns a positive value (as in the example above) it indicates that sequence 2 tends to increase when sequence 1 increases; a negative value indicates that sequence 2 tends to decrease when sequence 1 increases. The result value from seq_corr() will be between -1 and 1; a value of 1 or -1 indicates that the two sequences are perfectly correlated.

Following is an example script demonstrating the seq_cov() and seq_corr() functions:

 
    INSERT INTO SimpleSequence(testNumber,dVal1,dVal2)
    VALUES(6,'{2,3,5,7}','{3,3,5,9}');
     
    SELECT dVal1,dVal2,seq_cov(dVal1,dVal2) AS "cov" FROM SimpleSequence WHERE testNumber=6;
    SELECT dVal1,dVal2,seq_corr(dVal1,dVal2) AS "corr" FROM SimpleSequence WHERE testNumber=6;
     
    dVal1{}
    dVal2{}
    cov
    ------------------------------------------------------------------------------
    {2.000000, 3.000000, 5.000000, 7.000000}
    {3.000000, 3.000000, 5.000000, 9.000000}
    4.500000
            
     
    dVal1{}
    dVal2{}
    corr
    ------------------------------------------------------------------------------
    {2.000000, 3.000000, 5.000000, 7.000000}
    {3.000000, 3.000000, 5.000000, 9.000000}
    0.956689
            
         

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 3