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