LUdecompN
Updated 2024-03-06 20:59:31.720000
Syntax
SELECT * FROM [westclintech].[wct].[LUdecompN](
<@Matrix_TableName, nvarchar(max),>
,<@Matrix_Key1ColumnName, nvarchar(4000),>
,<@Matrix_Key2ColumnName, nvarchar(4000),>
,<@Matrix_DataColumnName, nvarchar(4000),>
,<@Matrix_GroupedColumnName, nvarchar(4000),>
,<@Matrix_GroupedColumnValue, sql_variant,>)
Description
Use the table-value function LUdecompN to calculate the LU factorization of an N x N matrix A in 3rd normal form using partial pivoting. LUdecompN 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 _Key2ColumnName
the name, as text, of the column in the table or view specified by @Matrix _TableName that contains the ‘column number’ value used in the array.
@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_Key1ColumnName
the name, as text, of the column in the table or view specified by @Matrix_TableName that contains the ‘row number’ value used in the array.
@Matrix_GroupedColumnValue
the column value to do the grouping on.
@Matrix _DataColumnName
the name, as text, of the column in the table or view specified by @Matrix _TableName that contains the matrix values to be used in the product. Data returned from the @Matrix_DataColumnName must be of the type float or of a type that implicitly converts to float.
@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 LUdecompN calculation.
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": "1545f759-33b1-456d-a76c-90d67dfd6b47", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "166ef812-7d13-40d3-b504-e2828ded5269", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "139578dc-4305-42f0-ba19-6156faef9143", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}, {"id": "fa21f45e-7eab-4234-a318-417a7e6eb2c9", "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 LUdecompN_q function for more complicated queries.
Use LUdecomp for a table not in third-normal form.
The function returns an error if the matrix contains a non-numeric value.
The returned Type column contains 'L', 'U', or 'P'
Examples
In this example, we will populate a temporary table #m and calculate its LU factorization.
SELECT *
INTO #m
FROM
(
VALUES
(0, 0, 0.002),
(0, 1, 1.231),
(0, 2, 2.471),
(1, 0, 1.196),
(1, 1, 3.165),
(1, 2, 2.54),
(2, 0, 1.475),
(2, 1, 4.271),
(2, 2, 2.142)
) m (r, c, x);
SELECT *
FROM wct.LUdecompN('#m', 'r', 'c', 'x', '', 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.
SELECT Type,
[0],
[1],
[2]
FROM
(SELECT * FROM wct.LUdecompN('#m', 'r', 'c', 'x', '', 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 the input matrix using the calculation P'LU.
SELECT k.*
FROM
(
SELECT Type as MatrixType,
wct.NMATRIX2STRING(RowNum, ColNum, Value) as Matrix
FROM wct.LUdecompN('#m', 'r', 'c', 'x', '', 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 - Calculate the LU factorization of an N x N matrix using partial pivoting.
LUDECOMP_q - 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.