Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server covariance matrix function


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.



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service