Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server function to calculate A * A' or A * B'


TCROSSPROD

Updated: 10 April 2015


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 that 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 [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 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 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

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service