Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

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


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

 



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service