Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server QR decomposition


QRdecomp

Updated: 31 January 2012


Use QRdecomp for decomposing a de-normalized N x N matrix A into the product of an upper triangular matrix R and an orthogonal matrix Q, such that
                A=QR.
Syntax
SELECT * FROM [wctMath].[wct].[QRdecomp](
  <@Matrix_TableName, nvarchar(max),>
 ,<@Matrix_ColumnNames, nvarchar(4000),>
 ,<@Matrix_GroupedColumnName, nvarchar(4000),>
 ,<@Matrix_GroupedColumnValue, sql_variant,>)
Arguments
@Matrix_ TableName
the name, as text, of the table or view that contains the values in the square (N x N) array to be used in the QRdecomp calculation.
@Matrix_ ColumnNames
the name, as text, of the columns in the table or view specified by @Matrix_TableName that contains the array values to be used in the MMULT calculation. Data returned from the @Matrix_ColumnNames must be of the type float or of a type that implicitly converts to float.
@Matrix_GroupedColumnName
the name, as text, of the column in the table or view specified by @Matrix_TableName which will be used for grouping the results.
@Matrix_GroupedColumnValue
the column value to do the grouping on.
Return Types
TABLE (
      [Matrix] [nvarchar](2) NULL,
      [RowNum] [int] NULL,
      [ColNum] [int] NULL,
      [ItemValue] [float] NULL
Remarks
·         The number of columns in the matrix must be equal to the number of rows or an error will be returned.
·         Use the QRdecomp_q function for more complicated queries.
·         Use QR to operate on a string representation of the matrix.
·         The function returns an error if the array contains a non-numeric value.
 
Examples
Since QRdecomp requires a table name as input, we will have to put our matrix into a temporary table before invoking the table-valued function.
SELECT *
INTO #m
FROM (
      SELECT 12,-51,4 UNION ALL
      SELECT 6,167,-68 UNION ALL
      SELECT -4,24,-41
      ) n(a,b,c) 
 
SELECT *
FROM wct.QRdecomp('#m','a,b,c','',NULL)
This produces the following result.
Matrix      RowNum      ColNum              ItemValue
------ ----------- ----------- ----------------------
Q                0           0     -0.857142857142857
Q                0           1      0.394285714285714
Q                0           2     -0.331428571428571
Q                1           0     -0.428571428571429
Q                1           1     -0.902857142857142
Q                1           2     0.0342857142857143
Q                2           0      0.285714285714286
Q                2           1     -0.171428571428571
Q                2           2     -0.942857142857142
R                0           0                    -14
R                0           1                    -21
R                0           2                     14
R                1           0                      0
R                1           1                   -175
R                1           2                     70
R                2           0                      0
R                2           1                      0
R                2           2                     35
 
Note that the results are returned in third-normal form.
If we calculate Q * R we should get the original matrix, A, returned, within the limits of floating point arithmetic.
SELECT *
INTO #n
FROM wct.QRdecomp('#m','a,b,c','',NULL) Q
SELECT RowNum
,ColNum
,ROUND(ItemValue, 0) as ItemValue
FROM wct.MMULTN_q(
'Select RowNum, ColNum, ItemValue FROM #n WHERE MATRIX = ' + CHAR(39) + 'Q' + CHAR(39),
'Select RowNum, ColNum, ItemValue FROM #n WHERE MATRIX = ' + CHAR(39) + 'R' + CHAR(39)
      )
This produces the following result.
     RowNum      ColNum              ItemValue
----------- ----------- ----------------------
          0           0                     12
          0           1                    -51
          0           2                      4
          1           0                      6
          1           1                    167
          1           2                    -68
          2           0                     -4
          2           1                     24
          2           2                    -41
 
We can also calculate QTQ, verifying that this will return the identity matrix.
SELECT *
INTO #n
FROM wct.QRdecomp('#m','a,b,c','',NULL) Q
SELECT RowNum
,ColNum
,ROUND(ItemValue, 0) as ItemValue
FROM wct.MMULTN_q(
'Select ColNum, RowNum, ItemValue FROM #n WHERE MATRIX = ' + CHAR(39) + 'Q' + CHAR(39),
'Select RowNum, ColNum, ItemValue FROM #n WHERE MATRIX = ' + CHAR(39) + 'Q' + CHAR(39)
      )
This returns the following result.
     RowNum      ColNum              ItemValue
----------- ----------- ----------------------
          0           0                      1
          0           1                      0
          0           2                      0
          1           0                      0
          1           1                      1
          1           2                      0
          2           0                      0
          2           1                      0
          2           2                      1
 


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service