Login     Register

        Contact Us     Search

Calculating a Covariance Matrix in SQL Server

Apr 15

Written by: Charles Flock
4/15/2014 4:58 PM  RssIcon

In this article we look at 2 relatively straightforward methods for calculating a variance-covariance matrix in SQL Server, using the XLeratorDB function library, and compare them to the calculation provided in the Excel Data Analysis tool.
We can think of a covariance matrix in the following way. Given the following 4-by-n data set

A1
B1
C1
D1
A2
B2
C2
D2
A3
B3
C3
D3
An
Bn
Cn
Dn

the covariance matrix would be a 4-by-4 matrix with the following elements:

cov(A,A)
cov(B,A)
cov(C,A)
cov(D,A)
cov(A,B)
cov(B,B)
cov(C,B)
cov(D,B)
cov(A,C)
cov(B,C)
cov(C,C)
cov(D,C)
cov(A,D)
cov(B,D)
cov(C,D)
cov(D,D)

where COV is the Covariance function and COV(A,B) would be the covariance of column A and column B. When the same column in passed into the covariance function twice, the result is the variance so the diagonal of the covariance matrix consists of the variance for each of the columns. This is why the covariance matrix is also known as the variance-covariance matrix.

var(A)
cov(B,A)
cov(C,A)
cov(D,A)
cov(A,B)
var(B)
cov(C,B)
cov(D,B)
cov(A,C)
cov(B,C)
var(C)
cov(D,C)
cov(A,D)
cov(B,D)
cov(C,D)
var(D)

The covariance matrix is a square (m-by-m) matrix where m is the number of columns in the dataset.
Let's look at an example where the data are not stored in third-normal form: what I usually refer to as spreadsheet format. We will just put some data into a table.
SELECT
   *
INTO
   #t
FROM (VALUES
   (1,23,-16.7,14.2,-36.5,-35.6),
   (2,6.4,7.5,-0.6,-31.5,-56.4),
   (3,20.2,17.9,-4.2,41,-33.8),
   (4,20.8,31.3,-7.2,10,23.4),
   (5,-0.4,8.9,43.2,35.5,27.6),
   (6,6.6,-12.7,21,36.5,-2),
   (7,-7.6,-24,32.8,8.5,54.8),
   (8,6.2,9.2,15,-19,-51),
   (9,8,-5.7,24,37.5,36.8),
   (10,2.8,-16.3,7.4,18,-41.8)
   )N(rn,A,B,C,D,E)
 
SELECT
   *
FROM
   #t
This is what the table should look like.

rn
A
B
C
D
E
1
23
-16.7
14.2
-36.5
-35.6
2
6.4
7.5
-0.6
-31.5
-56.4
3
20.2
17.9
-4.2
41
-33.8
4
20.8
31.3
-7.2
10
23.4
5
-0.4
8.9
43.2
35.5
27.6
6
6.6
-12.7
21
36.5
-2
7
-7.6
-24
32.8
8.5
54.8
8
6.2
9.2
15
-19
-51
9
8
-5.7
24
37.5
36.8
10
2.8
-16.3
7.4
18
-41.8

The covariance matrix is calculated on all the possible 2-column combinations. Order matters, so there are 5 * 5 possible column combinations. One way to approach the calculation is just construct 25 explicit pieces of SQL to do the calculation; something like this.
SELECT
       'A' as row,
       'A' as col,
       wct.COVARIANCE_P(A,A) as COVAR
FROM
       #t
UNION ALL
SELECT
       'A' as row,
       'B' as col,
       wct.COVARIANCE_P(A,B) as COVAR
FROM
       #t
UNION ALL
SELECT
       'A' as row,
       'C' as col,
       wct.COVARIANCE_P(A,C) as COVAR
FROM
       #t
--Keep repeating until all possible
--combinations have been entered, ending
--with E E.
UNION ALL
SELECT
       'E' as row,
       'E' as col,
       wct.COVARIANCE_P(E,E) as COVAR
FROM
       #t
But there is a much more efficient and simpler way to do this. We can UNPIVOT the columns using the CROSS APPLY syntax and automatically generate all the possible column combinations.
SELECT
   x.col as row,
   y.col,
   WCT.COVARIANCE_P(x.val,y.val) as COVAR
