MINVERSE_q
Updated 2023-10-16 20:50:28.903000
Syntax
SELECT * FROM [westclintech].[wct].[MINVERSE_q] (
<@Matrix_RangeQuery, nvarchar(max),>)
Description
Use the table-valued function MINVERSE_q to calculate the matrix inverse of a square (N x N) array.
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
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "56fe3751-e6f3-4e6b-8f01-fe25752318ba", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "322240de-da79-4e67-8e48-e0d568808596", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "2bf487bf-4e1a-4b86-929c-de1eed0e7443", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}
Remarks
The number of columns in the array must be equal to the number of rows or an error will be returned.
If the matrix determinant is zero, an error will be returned.
Use the MINVERSE function for simpler queries.
Use MINVERSEN_q for a table in normal form.
If the array contains NULL, then NULL will be returned.
The function returns an error if the array contains a non-numeric value.
The table returned from MINVERSE_q multiplied by the input matrix returns the identity matrix.
Examples
In this example, we calculate the matrix inverse directly from the SELECT statement.
SELECT C.rownum,
C.ColNum,
ROUND(C.ItemValue, 8)
FROM wct.MINVERSE_q('SELECT 1,-2,3,-4 UNION ALL
SELECT 5,5,7,8 UNION ALL
SELECT 9,10,11,12 UNION ALL
SELECT 13,14,15,16') C;
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":"","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"rownum":"0","ColNum":"0","":"-0.0625"},{"rownum":"0","ColNum":"1","":"0.75"},{"rownum":"0","ColNum":"2","":"-3.1875"},{"rownum":"0","ColNum":"3","":"2"},{"rownum":"1","ColNum":"0","":"0"},{"rownum":"1","ColNum":"1","":"-1"},{"rownum":"1","ColNum":"2","":"2"},{"rownum":"1","ColNum":"3","":"-1"},{"rownum":"2","ColNum":"0","":"0.1875"},{"rownum":"2","ColNum":"1","":"-0.25"},{"rownum":"2","ColNum":"2","":"1.5625"},{"rownum":"2","ColNum":"3","":"-1"},{"rownum":"3","ColNum":"0","":"-0.125"},{"rownum":"3","ColNum":"1","":"0.5"},{"rownum":"3","ColNum":"2","":"-0.625"},{"rownum":"3","ColNum":"3","":"0.25"}]}
If we wanted to SELECT data from a TABLE or a VIEW, the SELECT statement would look like this.
CREATE TABLE #i
(
MatrixID nvarchar(5),
rowno int,
Col00 float,
Col01 float,
Col02 float,
Col03 float,
Col04 float,
Col05 float,
Col06 float,
Col07 float,
Col08 float,
Col09 float
);
INSERT INTO #i
(
Matrixid,
rowno,
Col00,
col01,
col02,
col03
)
VALUES
('1A', 0, 1.0, -2.0, 3.0, -4.0);
INSERT INTO #i
(
Matrixid,
rowno,
Col00,
col01,
col02,
col03
)
VALUES
('1A', 0, 5.0, 5.0, 7.0, 8.0);
INSERT INTO #i
(
Matrixid,
rowno,
Col00,
col01,
col02,
col03
)
VALUES
('1A', 0, 9.0, 10.0, 11.0, 12.0);
INSERT INTO #i
(
Matrixid,
rowno,
Col00,
col01,
col02,
col03
)
VALUES
('1A', 0, 13.0, 14.0, 15.0, 16.0);
SELECT C.rownum,
C.ColNum,
ROUND(C.ItemValue, 8) as ItemValue
FROM wct.MINVERSE_q('SELECT col00
,col01
,col02
,col03
FROM #i') C;
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":"-0.0625"},{"rownum":"0","ColNum":"1","ItemValue":"0.75"},{"rownum":"0","ColNum":"2","ItemValue":"-3.1875"},{"rownum":"0","ColNum":"3","ItemValue":"2"},{"rownum":"1","ColNum":"0","ItemValue":"0"},{"rownum":"1","ColNum":"1","ItemValue":"-1"},{"rownum":"1","ColNum":"2","ItemValue":"2"},{"rownum":"1","ColNum":"3","ItemValue":"-1"},{"rownum":"2","ColNum":"0","ItemValue":"0.1875"},{"rownum":"2","ColNum":"1","ItemValue":"-0.25"},{"rownum":"2","ColNum":"2","ItemValue":"1.5625"},{"rownum":"2","ColNum":"3","ItemValue":"-1"},{"rownum":"3","ColNum":"0","ItemValue":"-0.125"},{"rownum":"3","ColNum":"1","ItemValue":"0.5"},{"rownum":"3","ColNum":"2","ItemValue":"-0.625"},{"rownum":"3","ColNum":"3","ItemValue":"0.25"}]}
In this example, we will use the MINVERSE_q function and the MMULT_q function to return the identity matrix.
DECLARE @MString as varchar(4000),
@IString as varchar(4000);
SET @Mstring = 'SELECT 1,-2,3,-4 UNION ALL
SELECT 5,5,7,8 UNION ALL
SELECT 9,10,11,12 UNION ALL
SELECT 13,14,15,16';
SET @IString
= 'SELECT [0],[1],[2],[3]
FROM (
SELECT C.*
FROM wct.MINVERSE_q(''' + @MString
+ '''
) C
) M PIVOT (
MAX(ItemValue)
FOR colnum IN ([0],[1],[2],[3])
) AS pvt
ORDER BY rownum';
SELECT ROUND([0], 12) as [0],
ROUND([1], 12) as [1],
ROUND([2], 12) as [2],
ROUND([3], 12) as [3]
FROM
(
SELECT *
FROM wct.MMULT_q(
'SELECT 1,-2,3,-4 UNION ALL
SELECT 5,5,7,8 UNION ALL
SELECT 9,10,11,12 UNION ALL
SELECT 13,14,15,16',
@Istring
)
) d
PIVOT
(
MAX(ItemValue)
for ColNum in ([0], [1], [2], [3])
) pvt;
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"}],"rows":[{"0":"1","1":"0","2":"0","3":"0"},{"0":"0","1":"0","2":"0","3":"1"},{"0":"0","1":"1","2":"0","3":"0"},{"0":"0","1":"0","2":"1","3":"0"}]}