Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server correlation matrix function


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 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

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service