MCOVN_q
Updated: 30 September 2013
Use MCOVN_q 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_q](
<@Matrix_RangeQuery, nvarchar(max),>)
Arguments
@Matrix_RangeQuery
the SELECT statement, as text, used to determine the matrix to be used in this function. The SELECT statement specifies the row, the column, and the value to be returned from the table or view or can be used to enter the matrix values directly. @Matrix_RangeQuery must always return three columns of data. Data returned from the @Matrix_RangeQuery select must be of the type float or of a type that implicitly converts to float.
Return Types
TABLE (
[RowNum] [int] NULL,
[ColNum] [int] NULL,
[ItemValue] [float] NULL
Remarks
· Use the MCOVN function for simpler queries.
· Use MCOV_q for a de-normalized table.
· If the array 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 temp table containing a single 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_q('SELECT rn,cn,z FROM #c')
DROP TABLE #c
Here are the first few rows of the result table.
Using the same data as the previous example, we will select the data directly from a derived table.
SELECT *
FROM wct.MCOVN_q('SELECT rn,cn,z FROM (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)')
Here are the first few rows of 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. Note that we have to convert the Matrix identifier (which is defined as int) to a varchar to include it in the where clause of the @Matrix_RangeQuery. This would not be necessary in the MCOVN function.
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_q('SELECT rn,cn,z FROM #c where MATRIX = ' + CAST(n.Matrix as varchar(max)))k
DROP TABLE #c
Here are the first few rows of the resultant table.
