SQL Server MCOV Function

Updated 2023-10-17 01:40:16.517000

Description

Use the scalar function MCOV to calculate a sample covariance matrix. MCOV expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons. Please refer to the COVM documentation for an explanation as to how the sample covariance matrix is calculated.

Syntax

SELECT [westclintech].[wct].[MCOV](
   <@Matrix, nvarchar(max),>)

Arguments

@Matrix

a string representation of the matrix.

Return Type

nvarchar(max)

Remarks

The string representations of @Matrix must only contain numbers, commas (to separate the columns), and semi-colons to separate the rows.

Consecutive commas will generate an error.

Consecutive semi-colons will generate an error.

Non-numeric data between commas will generate an error.

Non-numeric data between semi-colons will generate an error.

To convert non-normalized data to a string format, use the MATRIX2STRING or the MATRIX2STRING_q function.

To convert normalized data to a string format, us the NMATRIX2STRING or the NMATRIX2STRING_q function.

To convert the string result to a table, use the table-valued function MATRIX.

Examples

In this example we use the MATRIX2STRING_q function to create a properly formatted matrix for a varchar variable which becomes the input to MCOV .

DECLARE @A as varchar(max)
    = wct.MATRIX2STRING_q('SELECT
          x1,x2,x3,x4,x5,x6,x7,x8
        FROM (VALUES
           (1,1,1,1,1,1,1,1)
          ,(2,3,4,5,6,7,8,9)
          ,(4,9,16,25,36,49,64,81)
        )n(x1,x2,x3,x4,x5,x6,x7,x8)');
SELECT wct.MCOV(@A);

This produces the following result.

We also could have passed the input to the function as:

SELECT wct.MCOV('1,1,1,1,1,1,1,1;2,3,4,5,6,7,8,9;4,9,16,25,36,49,64,81');

as well as:

SELECT wct.MCOV(wct.MATRIX2STRING_q('SELECT
              x1,x2,x3,x4,x5,x6,x7,x8
           FROM (VALUES
               (1,1,1,1,1,1,1,1)
              ,(2,3,4,5,6,7,8,9)
              ,(4,9,16,25,36,49,64,81)
           )n(x1,x2,x3,x4,x5,x6,x7,x8)'));

We can use the table-valued function MATRIX to convert the string results to a table and the PIVOT function to provide the traditional representation of a matrix.

SELECT ROUND([0], 2) as [0],
       ROUND([1], 2) as [1],
       ROUND([2], 2) as [2],
       ROUND([3], 2) as [3],
       ROUND([4], 2) as [4],
       ROUND([5], 2) as [5],
       ROUND([6], 2) as [6],
       ROUND([7], 2) as [7]
FROM
(
    SELECT *
    FROM wct.MATRIX(wct.MCOV('1,1,1,1,1,1,1,1;2,3,4,5,6,7,8,9;4,9,16,25,36,49,64,
              81'))
) d
PIVOT
(
    SUM(ItemValue)
    FOR ColNum in ([0], [1], [2], [3], [4], [5], [6], [7])
) pvt
ORDER BY RowNum;

This produces the following result.

See Also

COVARIANCE_P - Aggregate function to calculate the population covariance

COVARIANCE_S - Aggregate function to calculate the sample covariance

COVM - Table-valued function to calculate the covariance matrix

MCORR - Correlation matrix using a formatted matrix as input