SQL Server QRdecomp Function

Updated 2023-10-20 13:04:27.837000

Description

Use the table-valued function 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 [westclintech].[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 Type

table

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.

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.

We can also calculate Q^TQ, 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.