SQL Server CHOLESKY Function

Updated 2024-04-11 15:34:02.407000

Description

Use the table-valued function CHOLESKY to calculate the Cholesky decomposition of a symmetric positive-definite matrix. The CHOLESKY function returns a table of the lower triangular matrix (L), such that

\textbf{A}=\textbf{LL}^T

Syntax

SELECT * FROM [westclintech].[wct].[CHOLESKY](
  <@A, nvarchar(max),>
 ,<@Is3N, bit,>)

Arguments

@A

A SELECT statement in the form of a string which returns the matrix to be decomposed.

@Is3N

A bit value which indicates whether the returned matrix is in 3rd Normal form (1) or in matrix form (0). ## Return Type table

Remarks

If @A is NULL an error is returned.

@A must be a symmetric positive-definite matrix.

Examples

Example #1

Calculate the Cholesky decomposition for:

\begin{bmatrix}6&3&4&8\\3&6&5&1\\4&5&10&7\\8&1&7&25\end{bmatrix}

We will put the matrix into a temp table first select from the temp table.

SELECT *
INTO   #A
  FROM (   VALUES (1, 6, 3, 4, 8),
                  (2, 3, 6, 5, 1),
                  (3, 4, 5, 10, 7),
                  (4, 8, 1, 7, 25)) n (rn, a1, a2, a3, a4);

SELECT *
  FROM wct.CHOLESKY('SELECT a1,a2,a3,a4 FROM #A ORDER BY rn', 0);

This produces the following result.

Of course, there was no need to put the matrix into a temporary table. The following SQL would return the same result.

SELECT *
  FROM wct.CHOLESKY(
           N'
	SELECT
		a1,a2,a3,a4
	FROM (VALUES
		(1,6,3,4,8),
		(2,3,6,5,1),
		(3,4,5,10,7),
		(4,8,1,7,25)
		)n(rn,a1,a2,a3,a4)
	ORDER BY
		rn',
           0);

We can reformat the output into matrix form using PIVOT.

SELECT [0],
       [1],
       [2],
       [3]
  FROM wct.CHOLESKY(
           N'
	SELECT
		a1,a2,a3,a4
	FROM (VALUES
		(1,6,3,4,8),
		(2,3,6,5,1),
		(3,4,5,10,7),
		(4,8,1,7,25)
		)n(rn,a1,a2,a3,a4)
	ORDER BY
		rn',
           0) d
    PIVOT (   max(itemvalue)
              FOR colnum in ([0], [1], [2], [3])) pvt
 ORDER BY RowNum;

This produces the following result.

Example #2

In this example, the matrix A is in 3^rd normal form.

SELECT *
  FROM wct.CHOLESKY(
           N'
	SELECT 
		*
	FROM (VALUES
		(0,0,6),
		(0,1,3),
		(0,2,4),
		(0,3,8),
		(1,0,3),
		(1,1,6),
		(1,2,5),
		(1,3,1),
		(2,0,4),
		(2,1,5),
		(2,2,10),
		(2,3,7),
		(3,0,8),
		(3,1,1),
		(3,2,7),
		(3,3,25)
		)n(RowNum,ColNum,ItemValue)',
           1);

This produces the following result.

We can also verify that that A=LL' by calculating the residual error as the square root of the sum of the differences squared (see the SUMXMY2 function).

SELECT SQRT(SUM(SQUARE(#a.itemvalue - ch.itemvalue))) as err
  FROM #a
 INNER JOIN (   SELECT *
                  FROM wct.MMULTN_q(
                           'SELECT rownum,colnum,itemvalue FROM #chol', 'SELECT colnum,rownum,itemvalue FROM #chol') ) ch
    ON #a.RowNum = ch.RowNum
   AND #a.ColNum = ch.ColNum

This produces the following result.

                   err
----------------------
  1.77635683940025E-15

See Also

MATMULT - matrix multiplication for string representations of matrices

MATRIX2STRING - Turn table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.

NMATRIX2STRING - Turn third-normal form table data into a string representation of a matrix, where the columns are separated by commas and the rows are separated by semi-colons.