LUdecomp
Updated 2024-03-06 20:57:57.167000
Syntax
SELECT * FROM [westclintech].[wct].[LUdecomp](
<@Matrix_TableName, nvarchar(max),>
,<@Matrix_ColumnNames, nvarchar(4000),>
,<@Matrix_GroupedColumnName, nvarchar(4000),>
,<@Matrix_GroupedColumnValue, sql_variant,>)
Description
Use the table-value function LUdecomp to calculate the LU factorization of an N x N matrix A using partial pivoting. LUdecomp 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_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 LUdecomp 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 LUdecomp 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": "b99516e8-5635-4de9-8d68-324512973c44", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "a1634d36-dd1f-4fba-b136-52d2ef74d006", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "6b2db705-7c21-47a8-8d5f-0aaf4e6861c8", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}, {"id": "6107e441-e794-4b53-acf3-8f2b9cead896", "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_q function for more complicated queries.
Use LUdecompN 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
Since LUdecomp requires a table name as input, we will have to put our matrix into a temporary table before invoking the table-valued function. Make sure that your table structure guarantees that the input matrix will be returned in the correct order.
SELECT *
INTO #m
FROM
(
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
) n(a, b, c);
GO
SELECT *
FROM wct.LUdecomp('#m', 'a,b,c', '', NULL);
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 can us the PIVOT function. Also note that we can use * to select all the columns.
SELECT *
INTO #m
FROM
(
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
) n(a, b, c);
GO
SELECT Type,
[0],
[1],
[2]
FROM
(SELECT * FROM wct.LUdecomp('#m', '*', '', NULL) ) d
PIVOT
(
sum(Value)
for ColNum in ([0], [1], [2])
) as 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 that input matrix using the calculation P'LU.
SELECT k.*
FROM
(
SELECT Type as MatrixType,
wct.NMATRIX2STRING(RowNum, ColNum, Value) as Matrix
FROM wct.LUdecomp('#m', '*', '', NULL)
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"}]}
See Also
LU - LU factorization with partial pivoting
LUDECOMP_q - 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.