QRdecomp_q

Updated 2024-02-13 19:41:40.480000

Syntax

SELECT * FROM [westclintech].[wct].[QRdecomp_q](
   <@Matrix_RangeQuery, nvarchar(max),>)

Description

Use the table-valued function QRdecomp_q 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_RangeQuery

the SELECT statement, as text, used to determine the square (N x N) matrix to be used in this function. The SELECT statement specifies the column names from the table or view or can be used to enter the matrix values directly. Data returned from the @Matrix_RangeQuery select 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": "ebce8527-5a41-426c-af8c-46b0b73eb086", "colName": "Matrix", "colDatatype": "nvarchar(4000)", "colDesc": "Identifier for the 'Q' or 'R' matrix"}, {"id": "3871cbd9-61bf-44ad-83dc-7999105cc157", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "c6054e91-f758-46b2-93e5-c66a49692269", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "a93829ae-1186-4a1f-8f7b-2588d8a4dc3f", "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 function for simpler 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

In this example, we will enter the matrix directly into the function without populating a table.

SELECT *

FROM wct.QRDECOMP_q('

      SELECT 12,-51,4 UNION ALL

      SELECT 6,167,-68 UNION ALL

      SELECT -4,24,-41');

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 the matrix values had been in a table, could have simply changed the SQL to SELECT from the table.

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_q('

      SELECT * FROM #m');

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_q('

      SELECT 12,-51,4 UNION ALL

      SELECT 6,167,-68 UNION ALL

      SELECT -4,24,-41');

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_q('

      SELECT 12,-51,4 UNION ALL

      SELECT 6,167,-68 UNION ALL

      SELECT -4,24,-41');

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"}]}