MCROSS
Updated 2023-10-16 20:22:32.930000
Syntax
SELECT * FROM [wct].[MCROSS](
<@A, nvarchar(max),>
,<@B, nvarchar(max),>
,<@Is3N, bit,>)
Description
Use the table-valued function MCROSS to calculate the matrix cross-product of 2 matrices. When only one matrix is passed into the function the result is A' * A, otherwise the result is A' * B.
Arguments
@A
the SELECT statement which returns the resultant table containing the A matrix.
@B
the SELECT statement which returns the resultant table containing the B matrix.
@Is3N
a bit value identifying the form for the resultant table returned by @A and @B. Enter 'True' for a resultant table in 3rd normal form. Enter 'False' for a de-normalized table in 'spreadsheet' form.
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": "4625b0a1-1c99-482f-9470-a1864da9d08b", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "4fd84039-9a2d-4039-a5b3-f245d5e61671", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "8bc6399c-52b4-490b-aa56-e89b9213fc23", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}
Remarks
If @Is3N is NULL then @Is3N = 'False'.
If @Is3N is'True' then the result table should be returned as row, column, and value.
If the resultant table contains NULL, then NULL will be returned.
The function returns an error if the array contains a non-numeric value.
If @B IS NULL then the function computes A' * A.
If @B IS NOT NULL then the number of rows in @A must equal to the number of rows in @B.
If @B IS NULL then the function returns an n-by-n matrix where n is the number of columns in @A.
If @B IS NOT NULL then the function returns an n-by-p matrix where n is the number of columns in @A and p is the number of columns in @B.
Examples
In this example we calculate A' * A using a matrix in spreadsheet format.
SELECT *
FROM wct.MCROSS(
'SELECT
*
FROM (VALUES
(1,1,1,1),
(1,2,4,8),
(1,3,9,27),
(1,4,16,64),
(1,5,25,125)
)n(x1,x2,x3,x4)',
NULL,
'False'
);
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":"5"},{"RowNum":"0","ColNum":"1","ItemValue":"15"},{"RowNum":"0","ColNum":"2","ItemValue":"55"},{"RowNum":"0","ColNum":"3","ItemValue":"225"},{"RowNum":"1","ColNum":"0","ItemValue":"15"},{"RowNum":"1","ColNum":"1","ItemValue":"55"},{"RowNum":"1","ColNum":"2","ItemValue":"225"},{"RowNum":"1","ColNum":"3","ItemValue":"979"},{"RowNum":"2","ColNum":"0","ItemValue":"55"},{"RowNum":"2","ColNum":"1","ItemValue":"225"},{"RowNum":"2","ColNum":"2","ItemValue":"979"},{"RowNum":"2","ColNum":"3","ItemValue":"4425"},{"RowNum":"3","ColNum":"0","ItemValue":"225"},{"RowNum":"3","ColNum":"1","ItemValue":"979"},{"RowNum":"3","ColNum":"2","ItemValue":"4425"},{"RowNum":"3","ColNum":"3","ItemValue":"20515"}]}
In this example, we supply the same matrix, except that it is now in 3rd normal form.
SELECT *
FROM wct.MCROSS(
'
SELECT
r.n,
c.n,
POWER(r.n,c.n)
FROM (VALUES (1),(2),(3),(4),(5))r(n)
CROSS APPLY (VALUES (0),(1),(2),(3))c(n)
ORDER BY 1,2',
NULL,
'True'
);
This produces the following results.
{"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":"5"},{"RowNum":"0","ColNum":"1","ItemValue":"15"},{"RowNum":"0","ColNum":"2","ItemValue":"55"},{"RowNum":"0","ColNum":"3","ItemValue":"225"},{"RowNum":"1","ColNum":"0","ItemValue":"15"},{"RowNum":"1","ColNum":"1","ItemValue":"55"},{"RowNum":"1","ColNum":"2","ItemValue":"225"},{"RowNum":"1","ColNum":"3","ItemValue":"979"},{"RowNum":"2","ColNum":"0","ItemValue":"55"},{"RowNum":"2","ColNum":"1","ItemValue":"225"},{"RowNum":"2","ColNum":"2","ItemValue":"979"},{"RowNum":"2","ColNum":"3","ItemValue":"4425"},{"RowNum":"3","ColNum":"0","ItemValue":"225"},{"RowNum":"3","ColNum":"1","ItemValue":"979"},{"RowNum":"3","ColNum":"2","ItemValue":"4425"},{"RowNum":"3","ColNum":"3","ItemValue":"20515"}]}
In this example we supply both an A and a B matrix in spreadsheet form and calculate A' * B. We will do this by creating 2 temporary tables for the A and the B matrix and then selecting from the tables in the MCROSS function.
--Create matrix A
SELECT r.n as rn,
POWER(r.n, 0) as x0,
POWER(r.n, 1) as x1,
POWER(r.n, 2) as x2,
POWER(r.n, 3) as x3
INTO #A
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5)
) r (n);
--Create matrix B
SELECT r.n as rn,
1 / POWER(cast(r.n as float), 1) as x0,
1 / POWER(cast(r.n as float), 2) as x1,
1 / POWER(cast(r.n as float), 3) as x2
INTO #B
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5)
) r (n);
--A' * B
SELECT *
FROM wct.MCROSS('SELECT x0,x1,x2,x3 FROM #A order by rn', 'SELECT x0,x1,x2 FROM
#B order by rn', 'False');
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.28333333333333"},{"RowNum":"0","ColNum":"1","ItemValue":"1.46361111111111"},{"RowNum":"0","ColNum":"2","ItemValue":"1.18566203703704"},{"RowNum":"1","ColNum":"0","ItemValue":"5"},{"RowNum":"1","ColNum":"1","ItemValue":"2.28333333333333"},{"RowNum":"1","ColNum":"2","ItemValue":"1.46361111111111"},{"RowNum":"2","ColNum":"0","ItemValue":"15"},{"RowNum":"2","ColNum":"1","ItemValue":"5"},{"RowNum":"2","ColNum":"2","ItemValue":"2.28333333333333"},{"RowNum":"3","ColNum":"0","ItemValue":"55"},{"RowNum":"3","ColNum":"1","ItemValue":"15"},{"RowNum":"3","ColNum":"2","ItemValue":"5"}]}
Performing the same calculation as in the previous example, except that this time the data in matrices A and B are in 3rd normal form.
--Create the A Matrix
SELECT r.n as rowno,
c.n as colno,
POWER(r.n, c.n) as x
INTO #A
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5)
) r (n)
CROSS APPLY
(
VALUES
(0),
(1),
(2),
(3)
) c (n);
--Create the B Matrix
SELECT r.n as rowno,
c.n as colno,
1 / POWER(cast(r.n as float), c.n) as x
INTO #B
FROM
(
VALUES
(1),
(2),
(3),
(4),
(5)
) r (n)
CROSS APPLY
(
VALUES
(1),
(2),
(3)
) c (n);
--A' * B
SELECT *
FROM wct.MCROSS('SELECT * FROM #A order by 1,2', 'SELECT * FROM #B order by 1,2',
'True');
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.28333333333333"},{"RowNum":"0","ColNum":"1","ItemValue":"1.46361111111111"},{"RowNum":"0","ColNum":"2","ItemValue":"1.18566203703704"},{"RowNum":"1","ColNum":"0","ItemValue":"5"},{"RowNum":"1","ColNum":"1","ItemValue":"2.28333333333333"},{"RowNum":"1","ColNum":"2","ItemValue":"1.46361111111111"},{"RowNum":"2","ColNum":"0","ItemValue":"15"},{"RowNum":"2","ColNum":"1","ItemValue":"5"},{"RowNum":"2","ColNum":"2","ItemValue":"2.28333333333333"},{"RowNum":"3","ColNum":"0","ItemValue":"55"},{"RowNum":"3","ColNum":"1","ItemValue":"15"},{"RowNum":"3","ColNum":"2","ItemValue":"5"}]}
See Also
CROSSPROD - scalar function to calculate A' * A or A' * B
MATMULT - matrix multiplication for string representations of matrices
MMULT_Q - Calculate the matrix product of two arrays.
TCROSSPROD - scalar function to calculate A * A' or A * B'
TMCROSS - table-valued function to calculate A * A' or A * B'