CROSSPROD
Updated 2023-10-16 19:12:39.567000
Syntax
SELECT [westclintech].[wct].[CROSSPROD](
<@A, nvarchar(max),>
,<@B, nvarchar(max),>)
Description
Use the scalar function CROSSPROD 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. CROSSPROD produces the same result as MATMULT(TRANSPOSE(@A),@A) or MATMULT(TRANSPOSE(@A),@B) with less than half of the floating point operations.
CROSSPROD 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, use 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 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.
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.CROSSPROD(@A, NULL) as [A' * A];
This produces the following result.
{"columns":[{"field":"A' * A"}],"rows":[{"A' * A":"5,15,55,225;15,55,225,979;55,225,979,4425;225,979,4425,20515"}]}
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.CROSSPROD(
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":"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 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.CROSSPROD(
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),1) as x0,
1/POWER(cast(r.n as float),2) as x1,
1/POWER(cast(r.n as float),3) as x2
FROM (VALUES (1),(2),(3),(4),(5))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":"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 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),
(4),
(5)
) r (n)
CROSS APPLY
(
VALUES
(1),
(2),
(3)
) c (n);
--A' * B
SELECT *
FROM wct.MATRIX(wct.CROSSPROD(
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":"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
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.
TCROSSPROD - scalar function to calculate A * A' or A * B'
TMCROSS - table-valued function to calculate A * A' or A * B'