SQL Server CROSSPROD Function

Updated 2023-10-16 19:12:39.567000

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.

Syntax

SELECT [westclintech].[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 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.

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

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'

TRANSPOSE - scalar function to convert A to A'