TCROSSPROD
Updated 2023-10-17 15:56:25.777000
Syntax
SELECT [westclintech].[wct].[TCROSSPROD](
<@A, nvarchar(max),>
,<@B, nvarchar(max),>)
Description
Use the scalar function TCROSSPROD 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'. TCROSSPROD produces the same result as MATMULT(@A,TRANSPOSE(@A)) or MATMULT(@A,TRANSPOSE(@B)) with less than half of the floating point operations.
TCROSSPROD expects a string representation of the matrix with columns separated by commas and rows separated by semi-colons.
Arguments
@A
A string representation of the A matrix.
@B
A string representation of the B matrix.
Return Type
nvarchar(max)
Remarks
The string representations of @A and/or @B must only contain numbers, commas (to separate the columns), and semi-colons to separate the rows.
Consecutive commas will generate an error.
Consecutive semi-colons will generate an error.
Non-numeric data between commas will generate an error.
Non-numeric data between semi-colons will generate an error.
To convert non-normalized data to a string format, use the MATRIX2STRING or the MATRIX2STRING_q function.
To convert normalized data to a string format, us the NMATRIX2STRING or the NMATRIX2STRING_q function.
To convert the string result to a table, us the table-valued function MATRIX .
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'.
DECLARE @A as varchar(max) = '1,1,1,1;1,2,4,8;1,3,9,27;1,4,16,64;1,5,25,125';
SELECT wct.TCROSSPROD(@A, NULL) as [A * A'];
This produces the following result.
{"columns":[{"field":"A * A'"}],"rows":[{"A * A'":"4,15,40,85,156;15,85,259,585,1111;40,259,820,1885,3616;85,585,1885,4369,8421;156,1111,3616,8421,16276"}]}
Using the same data we convert the matrix to a string using the MATRIX2STRING_q function and tabularize the results using the MATRIX function.
SELECT *
FROM wct.MATRIX(wct.TCROSSPROD(
wct.MATRIX2STRING_q('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
)
);
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 both an A and a B matrix and calculate A * B'. We will use the MATRIX2STRING_q function to format the input and the MATRIX function to format the output.
SELECT *
FROM wct.MATRIX(wct.TCROSSPROD(
wct.MATRIX2STRING_q('SELECT
POWER(r.n,0) as x0,
POWER(r.n,1) as x1,
POWER(r.n,2) as x2,
POWER(r.n,3) as x3
FROM (VALUES (1),(2),(3),(4),(5))r(n)'),
wct.MATRIX2STRING_q('SELECT
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
FROM (VALUES (1),(2),(3))r(n)')
)
);
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 will put the A and B matrices in a table and the use the NMATRIX2STRING_q function to create the input strings.
--Table to store Matrices
CREATE TABLE #Matrix
(
MatrixID char(1),
RowNum int,
ColNum int,
x float
);
--Put A into #Matrix
INSERT INTO #Matrix
SELECT 'A',
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);
--Put B into #Matrix
INSERT INTO #Matrix
SELECT 'B',
r.n,
c.n,
1 / POWER(cast(r.n as float), c.n)
FROM
(
VALUES
(1),
(2),
(3)
) r (n)
CROSS APPLY
(
VALUES
(0),
(1),
(2),
(3)
) c (n);
--A * B'
SELECT *
FROM wct.MATRIX(wct.TCROSSPROD(
wct.NMATRIX2STRING_q('SELECT RowNum,ColNum,x
FROM #Matrix WHERE MatrixID = ''A''')
,
wct.NMATRIX2STRING_q('SELECT RowNum,ColNum,x
FROM #Matrix WHERE MatrixID = ''B''')
)
);
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
TMCROSS - table-valued function to calculate A * A' or A * B'