FROM
   #t
CROSS APPLY(VALUES
   ('A',A),('B',B),('C',C),('D',D),('E',E))x(col,val)
CROSS APPLY(VALUES
   ('A',A),('B',B),('C',C),('D',D),('E',E))y(col,val)
GROUP BY
   x.col,
   y.col
This produces the following result.
row col COVAR
---- ---- ----------------------
A    A    88.28
B    A    74.25
C    A    -98.624
D    A    -47.02
E    A    -128.456
A    B    74.25
B    B    283.4524
C    B    -132.2284
D    B    39.265
E    B    -74.754
A    C    -98.624
B    C    -132.2284
C    C    239.0384
D    C    112.75
E    C    339.412
A    D    -47.02
B    D    39.265
C    D    112.75
D    D    786.15
E    D    567.34
A    E    -128.456
B    E    -74.754
C    E    339.412
D    E    567.34
E    E    1500.12
If you wanted to return the result in 'spreadsheet' format, we can simply wrap our SQL inside a PIVOT statement.
SELECT
   [A],[B],[C],[D],[E]
FROM (
   SELECT
       x.col as row,
       y.col,
       WCT.COVARIANCE_P(x.val,y.val) as COVAR
   FROM
       #t
   CROSS APPLY(VALUES
       ('A',A),('B',B),('C',C),('D',D),('E',E))x(col,val)
   CROSS APPLY(VALUES
       ('A',A),('B',B),('C',C),('D',D),('E',E))y(col,val)
   GROUP BY
       x.col,
       y.col
   ) D
PIVOT(
   SUM(COVAR)
FOR
   col in([A],[B],[C],[D],[E])
   ) as P
ORDER BY
   row
This produces the following result.

A
B
C
D
E
88.28
74.25
-98.624
-47.02
-128.456
74.25
283.4524
-132.228
39.265
-74.754
-98.624
-132.228
239.0384
112.75
339.412
-47.02
39.265
112.75
786.15
567.34
-128.456
-74.754
339.412
567.34
1500.12

It turns out that Excel has a Data Analysis tool for calculating the covariance matrix. Let's see what it returns using the same data.

 
A
B
C
D
E
A
88.28
 
 
 
 
B
74.25
283.4524
 
 
 
C
-98.624
-132.228
239.0384
 
 
D
-47.02
39.265
112.75
786.15
 
E
-128.456
-74.754
339.412
567.34
1500.12

Interestingly, Excel only returns the lower triangular portion of the covariance matrix. As you can see from the matrix we created in SQL Server, the covariance matrix is symmetric so that the value for cov(A,B) = cov(B,A). There is really no explanation in the Excel documentation about why the upper triangular portion of the matrix is not returned.
In dealing with the data in spreadsheet format, our SQL is sensitive to the column definition of the underlying table. In this next example we will use some matrix functions from the XLeratorDB/math library to create the covariance matrix, which returns a result in third-normal form, and which doesn't require un-pivoting the underlying data.
DECLARE @C as varchar(max) = wct.MATRIX2STRING_q(
   'SELECT
       *
   FROM (VALUES
       (23,-16.7,14.2,-36.5,-35.6),
       (6.4,7.5,-0.6,-31.5,-56.4),
       (20.2,17.9,-4.2,41,-33.8),
       (20.8,31.3,-7.2,10,23.4),
       (-0.4,8.9,43.2,35.5,27.6),
       (6.6,-12.7,21,36.5,-2),
       (-7.6,-24,32.8,8.5,54.8),
       (6.2,9.2,15,-19,-51),
       (8,-5.7,24,37.5,36.8),
       (2.8,-16.3,7.4,18,-41.8)
       )N(A,B,C,D,E)'
   )
DECLARE @mrows as float = wct.MROWS(@C)
DECLARE @invmrows as float = 1e+00/@mrows
DECLARE @x as varchar(max) = wct.MATSUB(@C, wct.MATMULT(wct.MATMULT(wct.ONES(@mrows,@mrows),@C),@invmrows))
 
