SQL Server TCROSSPROD Function

Updated 2023-10-17 15:56:25.777000

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.

Syntax

SELECT [westclintech].[wct].[TCROSSPROD](
  <@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, 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.

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.

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.

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.

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'

TRANSPOSE - scalar function to convert A to A'