Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

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


MCROSS

Updated: 10 April 2015


Use the table-valued function MCROSS 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.
Syntax
SELECT * FROM [wct].[MCROSS](
  <@A, nvarchar(max),>
 ,<@B, nvarchar(max),>
 ,<@Is3N, bit,>)

Arguments
@A
                the SELECT statement which returns the resultant table containing the A matrix.
@B
                the SELECT statement which returns the resultant table containing the B matrix.
@Is3N
a bit value identifying the form for the resultant table returned by @A and @B. Enter 'True' for a resultant table in 3rd normal form. Enter 'False' for a de-normalized table in 'spreadsheet' form.

Return Types
TABLE (
       [RowNum] [int] NULL,
       [ColNum] [int] NULL,
       [ItemValue] [float] NULL
)

Remarks
·         If @Is3N is NULL then @Is3N = 'False'.
·         If @Is3N is'True' then the result table should be returned as row, column, and value.
·         If the resultant table contains NULL, then NULL will be returned.
·         The function returns an error if the array contains a non-numeric value.
·         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 using a matrix in spreadsheet format.
SELECT
   *
FROM
   wct.MCROSS('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
       ,'False'
   )
This produces the following result.

 

In this example, we supply the same matrix, except that it is now in 3rd normal form.
SELECT
   *
FROM
   wct.MCROSS('
   SELECT
       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)
   ORDER BY 1,2'
   ,NULL
   ,'True'
   )
This produces the following results.

 

In this example we supply both an A and a B matrix in spreadsheet form and calculate A' * B. We will do this by creating 2 temporary tables for the A and the B matrix and then selecting from the tables in the MCROSS function.
--Create matrix A
SELECT
   r.n as rn,
   POWER(r.n,0) as x0,
   POWER(r.n,1) as x1,
   POWER(r.n,2) as x2,
   POWER(r.n,3) as x3
INTO
   #A
FROM (VALUES (1),(2),(3),(4),(5))r(n)
 
--Create matrix B
SELECT
   r.n as rn,
   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
INTO
   #B
FROM (VALUES (1),(2),(3),(4),(5))r(n)
 
--A' * B
SELECT
   *
FROM
   wct.MCROSS(
       'SELECT x0,x1,x2,x3 FROM #A order by rn',
       'SELECT x0,x1,x2 FROM #B order by rn',
       'False'
       )
This produces the following result.

 

Performing the same calculation as in the previous example, except that this time the data in matrices A and B are in 3rd normal form.
--Create the A Matrix
SELECT
   r.n as rowno,
   c.n as colno,
   POWER(r.n,c.n) as x
INTO
   #A
FROM (VALUES
   (1),(2),(3),(4),(5))r(n)
CROSS APPLY(VALUES
   (0),(1),(2),(3))c(n)
 
--Create the B Matrix
SELECT
   r.n as rowno,
   c.n as colno,
   1/POWER(cast(r.n as float),c.n) as x
INTO
   #B
FROM (VALUES
   (1),(2),(3),(4),(5))r(n)
CROSS APPLY(VALUES
   (1),(2),(3))c(n)
 
--A' * B
SELECT
   *
FROM
   wct.MCROSS(
       'SELECT * FROM #A order by 1,2',
       'SELECT * FROM #B order by 1,2',
       'True'
       )
This produces the following result.

 

See Also

 



Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service