QRdecomp

Updated 2023-10-20 13:04:27.837000

Syntax

SELECT * FROM [westclintech].[wct].[QRdecomp](
  <@Matrix_TableName, nvarchar(max),>
 ,<@Matrix_ColumnNames, nvarchar(4000),>
 ,<@Matrix_GroupedColumnName, nvarchar(4000),>
 ,<@Matrix_GroupedColumnValue, sql_variant,>)

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.

Arguments

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

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

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "1b031ce3-6020-4521-adc5-feeb1bca5699", "colName": "Matrix", "colDatatype": "nvarchar(4000)", "colDesc": "Identifier for the 'Q' or 'R' matrix"}, {"id": "cf29fb12-bdb4-410d-8e68-3527326a9ac8", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "88c4bc96-84a8-40f7-abe3-e363c9fdd72f", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "3fcae906-18e0-495d-aa50-29d7604ddeef", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}

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.

{"columns":[{"field":"Matrix"},{"field":"RowNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ColNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ItemValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Matrix":"Q","RowNum":"0","ColNum":"0","ItemValue":"-0.857142857142857"},{"Matrix":"Q","RowNum":"0","ColNum":"1","ItemValue":"0.394285714285714"},{"Matrix":"Q","RowNum":"0","ColNum":"2","ItemValue":"-0.331428571428571"},{"Matrix":"Q","RowNum":"1","ColNum":"0","ItemValue":"-0.428571428571429"},{"Matrix":"Q","RowNum":"1","ColNum":"1","ItemValue":"-0.902857142857142"},{"Matrix":"Q","RowNum":"1","ColNum":"2","ItemValue":"0.0342857142857143"},{"Matrix":"Q","RowNum":"2","ColNum":"0","ItemValue":"0.285714285714286"},{"Matrix":"Q","RowNum":"2","ColNum":"1","ItemValue":"-0.171428571428571"},{"Matrix":"Q","RowNum":"2","ColNum":"2","ItemValue":"-0.942857142857142"},{"Matrix":"R","RowNum":"0","ColNum":"0","ItemValue":"-14"},{"Matrix":"R","RowNum":"0","ColNum":"1","ItemValue":"-21"},{"Matrix":"R","RowNum":"0","ColNum":"2","ItemValue":"14"},{"Matrix":"R","RowNum":"1","ColNum":"0","ItemValue":"0"},{"Matrix":"R","RowNum":"1","ColNum":"1","ItemValue":"-175"},{"Matrix":"R","RowNum":"1","ColNum":"2","ItemValue":"70"},{"Matrix":"R","RowNum":"2","ColNum":"0","ItemValue":"0"},{"Matrix":"R","RowNum":"2","ColNum":"1","ItemValue":"0"},{"Matrix":"R","RowNum":"2","ColNum":"2","ItemValue":"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.

{"columns":[{"field":"RowNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ColNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ItemValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RowNum":"0","ColNum":"0","ItemValue":"12"},{"RowNum":"0","ColNum":"1","ItemValue":"-51"},{"RowNum":"0","ColNum":"2","ItemValue":"4"},{"RowNum":"1","ColNum":"0","ItemValue":"6"},{"RowNum":"1","ColNum":"1","ItemValue":"167"},{"RowNum":"1","ColNum":"2","ItemValue":"-68"},{"RowNum":"2","ColNum":"0","ItemValue":"-4"},{"RowNum":"2","ColNum":"1","ItemValue":"24"},{"RowNum":"2","ColNum":"2","ItemValue":"-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.

{"columns":[{"field":"RowNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ColNum","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"ItemValue","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"RowNum":"0","ColNum":"0","ItemValue":"1"},{"RowNum":"0","ColNum":"1","ItemValue":"0"},{"RowNum":"0","ColNum":"2","ItemValue":"0"},{"RowNum":"1","ColNum":"0","ItemValue":"0"},{"RowNum":"1","ColNum":"1","ItemValue":"1"},{"RowNum":"1","ColNum":"2","ItemValue":"0"},{"RowNum":"2","ColNum":"0","ItemValue":"0"},{"RowNum":"2","ColNum":"1","ItemValue":"0"},{"RowNum":"2","ColNum":"2","ItemValue":"1"}]}