LUdecomp_q
Updated 2024-03-06 20:58:47.617000
Syntax
SELECT * FROM [westclintech].[wct].[LUdecomp_q](
<@Matrix_RangeQuery, nvarchar(max),>)
Description
Use the table-value function LUdecomp_q to calculate the LU factorization of an N x N matrix A using partial pivoting. LUdecomp_q returns a lower triangular matrix L, an upper triangular matrix U, and a permutation matrix P such that,
LU = PA
This means that L has only zeroes above the diagonal and U has only zeroes below the diagonal.
For a 3 x 3 matrix this becomes:
P\times\begin{bmatrix}a_{11}&a_{12}&a_{13}\\a_{21}&a_{22}&a_{23}\\a_{31}&a_{32}&a_{33}\end{bmatrix}=\begin{bmatrix}l_{11}&0&0\\l_{21}&l_{22}&0\\l_{31}&l_{32}&l_{33}\end{bmatrix}\begin{bmatrix}u_{11}&u_{12}&u_{13}\\0&u_{22}&u_{23}\\0&0&u_{33}\end{bmatrix}
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": "0111ab70-e827-4ed5-b288-95e270493c71", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "84612c26-47b4-4649-bd37-8658dda8d363", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "f24e4a91-8833-4b1f-94a6-03d6465477d4", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}, {"id": "57fb1d7a-a5c7-4a46-8312-50b1ddc74541", "colName": "Type", "colDatatype": "nvarchar(4000)", "colDesc": "The pivot(P), lower triangular(L) or upper triangular (U) matrix type"}]}
Remarks
The number of columns in the matrix must be equal to the number of rows or an error will be returned.
Use the LUdecomp function for simpler queries.
Use LUdecompN_q for a table in third-normal form.
Use LU for a matrix stored as a string.
Type is either 'L', 'U', or 'P'.
The function returns an error if the array contains a non-numeric value.
Examples
In this example, we calculate the LU decomposition directly from the SELECT statement.
SELECT *
FROM wct.LUdecomp_q('
SELECT 0.002,1.231,2.471 UNION ALL
SELECT 1.196,3.165,2.54 UNION ALL
SELECT 1.475,4.271,2.142');
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":"Value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Type"}],"rows":[{"RowNum":"0","ColNum":"0","Value":"1","Type":"L"},{"RowNum":"0","ColNum":"1","Value":"0","Type":"L"},{"RowNum":"0","ColNum":"2","Value":"0","Type":"L"},{"RowNum":"1","ColNum":"0","Value":"0.00167224080267559","Type":"L"},{"RowNum":"1","ColNum":"1","Value":"1","Type":"L"},{"RowNum":"1","ColNum":"2","Value":"0","Type":"L"},{"RowNum":"2","ColNum":"0","Value":"1.23327759197324","Type":"L"},{"RowNum":"2","ColNum":"1","Value":"0.299970803835884","Type":"L"},{"RowNum":"2","ColNum":"2","Value":"1","Type":"L"},{"RowNum":"0","ColNum":"0","Value":"1.196","Type":"U"},{"RowNum":"0","ColNum":"1","Value":"3.165","Type":"U"},{"RowNum":"0","ColNum":"2","Value":"2.54","Type":"U"},{"RowNum":"1","ColNum":"0","Value":"0","Type":"U"},{"RowNum":"1","ColNum":"1","Value":"1.22570735785953","Type":"U"},{"RowNum":"1","ColNum":"2","Value":"2.4667525083612","Type":"U"},{"RowNum":"2","ColNum":"0","Value":"0","Type":"U"},{"RowNum":"2","ColNum":"1","Value":"0","Type":"U"},{"RowNum":"2","ColNum":"2","Value":"-1.73047881640933","Type":"U"},{"RowNum":"0","ColNum":"0","Value":"0","Type":"P"},{"RowNum":"0","ColNum":"1","Value":"1","Type":"P"},{"RowNum":"0","ColNum":"2","Value":"0","Type":"P"},{"RowNum":"1","ColNum":"0","Value":"1","Type":"P"},{"RowNum":"1","ColNum":"1","Value":"0","Type":"P"},{"RowNum":"1","ColNum":"2","Value":"0","Type":"P"},{"RowNum":"2","ColNum":"0","Value":"0","Type":"P"},{"RowNum":"2","ColNum":"1","Value":"0","Type":"P"},{"RowNum":"2","ColNum":"2","Value":"1","Type":"P"}]}
Note that the results are returned in third-normal form. If we wanted to a more traditional (de-normalized) presentation of the results, we could use the PIVOT function.
SELECT Type,
[0],
[1],
[2]
FROM
(
SELECT *
FROM wct.LUdecomp_q('
SELECT 0.002,1.231,2.471 UNION ALL
SELECT 1.196,3.165,2.54 UNION ALL
SELECT 1.475,4.271,2.142')
) d
PIVOT
(
SUM(Value)
FOR ColNum in ([0], [1], [2])
) p;
This produces the following result.
{"columns":[{"field":"Type"},{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"1","1":"0","2":"0","Type":"L"},{"0":"0.00167224080267559","1":"1","2":"0","Type":"L"},{"0":"1.23327759197324","1":"0.299970803835884","2":"1","Type":"L"},{"0":"0","1":"1","2":"0","Type":"P"},{"0":"1","1":"0","2":"0","Type":"P"},{"0":"0","1":"0","2":"1","Type":"P"},{"0":"1.196","1":"3.165","2":"2.54","Type":"U"},{"0":"0","1":"1.22570735785953","2":"2.4667525083612","Type":"U"},{"0":"0","1":"0","2":"-1.73047881640933","Type":"U"}]}
In this example, we demonstrate how to reconstruct the input matrix using the calculation P'LU.
SELECT k.*
FROM
(
SELECT Type as MatrixType,
wct.NMATRIX2STRING(RowNum, ColNum, Value) as Matrix
FROM wct.LUdecomp_q('
SELECT 0.002,1.231,2.471 UNION ALL
SELECT 1.196,3.165,2.54 UNION ALL
SELECT 1.475,4.271,2.142')
GROUP BY Type
) p
PIVOT
(
MAX(Matrix)
FOR MatrixType IN (L, P, U)
) d
CROSS APPLY wct.MATRIX(wct.MATMULT(wct.TRANSPOSE(P), wct.MATMULT(L, U))) K;
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":"0.00200000000000001"},{"RowNum":"0","ColNum":"1","ItemValue":"1.231"},{"RowNum":"0","ColNum":"2","ItemValue":"2.471"},{"RowNum":"1","ColNum":"0","ItemValue":"1.196"},{"RowNum":"1","ColNum":"1","ItemValue":"3.165"},{"RowNum":"1","ColNum":"2","ItemValue":"2.54"},{"RowNum":"2","ColNum":"0","ItemValue":"1.475"},{"RowNum":"2","ColNum":"1","ItemValue":"4.27099999999998"},{"RowNum":"2","ColNum":"2","ItemValue":"2.14199999999999"}]}
In this example, we will use the VALUES statement.
SELECT *
FROM wct.LUdecomp_q('SELECT * FROM (VALUES
(0.002,1.231,2.471),
(1.196,3.165,2.54),
(1.475,4.271,2.142)
)n(x1,x2,x3)');
This returns the same result as the first example.
This example demonstrates how to use the function by selecting data from a table.
SELECT IDENTITY(int, 1, 1) as rn,
*
INTO #A
FROM
(
VALUES
(0.002, 1.231, 2.471),
(1.196, 3.165, 2.54),
(1.475, 4.271, 2.142)
) n (x1, x2, x3);
SELECT *
FROM wct.LUdecomp_q('
SELECT
x1,x2,x3
FROM
#A
ORDER BY
rn');
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":"Value","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Type"}],"rows":[{"RowNum":"0","ColNum":"0","Value":"1","Type":"L"},{"RowNum":"0","ColNum":"1","Value":"0","Type":"L"},{"RowNum":"0","ColNum":"2","Value":"0","Type":"L"},{"RowNum":"1","ColNum":"0","Value":"0.00167224080267559","Type":"L"},{"RowNum":"1","ColNum":"1","Value":"1","Type":"L"},{"RowNum":"1","ColNum":"2","Value":"0","Type":"L"},{"RowNum":"2","ColNum":"0","Value":"1.23327759197324","Type":"L"},{"RowNum":"2","ColNum":"1","Value":"0.299970803835884","Type":"L"},{"RowNum":"2","ColNum":"2","Value":"1","Type":"L"},{"RowNum":"0","ColNum":"0","Value":"1.196","Type":"U"},{"RowNum":"0","ColNum":"1","Value":"3.165","Type":"U"},{"RowNum":"0","ColNum":"2","Value":"2.54","Type":"U"},{"RowNum":"1","ColNum":"0","Value":"0","Type":"U"},{"RowNum":"1","ColNum":"1","Value":"1.22570735785953","Type":"U"},{"RowNum":"1","ColNum":"2","Value":"2.4667525083612","Type":"U"},{"RowNum":"2","ColNum":"0","Value":"0","Type":"U"},{"RowNum":"2","ColNum":"1","Value":"0","Type":"U"},{"RowNum":"2","ColNum":"2","Value":"-1.73047881640933","Type":"U"},{"RowNum":"0","ColNum":"0","Value":"0","Type":"P"},{"RowNum":"0","ColNum":"1","Value":"1","Type":"P"},{"RowNum":"0","ColNum":"2","Value":"0","Type":"P"},{"RowNum":"1","ColNum":"0","Value":"1","Type":"P"},{"RowNum":"1","ColNum":"1","Value":"0","Type":"P"},{"RowNum":"1","ColNum":"2","Value":"0","Type":"P"},{"RowNum":"2","ColNum":"0","Value":"0","Type":"P"},{"RowNum":"2","ColNum":"1","Value":"0","Type":"P"},{"RowNum":"2","ColNum":"2","Value":"1","Type":"P"}]}
See Also
LU - LU factorization with partial pivoting
LUDECOMP - Calculate the LU factorization of an N x N matrix using partial pivoting.
LUDECOMPN - Calculate the LU factorization of an N x N matrix using partial pivoting.
LUDECOMPN_Q - Calculate the LU factorization of an N x N matrix using partial pivoting.