SQL Server LUdecomp Function

Updated 2024-03-06 20:57:57.167000

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}

Syntax

SELECT * FROM [westclintech].[wct].[LUdecomp](
  <@Matrix_TableName, nvarchar(max),>
 ,<@Matrix_ColumnNames, 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 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.

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

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.

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.

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.

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.