MCOVN
Updated: 30 September 2013
Use MCOVN to calculate the covariance matrix of a matrix in 3rd normal form.
The covariance of a matrix (A) is calculated by subtracting the column mean (µ) from each element in a column, calculating XTX and then dividing the result by n-1, where n is the number of rows. For example:
Syntax
SELECT * FROM [wctMath].[wct].[MCOVN](
<@Matrix_TableName, nvarchar(max),>
,<@Matrix_Key1ColumnName, nvarchar(4000),>
,<@Matrix_Key2ColumnName, nvarchar(4000),>
,<@Matrix_DataColumnName, nvarchar(4000),>
,<@Matrix_GroupedColumnName, nvarchar(4000),>
,<@Matrix_GroupedColumnValue, sql_variant,>)
Arguments
@Matrix_TableName
the name, as text, of the table or view that contains the matrix values to be used in the calculation. @Matrix_TableName cannot be a table variable or a common table expression.
@Matrix_Key1ColumnName
the name, as text, of the column in the table or view specified by @Matrix_TableName that contains the 'row number' value used in the array.
@Matrix _Key2ColumnName
the name, as text, of the column in the table or view specified by @Matrix _TableName that contains the 'column number' value used in the array.
@Matrix _DataColumnName
the name, as text, of the column in the table or view specified by @Matrix _TableName that contains the matrix values to be used in the product. Data returned from the @Matrix_DataColumnName must be of the type float or of a type that implicitly converts to float.
@Matrix_GroupedColumnName
the name, as text, of the column in the table or view specified by @Matrix_TableName which will be used for grouping the results.
@Matrix_GroupedColumnValue
the column value to do the grouping on.
Return Types
TABLE (
[RowNum] [int] NULL,
[ColNum] [int] NULL,
[ItemValue] [float] NULL
Remarks
· Use the MCOVN_q function for more complex queries.
· Use MCOV for a de-normalized table.
· If the matrix contains NULL, then NULL will be returned.
· The function returns an error if the array contains a non-numeric value.
· If the supplied input is a vector, then result will be the (sample) variance.
Examples
In this example, we calculate the matrix covariance from a temporary table containing one matrix.
SET NOCOUNT ON
CREATE TABLE #c(
rn int,
cn int,
z float,
PRIMARY KEY(rn,cn)
)
INSERT INTO #c VALUES (1,1,1)
INSERT INTO #c VALUES (1,2,1)
INSERT INTO #c VALUES (1,3,1)
INSERT INTO #c VALUES (1,4,1)
INSERT INTO #c VALUES (1,5,1)
INSERT INTO #c VALUES (1,6,1)
INSERT INTO #c VALUES (1,7,1)
INSERT INTO #c VALUES (1,8,1)
INSERT INTO #c VALUES (2,1,2)
INSERT INTO #c VALUES (2,2,3)
INSERT INTO #c VALUES (2,3,4)
INSERT INTO #c VALUES (2,4,5)
INSERT INTO #c VALUES (2,5,6)
INSERT INTO #c VALUES (2,6,7)
INSERT INTO #c VALUES (2,7,8)
INSERT INTO #c VALUES (2,8,9)
INSERT INTO #c VALUES (3,1,4)
INSERT INTO #c VALUES (3,2,9)
INSERT INTO #c VALUES (3,3,16)
INSERT INTO #c VALUES (3,4,25)
INSERT INTO #c VALUES (3,5,36)
INSERT INTO #c VALUES (3,6,49)
INSERT INTO #c VALUES (3,7,61)
INSERT INTO #c VALUES (3,8,81)
SELECT *
FROM wct.MCOVN('#c','rn','cn','z','',NULL)
DROP TABLE #c
Here are the first few rows of the result table.
In this example, we use the same data as from the previous example, selecting it directly from a derived table.
SELECT *
FROM wct.MCOVN('(VALUES
(1,1,1)
, (1,2,1)
, (1,3,1)
, (1,4,1)
, (1,5,1)
, (1,6,1)
, (1,7,1)
, (1,8,1)
, (2,1,2)
, (2,2,3)
, (2,3,4)
, (2,4,5)
, (2,5,6)
, (2,6,7)
, (2,7,8)
, (2,8,9)
, (3,1,4)
, (3,2,9)
, (3,3,16)
, (3,4,25)
, (3,5,36)
, (3,6,49)
, (3,7,61)
, (3,8,81)
)n(rn,cn,z)','rn','cn','z','',NULL)
Here are the first few rows from the resultant table.
Let's put several matrices into a table and calculate the covariance matrix for each. We will use CROSS APPLY to calculate to the covariance matrix for matrix.
SET NOCOUNT ON
CREATE TABLE #c(
Matrix int,
rn int,
cn int,
z float,
PRIMARY KEY (Matrix, rn, cn)
)
INSERT INTO #c VALUES (100,1,1,-11)
INSERT INTO #c VALUES (100,1,2,-41)
INSERT INTO #c VALUES (100,1,3,36)
INSERT INTO #c VALUES (100,2,1,-31)
INSERT INTO #c VALUES (100,2,2,41)
INSERT INTO #c VALUES (100,2,3,-47)
INSERT INTO #c VALUES (100,3,1,48)
INSERT INTO #c VALUES (100,3,2,-38)
INSERT INTO #c VALUES (100,3,3,33)
INSERT INTO #c VALUES (100,4,1,8)
INSERT INTO #c VALUES (100,4,2,44)
INSERT INTO #c VALUES (100,4,3,-10)
INSERT INTO #c VALUES (101,1,1,39)
INSERT INTO #c VALUES (101,1,2,6)
INSERT INTO #c VALUES (101,1,3,-7)
INSERT INTO #c VALUES (101,2,1,33)
INSERT INTO #c VALUES (101,2,2,-49)
INSERT INTO #c VALUES (101,2,3,16)
INSERT INTO #c VALUES (101,3,1,14)
INSERT INTO #c VALUES (101,3,2,29)
INSERT INTO #c VALUES (101,3,3,13)
INSERT INTO #c VALUES (101,4,1,35)
INSERT INTO #c VALUES (101,4,2,-38)
INSERT INTO #c VALUES (101,4,3,-50)
INSERT INTO #c VALUES (101,5,1,9)
INSERT INTO #c VALUES (101,5,2,-32)
INSERT INTO #c VALUES (101,5,3,-25)
INSERT INTO #c VALUES (102,1,1,29)
INSERT INTO #c VALUES (102,1,2,49)
INSERT INTO #c VALUES (102,1,3,-17)
INSERT INTO #c VALUES (102,2,1,35)
INSERT INTO #c VALUES (102,2,2,28)
INSERT INTO #c VALUES (102,2,3,28)
INSERT INTO #c VALUES (102,3,1,-34)
INSERT INTO #c VALUES (102,3,2,-29)
INSERT INTO #c VALUES (102,3,3,-49)
INSERT INTO #c VALUES (102,4,1,0)
INSERT INTO #c VALUES (102,4,2,-5)
INSERT INTO #c VALUES (102,4,3,0)
INSERT INTO #c VALUES (102,5,1,-17)
INSERT INTO #c VALUES (102,5,2,14)
INSERT INTO #c VALUES (102,5,3,24)
INSERT INTO #c VALUES (102,6,1,44)
INSERT INTO #c VALUES (102,6,2,3)
INSERT INTO #c VALUES (102,6,3,-23)
SELECT n.MATRIX
,k.*
FROM (SELECT DISTINCT MATRIX FROM #c)n
CROSS APPLY wct.MCOVN('#c','rn','cn','z','MATRIX',n.Matrix)k
DROP TABLE #c
Here are the first few rows of the resultant table.
