TMCROSS
Updated 2023-10-17 16:01:28.697000
Syntax
SELECT * FROM [wct].[TMCROSS](
<@A, nvarchar(max),>
,<@B, nvarchar(max),>
,<@Is3N, bit,>)
Description
Use the table-valued function TMCROSS 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": "886fb9e9-a5a9-4106-8689-6aa186a841e3", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "a0bb0fff-2939-49fc-93f9-bd865d59894f", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "8613535e-1ec8-4b46-a436-85c99f314d04", "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 columns in @A must equal to the number of columns in @B.
If @B IS NULL then the function returns an m-by-m matrix where m is the number of rows in @A.
If @B IS NOT NULL then the function returns an m-by-p matrix where m is the number of rows in @A and p is the number of rows in @B.
Examples
In this example we calculate A * A' using a matrix in spreadsheet format.
SELECT *
FROM wct.TMCROSS(
'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":"4"},{"RowNum":"0","ColNum":"1","ItemValue":"15"},{"RowNum":"0","ColNum":"2","ItemValue":"40"},{"RowNum":"0","ColNum":"3","ItemValue":"85"},{"RowNum":"0","ColNum":"4","ItemValue":"156"},{"RowNum":"1","ColNum":"0","ItemValue":"15"},{"RowNum":"1","ColNum":"1","ItemValue":"85"},{"RowNum":"1","ColNum":"2","ItemValue":"259"},{"RowNum":"1","ColNum":"3","ItemValue":"585"},{"RowNum":"1","ColNum":"4","ItemValue":"1111"},{"RowNum":"2","ColNum":"0","ItemValue":"40"},{"RowNum":"2","ColNum":"1","ItemValue":"259"},{"RowNum":"2","ColNum":"2","ItemValue":"820"},{"RowNum":"2","ColNum":"3","ItemValue":"1885"},{"RowNum":"2","ColNum":"4","ItemValue":"3616"},{"RowNum":"3","ColNum":"0","ItemValue":"85"},{"RowNum":"3","ColNum":"1","ItemValue":"585"},{"RowNum":"3","ColNum":"2","ItemValue":"1885"},{"RowNum":"3","ColNum":"3","ItemValue":"4369"},{"RowNum":"3","ColNum":"4","ItemValue":"8421"},{"RowNum":"4","ColNum":"0","ItemValue":"156"},{"RowNum":"4","ColNum":"1","ItemValue":"1111"},{"RowNum":"4","ColNum":"2","ItemValue":"3616"},{"RowNum":"4","ColNum":"3","ItemValue":"8421"},{"RowNum":"4","ColNum":"4","ItemValue":"16276"}]}
In this example, we supply the same matrix, except that it is now in 3 rd normal form.
SELECT *
FROM wct.TMCROSS(
'
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":"4"},{"RowNum":"0","ColNum":"1","ItemValue":"15"},{"RowNum":"0","ColNum":"2","ItemValue":"40"},{"RowNum":"0","ColNum":"3","ItemValue":"85"},{"RowNum":"0","ColNum":"4","ItemValue":"156"},{"RowNum":"1","ColNum":"0","ItemValue":"15"},{"RowNum":"1","ColNum":"1","ItemValue":"85"},{"RowNum":"1","ColNum":"2","ItemValue":"259"},{"RowNum":"1","ColNum":"3","ItemValue":"585"},{"RowNum":"1","ColNum":"4","ItemValue":"1111"},{"RowNum":"2","ColNum":"0","ItemValue":"40"},{"RowNum":"2","ColNum":"1","ItemValue":"259"},{"RowNum":"2","ColNum":"2","ItemValue":"820"},{"RowNum":"2","ColNum":"3","ItemValue":"1885"},{"RowNum":"2","ColNum":"4","ItemValue":"3616"},{"RowNum":"3","ColNum":"0","ItemValue":"85"},{"RowNum":"3","ColNum":"1","ItemValue":"585"},{"RowNum":"3","ColNum":"2","ItemValue":"1885"},{"RowNum":"3","ColNum":"3","ItemValue":"4369"},{"RowNum":"3","ColNum":"4","ItemValue":"8421"},{"RowNum":"4","ColNum":"0","ItemValue":"156"},{"RowNum":"4","ColNum":"1","ItemValue":"1111"},{"RowNum":"4","ColNum":"2","ItemValue":"3616"},{"RowNum":"4","ColNum":"3","ItemValue":"8421"},{"RowNum":"4","ColNum":"4","ItemValue":"16276"}]}
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 TMCROSS 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), 0) as x0,
1 / POWER(cast(r.n as float), 1) as x1,
1 / POWER(cast(r.n as float), 2) as x2,
1 / POWER(cast(r.n as float), 3) as x3
INTO #B
FROM
(
VALUES
(1),
(2),
(3)
) r (n);
--A * B'
SELECT *
FROM wct.TMCROSS('SELECT x0,x1,x2,x3 FROM #A order by rn', 'SELECT x0,x1,x2,x3
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":"4"},{"RowNum":"0","ColNum":"1","ItemValue":"1.875"},{"RowNum":"0","ColNum":"2","ItemValue":"1.48148148148148"},{"RowNum":"1","ColNum":"0","ItemValue":"15"},{"RowNum":"1","ColNum":"1","ItemValue":"4"},{"RowNum":"1","ColNum":"2","ItemValue":"2.40740740740741"},{"RowNum":"2","ColNum":"0","ItemValue":"40"},{"RowNum":"2","ColNum":"1","ItemValue":"8.125"},{"RowNum":"2","ColNum":"2","ItemValue":"4"},{"RowNum":"3","ColNum":"0","ItemValue":"85"},{"RowNum":"3","ColNum":"1","ItemValue":"15"},{"RowNum":"3","ColNum":"2","ItemValue":"6.48148148148148"},{"RowNum":"4","ColNum":"0","ItemValue":"156"},{"RowNum":"4","ColNum":"1","ItemValue":"25.375"},{"RowNum":"4","ColNum":"2","ItemValue":"10.0740740740741"}]}
Performing the same calculation as in the previous example, except that this time the data in matrices A and B are in 3 rd 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)
) r (n)
CROSS APPLY
(
VALUES
(0),
(1),
(2),
(3)
) c (n);
--A' * B
SELECT *
FROM wct.TMCROSS('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":"4"},{"RowNum":"0","ColNum":"1","ItemValue":"1.875"},{"RowNum":"0","ColNum":"2","ItemValue":"1.48148148148148"},{"RowNum":"1","ColNum":"0","ItemValue":"15"},{"RowNum":"1","ColNum":"1","ItemValue":"4"},{"RowNum":"1","ColNum":"2","ItemValue":"2.40740740740741"},{"RowNum":"2","ColNum":"0","ItemValue":"40"},{"RowNum":"2","ColNum":"1","ItemValue":"8.125"},{"RowNum":"2","ColNum":"2","ItemValue":"4"},{"RowNum":"3","ColNum":"0","ItemValue":"85"},{"RowNum":"3","ColNum":"1","ItemValue":"15"},{"RowNum":"3","ColNum":"2","ItemValue":"6.48148148148148"},{"RowNum":"4","ColNum":"0","ItemValue":"156"},{"RowNum":"4","ColNum":"1","ItemValue":"25.375"},{"RowNum":"4","ColNum":"2","ItemValue":"10.0740740740741"}]}
See Also
MATMULT - matrix multiplication for string representations of matrices
MCROSS - table-valued function to calculate A' * A or A' * B
MMULT_Q - Calculate the matrix product of two arrays.
CROSSPROD - scalar function to calculate A' * A or A' * B