SQL Server LUdecompN Function

Updated 2024-03-06 20:59:31.720000

Description

Use the table-value function LUdecompN to calculate the LU factorization of an N x N matrix A in 3^rd 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}

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,>)

Arguments

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

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

Return Type

table

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.

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.

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.

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.

QRDECOMP - Decompose 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.