CROSSPROD
Updated: 10 April 2015
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.
Syntax
SELECT [wct].[CROSSPROD](
<@A, nvarchar(max),>
,<@B, nvarchar(max),>)
Arguments
@A
a string representation of the A matrix.
@B
a string representation of the B matrix.
Return Types
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 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.
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.
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.
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.
See Also