Analytics Functions Example 6: Correlation

Correlation

Perform a correlation calculation for two sequences - calculate the correlation between low and high values:

 
    SELECT symbol, high, low, seq_corr(low, high) as “correlation”
    FROM Quote WHERE symbol='IBM';
     
    symbol
    high{}
    low{}
    correlation
    ------------------------------------------------------------------------------
    IBM
    {196.350006, 196.289993, 194.460007, 193.779999, 193.300003, 193.490005,
    192.960007, 195.000000, 193.279999, 192.729996, 193.179993, 194.460007,
    195.000000, 196.080002, 208.580002, 205.059998, 205.179993, 206.220001,
    205.729996, 204.880005, 204.470001, 205.350006, 205.020004, 204.750000,
    201.289993, 200.910004, 202.089996, 201.949997, 200.740005, 200.949997,
    200.320007, 201.250000, 201.889999, 201.720001, 199.070007, 201.089996,
    202.490005, 199.899994, 202.750000, 203.119995, 202.940002, 205.190002,
    207.699997, 208.490005, 209.600006, 210.740005, 210.199997, 210.729996,
    212.360001, 215.860001, 215.899994, 214.500000, 215.119995, 215.820007,
    213.000000, 213.169998, 212.809998, 212.500000, 212.160004, 213.440002}
    {193.800003, 194.440002, 192.779999, 192.339996, 191.610001, 191.649994,
    191.279999, 192.899994, 191.750000, 190.389999, 191.350006, 193.240005,
    193.800003, 194.009995, 203.360001, 203.080002, 204.130005, 204.289993,
    203.639999, 203.190002, 202.960007, 203.839996, 203.570007, 202.509995,
    199.559998, 198.679993, 199.679993, 199.750000, 199.020004, 199.570007,
    199.259995, 199.820007, 200.220001, 198.860001, 198.110001, 198.839996,
    197.509995, 197.839996, 198.600006, 200.789993, 199.360001, 202.550003,
    205.690002, 206.660004, 208.240005, 209.429993, 209.039993, 209.089996,
    209.770004, 212.149994, 213.410004, 212.639999, 211.830002, 214.300003,
    210.110001, 211.619995, 210.050003, 211.500000, 210.100006, 209.740005}
    0.991867
            
     

Note that the calculated correlation value of 0.991867 indicates a high correlation between the high and low values; i.e. when the high value increases, very often the low value increases, and likewise when the high value decreases, very often the low value decreases.

To run this example

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    x 6