MCORR
Updated: 05 March 2015
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 [wct].[MCOV](
<@Matrix, nvarchar(max),>)
Arguments
@Matrix
a string representation of the matrix.
Return Types
[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 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