QRP
Updated 2024-06-21 13:18:56.070000
Syntax
SELECT * FROM [westclintech].[wct].[QR](
<@A, nvarchar(max),>
,<@toler, float,>)
Description
Use the table-valued function QRP to compute a QR decomposition of a matrix(A) such that:
AP=QR
Arguments
@A
The string representation of the matrix with columns separated by commas and rows separated by semi-colons.
@toler
The tolerance used to determine when to terminate column-pivoting. float.
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": "Q", "colDatatype": "nvarchar(max)", "colDesc": "The Q matrix"}, {"ordinal": 1, "colName": "R", "colDatatype": "nvarchar(max)", "colDesc": "The R matrix"}, {"ordinal": 2, "colName": "P", "colDatatype": "nvarchar(max)", "colDesc": "The P matrix"}, {"ordinal": 3, "colName": "rnk", "colDatatype": "int", "colDesc": "The matrix rank"}]}
Remarks
If @A is NULL an error is returned.
If @toler is NULL then 1.05367121277235E-08 is used. QRdecomp_q function for more complicated queries.
If @A is improperly formatted an error will be returned.
Examples
Example #1
In this example, we explicitly create the string representation of the @A matrix. We store the output of the QRP table-valued function in variables @Q, @R, @P and @rnk. We then use the XLeratorDB MATRIX, MATMULT and MATINVERSE functions to return the original matrix in row / column format. Note that we have rounded and cast the output as tinyint purely for presentation purposes.
DECLARE @Q as varchar(max);
DECLARE @R as varchar(max);
DECLARE @P as varchar(max);
DECLARE @rnk as tinyint;
DECLARE @A as varchar(max)
= '76,65,58,19,77;
48,7,85,34,69;
49,25,93,4,2;
86,47,27,72,71;
62,14,100,1,88;
70,81,39,95,94;
16,83,43,32,80'
SELECT @Q = Q,
@R = R,
@P = P,
@rnk = rnk
FROM wct.QRP(@A, NULL);
SELECT CAST(ROUND([0], 0) as tinyint) as [0],
CAST(ROUND([1], 0) as tinyint) as [1],
CAST(ROUND([2], 0) as tinyint) as [2],
CAST(ROUND([3], 0) as tinyint) as [3],
CAST(ROUND([4], 0) as tinyint) as [4]
FROM wct.MATRIX(wct.MATMULT(wct.MATMULT(@Q, @R), wct.TRANSPOSE(@P))) d
PIVOT ( MAX(ItemValue)
FOR ColNum IN ([0], [1], [2], [3], [4])) pvt
ORDER BY RowNum;
This produces the following result.
{
"columns": [
{
"field": "0",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "1",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "2",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "3",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "4",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
}
],
"rows": [
{
"0": 76,
"1": 65,
"2": 58,
"3": 19,
"4": 77
},
{
"0": 48,
"1": 7,
"2": 85,
"3": 34,
"4": 69
},
{
"0": 49,
"1": 25,
"2": 93,
"3": 4,
"4": 2
},
{
"0": 86,
"1": 47,
"2": 27,
"3": 72,
"4": 71
},
{
"0": 62,
"1": 14,
"2": 100,
"3": 1,
"4": 88
},
{
"0": 70,
"1": 81,
"2": 39,
"3": 95,
"4": 94
},
{
"0": 16,
"1": 83,
"2": 43,
"3": 32,
"4": 80
}
]
}
The following SQL returns the rank of the @A matrix.
SELECT
@rnk as [Rank];
This produces the following result.
Rank
----
5
Example #2
In this example, the @A matrix is in a table which is in spreadsheet format. We will use the Matrix2String_q function to put the @A matrix into the required format.
DECLARE @Q as varchar(max);
DECLARE @R as varchar(max);
DECLARE @P as varchar(max);
DECLARE @rnk as tinyint;
SELECT *
INTO #a
FROM ( VALUES (1, 35, 1, 6, 26, 19, 24),
(2, 3, 32, 7, 21, 23, 25),
(3, 31, 9, 2, 22, 27, 20),
(4, 8, 28, 33, 17, 10, 15),
(5, 30, 5, 34, 12, 14, 16),
(6, 4, 36, 29, 13, 18, 11)) n (rn, [1], [2], [3], [4], [5], [6]);
SELECT @Q = Q,
@R = R,
@P = P,
@rnk = rnk
FROM wct.QRP(wct.MATRIX2STring_q('SELECT [1],[2],[3],[4],[5],[6] FROM #a ORDER BY rn'), NULL);
SELECT CAST(ROUND([0], 0) as tinyint) as [0],
CAST(ROUND([1], 0) as tinyint) as [1],
CAST(ROUND([2], 0) as tinyint) as [2],
CAST(ROUND([3], 0) as tinyint) as [3],
CAST(ROUND([4], 0) as tinyint) as [4],
CAST(ROUND([5], 0) as tinyint) as [5]
FROM wct.MATRIX(wct.MATMULT(wct.MATMULT(@Q, @R), wct.TRANSPOSE(@P))) d
PIVOT ( MAX(ItemValue)
FOR ColNum IN ([0], [1], [2], [3], [4], [5])) pvt
ORDER BY RowNum;
DROP TABLE #a;
This produces the following result.
{
"columns": [
{
"field": "0",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "1",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "2",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "3",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "4",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "5",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
}
],
"rows": [
{
"0": 35,
"1": 1,
"2": 6,
"3": 26,
"4": 19,
"5": 24
},
{
"0": 3,
"1": 32,
"2": 7,
"3": 21,
"4": 23,
"5": 25
},
{
"0": 31,
"1": 9,
"2": 2,
"3": 22,
"4": 27,
"5": 20
},
{
"0": 8,
"1": 28,
"2": 33,
"3": 17,
"4": 10,
"5": 15
},
{
"0": 30,
"1": 5,
"2": 34,
"3": 12,
"4": 14,
"5": 16
},
{
"0": 4,
"1": 36,
"2": 29,
"3": 13,
"4": 18,
"5": 11
}
]
}
As in Example #1, we can get the rank with the following SQL.
SELECT
@rnk as [Rank];
This produces the following result.
Rank
----
5
Note that the input matrix is rank-deficient; it has a rank of 5 but there are 6 columns.
Example #3
In this example, the matrix is in a table in 3rd-normal form. We will use the XLeratorDB aggregate function NMATRIX2String to put the @A matrix into the required format.
DECLARE @Q as varchar(max);
DECLARE @R as varchar(max);
DECLARE @P as varchar(max);
DECLARE @rnk as tinyint;
SELECT *
INTO #a
FROM ( VALUES (0, 0, 49),
(0, 1, 42),
(0, 2, 35),
(0, 3, 28),
(0, 4, 21),
(0, 5, 14),
(0, 6, 7),
(1, 0, 42),
(1, 1, 36),
(1, 2, 30),
(1, 3, 24),
(1, 4, 18),
(1, 5, 12),
(1, 6, 6),
(2, 0, 35),
(2, 1, 30),
(2, 2, 25),
(2, 3, 20),
(2, 4, 15),
(2, 5, 10),
(2, 6, 5),
(3, 0, 28),
(3, 1, 24),
(3, 2, 20),
(3, 3, 16),
(3, 4, 12),
(3, 5, 8),
(3, 6, 4),
(4, 0, 21),
(4, 1, 18),
(4, 2, 15),
(4, 3, 12),
(4, 4, 9),
(4, 5, 6),
(4, 6, 3),
(5, 0, 14),
(5, 1, 12),
(5, 2, 10),
(5, 3, 8),
(5, 4, 6),
(5, 5, 4),
(5, 6, 2),
(6, 0, 7),
(6, 1, 6),
(6, 2, 5),
(6, 3, 4),
(6, 4, 3),
(6, 5, 2),
(6, 6, 1)) n (rn, colno, val);
SELECT @Q = Q,
@R = R,
@P = P,
@rnk = rnk
from (SELECT wct.NMATRIX2STRING(rn, colno, val) AS A FROM #a) n
cross apply wct.QRP(n.A, NULL);
SELECT CAST(ROUND([0], 0) as tinyint) as [0],
CAST(ROUND([1], 0) as tinyint) as [1],
CAST(ROUND([2], 0) as tinyint) as [2],
CAST(ROUND([3], 0) as tinyint) as [3],
CAST(ROUND([4], 0) as tinyint) as [4],
CAST(ROUND([5], 0) as tinyint) as [5],
CAST(ROUND([6], 0) as tinyint) as [6]
FROM wct.MATRIX(wct.MATMULT(wct.MATMULT(@Q, @R), wct.TRANSPOSE(@P))) d
PIVOT ( MAX(ItemValue)
FOR ColNum IN ([0], [1], [2], [3], [4], [5], [6])) pvt
ORDER BY RowNum;
DROP TABLE #a;
This returns the following result.
{
"columns": [
{
"field": "0",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "1",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "2",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "3",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "4",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "5",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
},
{
"field": "6",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
}
],
"rows": [
{
"0": 49,
"1": 42,
"2": 35,
"3": 28,
"4": 21,
"5": 14,
"6": 7
},
{
"0": 42,
"1": 36,
"2": 30,
"3": 24,
"4": 18,
"5": 12,
"6": 6
},
{
"0": 35,
"1": 30,
"2": 25,
"3": 20,
"4": 15,
"5": 10,
"6": 5
},
{
"0": 28,
"1": 24,
"2": 20,
"3": 16,
"4": 12,
"5": 8,
"6": 4
},
{
"0": 21,
"1": 18,
"2": 15,
"3": 12,
"4": 9,
"5": 6,
"6": 3
},
{
"0": 14,
"1": 12,
"2": 10,
"3": 8,
"4": 6,
"5": 4,
"6": 2
},
{
"0": 7,
"1": 6,
"2": 5,
"3": 4,
"4": 3,
"5": 2,
"6": 1
}
]
}
As in Example #1, we can get the rank with the following SQL.
SELECT @rnk as [Rank];
This produces the following result.
Rank
----
1
Note that the input matrix is rank-deficient; it has a rank of 1 but there are 7 columns.
See Also
MATMULT - matrix multiplication for string representations of matrices