CHOLESKY
Updated 2024-04-11 15:34:02.407000
Syntax
SELECT * FROM [westclintech].[wct].[CHOLESKY](
<@A, nvarchar(max),>
,<@Is3N, bit,>)
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
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
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"ordinal": 0, "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based index identifying the matrix row number"}, {"ordinal": 1, "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based index identifying the matrix column number"}, {"ordinal": 2, "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}
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.
{
"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": 2.449489742783178e+000
},
{
"RowNum": 0,
"ColNum": 1,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 0,
"ColNum": 2,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 0,
"ColNum": 3,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 1,
"ColNum": 0,
"ItemValue": 1.224744871391589e+000
},
{
"RowNum": 1,
"ColNum": 1,
"ItemValue": 2.121320343559642e+000
},
{
"RowNum": 1,
"ColNum": 2,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 1,
"ColNum": 3,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 2,
"ColNum": 0,
"ItemValue": 1.632993161855452e+000
},
{
"RowNum": 2,
"ColNum": 1,
"ItemValue": 1.414213562373095e+000
},
{
"RowNum": 2,
"ColNum": 2,
"ItemValue": 2.309401076758503e+000
},
{
"RowNum": 2,
"ColNum": 3,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 3,
"ColNum": 0,
"ItemValue": 3.265986323710905e+000
},
{
"RowNum": 3,
"ColNum": 1,
"ItemValue": -1.414213562373096e+000
},
{
"RowNum": 3,
"ColNum": 2,
"ItemValue": 1.587713240271470e+000
},
{
"RowNum": 3,
"ColNum": 3,
"ItemValue": 3.132491021535417e+000
}
]
}
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.
{
"columns": [
{
"field": "0",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell",
"minWidth": 180
},
{
"field": "1",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell",
"minWidth": 180
},
{
"field": "2",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell",
"minWidth": 180
},
{
"field": "3",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell",
"minWidth": 180
}
],
"rows": [
{
"0": 2.449489742783178e+000,
"1": 0.000000000000000e+000,
"2": 0.000000000000000e+000,
"3": 0.000000000000000e+000
},
{
"0": 1.224744871391589e+000,
"1": 2.121320343559642e+000,
"2": 0.000000000000000e+000,
"3": 0.000000000000000e+000
},
{
"0": 1.632993161855452e+000,
"1": 1.414213562373095e+000,
"2": 2.309401076758503e+000,
"3": 0.000000000000000e+000
},
{
"0": 3.265986323710905e+000,
"1": -1.414213562373096e+000,
"2": 1.587713240271470e+000,
"3": 3.132491021535417e+000
}
]
}
Example #2
In this example, the matrix A is in 3rd 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.
{
"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": 2.449489742783178e+000
},
{
"RowNum": 0,
"ColNum": 1,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 0,
"ColNum": 2,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 0,
"ColNum": 3,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 1,
"ColNum": 0,
"ItemValue": 1.224744871391589e+000
},
{
"RowNum": 1,
"ColNum": 1,
"ItemValue": 2.121320343559642e+000
},
{
"RowNum": 1,
"ColNum": 2,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 1,
"ColNum": 3,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 2,
"ColNum": 0,
"ItemValue": 1.632993161855452e+000
},
{
"RowNum": 2,
"ColNum": 1,
"ItemValue": 1.414213562373095e+000
},
{
"RowNum": 2,
"ColNum": 2,
"ItemValue": 2.309401076758503e+000
},
{
"RowNum": 2,
"ColNum": 3,
"ItemValue": 0.000000000000000e+000
},
{
"RowNum": 3,
"ColNum": 0,
"ItemValue": 3.265986323710905e+000
},
{
"RowNum": 3,
"ColNum": 1,
"ItemValue": -1.414213562373096e+000
},
{
"RowNum": 3,
"ColNum": 2,
"ItemValue": 1.587713240271470e+000
},
{
"RowNum": 3,
"ColNum": 3,
"ItemValue": 3.132491021535417e+000
}
]
}
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