SQL Server LUdecompN_q Function

Updated 2024-03-06 21:00:22.677000

Description

Use the table-value function LUdecompN_q to calculate the LU factorization of an N x N matrix A in 3^rd normal form using partial pivoting. LUdecompN_q returns a lower triangular matrix L, an upper triangular matrix U, and a permutation matrix P such that,

    LU = PA

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_q](
   <@Matrix_RangeQuery, nvarchar(max),>)

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

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 function for simpler queries.

Use LUdecomp_q for a table not in third-normal form.

The function returns an error if the array contains a non-numeric value.

The returned Type column contains 'L', 'U', or 'P'

Examples

In this example, we calculate the LU decomposition directly from the SELECT statement.

SELECT *
FROM wct.LUdecompN_q('
   SELECT
       *
   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)');

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_q('
   SELECT
       *
   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)')
) d
PIVOT
(
    SUM(Value)
    FOR ColNum in ([0], [1], [2])
) 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_q('
   SELECT
       *
   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)')
    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.

This example demonstrates how to use the function by selecting data from a table.

SELECT *
INTO #A
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_q('SELECT r,c,x FROM #A');

This produces the following result.

     RowNum      ColNum                  Value Type
----------- ----------- ---------------------- ----
          0           0                      1 L
          0           1                      0 L
          0           2                      0 L
          1           0    0.00167224080267559 L
          1           1                      1 L
          1           2                      0 L
          2           0       1.23327759197324 L
          2           1      0.299970803835884 L
          2           2                      1 L
          0           0                  1.196 U
          0           1                  3.165 U
          0           2                   2.54 U
          1           0                      0 U
          1           1       1.22570735785953 U
          1           2        2.4667525083612 U
          2           0                      0 U
          2           1                      0 U
          2           2      -1.73047881640933 U
          0           0                      0 P
          0           1                      1 P
          0           2                      0 P
          1           0                      1 P
          1           1                      0 P
          1           2                      0 P
          2           0                      0 P
          2           1                      0 P
          2           2                      1 P

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