QRSolve
Updated 2024-06-25 21:09:29.017000
Syntax
SELECT * FROM [westclintech].[QRSOLVE] (
<@A, nvarchar(max),>
,<@B, nvarchar(max),>)
Description
Use the scalar function QRSolve to solve systems of equations via the QR decomposition. You can use QRSolve for under- and over-determined systems of equations as well for exact solutions for equations in the form of
Ax=B
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
nvarchar(max)
Remarks
If @A is NULL an error is returned.
If @B is NULL nothing is returned. QRdecomp_q function for more complicated queries.
@A & @B must have the same number of rows or a nothing is returned.
Examples
Example #1
Solve for
\begin{bmatrix}21&100&-29&-42&-91\\52&-19&5&-22&83\\-12&-69&-33&-7&-6\\-16&-61&3&-22&86\\1&-16&20&38&14\end{bmatrix}\begin{bmatrix}x_1\\x_2\\x_3\\x_4\\x_5\end{bmatrix}=\begin{bmatrix}-43\\-56\\53\\51\\40\end{bmatrix}
We will create the @A and @B strings explicitly.
DECLARE @A as varchar(max)
= '21,100,-29,-42,-91;
52,-19,5,-22,83;
-12,-69,-33,-7,-6;
-16,-61,3,-22,86;
1,-16,20,38,14';
DECLARE @b as varchar(max) = '-43;
-56;
53;
51;
40';
DECLARE @x as varchar(max) = wct.QRSOLVE(@A, @b);
SELECT @x as x;
This produces the following result.
x
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-5.80447914121309;8.58166698168588;-21.9057482765838;12.797221562171;9.63797835838306
We can reformat the output using the XLeratorDB table-value function MATRIX.
DECLARE @A as varchar(max)
= '21,100,-29,-42,-91;
52,-19,5,-22,83;
-12,-69,-33,-7,-6;
-16,-61,3,-22,86;
1,-16,20,38,14';
DECLARE @b as varchar(max) = '-43;
-56;
53;
51;
40';
DECLARE @x as varchar(max) = wct.QRSOLVE(@A, @b);
SELECT *
FROM wct.matrix(@x);
This produces the following result.
RowNum ColNum ItemValue
----------- ----------- ----------------------
0 0 -5.80447914121309
1 0 8.58166698168588
2 0 -21.9057482765838
3 0 12.797221562171
4 0 9.63797835838306
Since this is an exact solution, we can run the following SQL, using the SUMXMY2 function (calculating the SUM((x-y)2)), to check to make sure that residual error is pretty close to zero.
DECLARE @A as varchar(max)
= '21,100,-29,-42,-91;
52,-19,5,-22,83;
-12,-69,-33,-7,-6;
-16,-61,3,-22,86;
1,-16,20,38,14';
DECLARE @b as varchar(max) = '-43;
-56;
53;
51;
40';
DECLARE @x as varchar(max) = wct.QRSOLVE(@A, @b);
SELECT SQRT(wct.SUMXMY2(wct.MATMULT(@A, @x), @B)) as resid;
This produces the following result.
resid
----------------------
3.12976129552829E-12
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