SELECT
   *
FROM
   wct.MATRIX(wct.MATMULT(wct.MATMULT(wct.TRANSPOSE(@x),@x),@invmrows))

This produces the following result.

RowNum      ColNum      ItemValue
----------- ----------- ----------------------
0           0           88.28
0           1           74.25
0           2           -98.624
0           3           -47.02
0           4           -128.456
1           0           74.25
1           1           283.4524
1           2           -132.2284
1           3           39.265
1           4           -74.754
2           0           -98.624
2           1           -132.2284
2           2           239.0384
2           3           112.75
2           4           339.412
3           0           -47.02
3           1           39.265
3           2           112.75
3           3           786.15
3           4           567.34
4           0           -128.456
4           1           -74.754
4           2           339.412
4           3           567.34
4           4           1500.12


This SQL used the following formula to calculate the covariance matrix.





As with the COVARIANCE_P function, if you want the resultant table returned in spreadsheet format you will have to use a PIVOT.
SELECT
   [0],[1],[2],[3],[4]
FROM (
   SELECT
       *
   FROM
       wct.MATRIX(wct.MATMULT(wct.MATMULT(wct.TRANSPOSE(@x),@x),@invmrows))
   ) D
PIVOT(
   SUM(ItemValue)
FOR
   ColNum in([0],[1],[2],[3],[4])
   ) as P
ORDER BY
   RowNum


The following SQL will automatically figure out the number of columns and return the results in spreadsheet format, so that it can be used reg
ardless of the dimension of the input matrix.
DECLARE @C as varchar(max) = wct.MATRIX2STRING_q(
   'SELECT
       *
   FROM (VALUES
       (47.5,18.7,9.2,8.9),
       (33.6,-16.3,11.5,10.3),
       (33.4,-4,26.5,-3.5),
       (-7.6,26.3,-4.3,14),
       (-17.6,12.3,-15.7,7.5),
       (-24.4,3.7,14.1,14.1),
       (30.8,35.6,41.8,2.3),
       (15.1,30.7,23.6,3.4),
       (15.9,11.8,10.8,2.3),
       (7.5,35,25.9,3.1)
       )n(a,b,c,d)'
   )
DECLARE @mrows as float = wct.MROWS(@C)
DECLARE @mcols as float = wct.MCOLS(@C)
DECLARE @invmrows as float = 1e+00/@mrows
DECLARE @x as varchar(max) = wct.MATSUB(@C, wct.MATMULT(wct.MATMULT(wct.ONES(@mrows,@mrows),@C),@invmrows))
 
DECLARE @sqlstring as varchar(max) = '
   SELECT
       @cols
   FROM (
       SELECT
          *
       FROM
          wct.MATRIX(wct.MATMULT(wct.MATMULT(wct.TRANSPOSE(' + wct.QUOTES(@x) + '),' + wct.QUOTES(@x) + '),' + cast(@invmrows as varchar(max)) + '))
       ) D
   PIVOT (
       SUM(ItemValue)
   FOR
       ColNum in (@cols)
       ) as P
   ORDER BY
       RowNum'
 
DECLARE @columns as varchar(max) =(
   SELECT
       '[' + CAST(SeriesValue as varchar(max)) + ']'
   FROM
       wct.SeriesInt(0, @mcols-1,NULL,NULL,NULL)
   ORDER BY
       SeriesValue
   FOR XML PATH('')
   )
SET @columns = REPLACE(@columns, '][','],[')
SET @sqlstring = REPLACE(@sqlstring,'@cols',@columns)
 
EXEC(@sqlstring)

This produces the following result.

0
1
2
3
514.8396
-35.3496
175.8802
-58.5918
-35.3496
269.6696
66.5608
-11.2652
175.8802
66.5608
241.8224
-48.4866
-58.5918
-11.2652
-48.4866
29.0984



All of the examples in this article were entered in SQL Server 2012. XLeratorDB works in SQL Server 2005, 2008, 2012, and 2014, though some functions might have a different syntax in SQL Server 2005.

You should download the 15-day free trial today and see how you can use XLeratorDB to get the most out of the data in your database engine.

 

Tags:
Categories:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service