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;