MCORR

Updated 2023-10-16 20:00:10.827000

Syntax

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

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.

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.

{"columns":[{"field":"column 1"}],"rows":[{"column 1":"1,0.995870594885822,0.989743318610787,0.98432413828809,0.9798637100972,0.97622103992743,0.973222701448379,0.970725343394151;0.995870594885822,1,0.998625428903524,0.996270962773436,0.993944095928862,0.991869783800371,0.990071896570823,0.988522467870286;0.989743318610787,0.998625428903524,1,0.999423797128766,0.998337488459583,0.997176464952738,0.996078416265654,0.995082098645899;0.98432413828809,0.996270962773436,0.999423797128766,1,0.999718640088218,0.999150742946594,0.998507503106759,0.997870827960502;0.9798637100972,0.993944095928862,0.998337488459583,0.999718640088218,1,0.999846989517886,0.999522026579879,0.999137118134003;0.97622103992743,0.991869783800371,0.997176464952738,0.999150742946594,0.999846989517886,1,0.999909873371905,0.999710773674355;0.973222701448379,0.990071896570823,0.996078416265654,0.998507503106759,0.999522026579879,0.999909873371905,1,0.99994354800767;0.970725343394151,0.988522467870286,0.995082098645899,0.997870827960502,0.999137118134003,0.999710773674355,0.99994354800767,1"}]}

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;