SQL Server MCORR Function

Updated 2023-10-16 20:00:10.827000

Description

Use the scalar function MCORR to calculate a correlation matrix. MCORR expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons. Please refer to the CORRM documentation for an explanation as to how the correlation matrix is computed.

Syntax

SELECT [westclintech].[wct].[MCORR] (
   <@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, us 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 MCORR .

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.MCORR(@A);

This produces the following result.

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

SELECT wct.MCORR('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.MCORR(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], 4) as [0],
       ROUND([1], 4) as [1],
       ROUND([2], 4) as [2],
       ROUND([3], 4) as [3],
       ROUND([4], 4) as [4],
       ROUND([5], 4) as [5],
       ROUND([6], 4) as [6],
       ROUND([7], 4) as [7]
FROM
(
    SELECT *
    FROM wct.MATRIX(wct.MCORR('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

CORRM - Table-valued function to calculate the correlation matrix

MCOV - Calculate a sample covariance matrix.

CORREL - Aggregate function to calculate the correlation coefficient

PEARSON - Aggregate function to calculate the correlation coefficient