MINVERSEN_q

Updated 2023-10-16 21:03:08.347000

Syntax

SELECT * FROM [westclintech].[wct].[MINVERSEN_q] (
   <@Matrix_RangeQuery, nvarchar(max),>)

Description

Use MINVERSEN_q to calculate the matrix inverse of a square (N x N) array stored in 3rd normal form.

Arguments

@Matrix_RangeQuery

the select statement, as text, used to determine the square (N x N) matrix to be used in this function. The resultant table from the SELECT statement must embody the row number, column number, and data value. The data value 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": "8116c5d0-a36d-4ab3-bf26-e18908e3a24e", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "bc0f1368-6159-4eaa-b268-f56a3efcceb3", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "b4d30f63-c199-4bf7-847b-0c98b05f1f67", "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 MINVERSEN function for simpler queries.

Use MINVERSE_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.

Examples

In this example, we calculate the matrix product directly from the SELECT statement.

SELECT C.rownum,

       C.ColNum,

       ROUND(C.ItemValue, 8)

FROM wct.MINVERSEN_q('SELECT 0,0,1 UNION ALL

      SELECT 0,1,-2 UNION ALL

      SELECT 0,2,3 UNION ALL

      SELECT 0,3,-4 UNION ALL

      SELECT 1,0,5 UNION ALL

      SELECT 1,1,5 UNION ALL

      SELECT 1,2,7 UNION ALL

      SELECT 1,3,8 UNION ALL

      SELECT 2,0,9 UNION ALL

      SELECT 2,1,10 UNION ALL

      SELECT 2,2,11 UNION ALL

      SELECT 2,3,12 UNION ALL

      SELECT 3,0,13 UNION ALL

      SELECT 3,1,14 UNION ALL

      SELECT 3,2,15 UNION ALL

      SELECT 3,3,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,

    Colno int,

    val float

);

INSERT INTO #i

SELECT '1A',

       0,

       0,

       1.0

UNION ALL

SELECT '1A',

       0,

       1,

       -2.0

UNION ALL

SELECT '1A',

       0,

       2,

       3.0

UNION ALL

SELECT '1A',

       0,

       3,

       -4.0

UNION ALL

SELECT '1A',

       1,

       0,

       5.0

UNION ALL

SELECT '1A',

       1,

       1,

       5.0

UNION ALL

SELECT '1A',

       1,

       2,

       7.0

UNION ALL

SELECT '1A',

       1,

       3,

       8.0

UNION ALL

SELECT '1A',

       2,

       0,

       9.0

UNION ALL

SELECT '1A',

       2,

       1,

       10.0

UNION ALL

SELECT '1A',

       2,

       2,

       11.0

UNION ALL

SELECT '1A',

       2,

       3,

       12.0

UNION ALL

SELECT '1A',

       3,

       0,

       13.0

UNION ALL

SELECT '1A',

       3,

       1,

       14.0

UNION ALL

SELECT '1A',

       3,

       2,

       15.0

UNION ALL

SELECT '1A',

       3,

       3,

       16.0;

SELECT C.rownum,

       C.ColNum,

       ROUND(C.ItemValue, 8)

FROM wct.MINVERSEN_q('SELECT rowno

      ,colno

      ,val

      FROM #i

      WHERE MatrixID = ''1A''') 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 return the results in matrix form, we can use the PIVOT function, though this requires knowing the number of columns returned by the function (which is equal to the number of rows).

SELECT [0],

       [1],

       [2],

       [3]

FROM

(

    SELECT C.rownum,

           C.ColNum,

           Round(ItemValue, 8) as ItemValue

    FROM wct.MINVERSEN_q('SELECT 0,0,1 UNION ALL

      SELECT 0,1,-2 UNION ALL

      SELECT 0,2,3 UNION ALL

      SELECT 0,3,-4 UNION ALL

      SELECT 1,0,5 UNION ALL

      SELECT 1,1,5 UNION ALL

      SELECT 1,2,7 UNION ALL

      SELECT 1,3,8 UNION ALL

      SELECT 2,0,9 UNION ALL

      SELECT 2,1,10 UNION ALL

      SELECT 2,2,11 UNION ALL

      SELECT 2,3,12 UNION ALL

      SELECT 3,0,13 UNION ALL

      SELECT 3,1,14 UNION ALL

      SELECT 3,2,15 UNION ALL

      SELECT 3,3,16') C

) M

PIVOT

(

    MAX(ItemValue)

    FOR colnum IN ([0], [1], [2], [3])

) AS pvt

ORDER BY rownum;

This produces the following result. In this example, we will use the MINVERSEN_q function and the MMULTN_q function to return the identity matrix.

DECLARE @MString as varchar(4000),

        @IString as varchar(4000);

SET @MSTring

    = 'SELECT 0,0,1 UNION ALL

      SELECT 0,1,-2 UNION ALL

      SELECT 0,2,3 UNION ALL

      SELECT 0,3,-4 UNION ALL

      SELECT 1,0,5 UNION ALL

      SELECT 1,1,5 UNION ALL

      SELECT 1,2,7 UNION ALL

      SELECT 1,3,8 UNION ALL

      SELECT 2,0,9 UNION ALL

      SELECT 2,1,10 UNION ALL

      SELECT 2,2,11 UNION ALL

      SELECT 2,3,12 UNION ALL

      SELECT 3,0,13 UNION ALL

      SELECT 3,1,14 UNION ALL

      SELECT 3,2,15 UNION ALL

      SELECT 3,3,16';

SET @Istring = 'SELECT C.*

FROM wct.MINVERSEN_q(''' + @Mstring + '''

      ) C';

SELECT [0],

       [1],

       [2],

       [3]

FROM

(

    SELECT C.rownum,

           c.colnum,

           round(c.ItemValue, 8) as ItemValue

    FROM wct.MMULTN_q(@Mstring, @IString) C

) M

PIVOT

(

    MAX(ItemValue)

    FOR colnum IN ([0], [1], [2], [3])

) AS 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"}]}