SQL Server SVdecomp Function

Updated 2023-10-17 15:39:32.457000

Description

Use the table-valued function SVdecomp to calculate the economy-sized singular value decomposition of an m-x-n matrix A*.* SVdecomp returns an m-x-n orthogonal matrix U, an n-x-n orthogonal matrix V and an n-x-n diagonal matrix W such that,

    A = UWV^T

Syntax

SELECT * FROM [westclintech].[wct].[SVdecomp](
  <@Matrix_RangeQuery, nvarchar(max),>
 ,<@Is3N, bit,>)

Arguments

@Matrix_RangeQuery

the SELECT statement, as text, used to return the input matrix for this function. The SELECT statement specifies the column names from the table or view or can be used to enter the matrix values directly. Data returned from the @Matrix_RangeQuery select must be of the type float or of a type that implicitly converts to float.

@Is3N

a bit value identifying the form for the resultant table returned by @Matrix_RangeQuery. Enter 'True' for a resultant table in 3^rd normal form. Enter 'False' for a de-normalized table in 'spreadsheet' form.

Return Type

table

Remarks

If @Is3N is NULL then @Is3N = 'False'.

Use SVD for a matrix stored as a string.

[Type] is either 'U', 'V' or 'W'.

The function returns an error if the matrix contains a non-numeric value.

Examples

In this example @Matrix_RangeQuery returns the matrix from a derived table embodied in the statement. The matrix is in spreadsheet form.

SELECT *
FROM wct.SVDECOMP(
                     'SELECT
       *
   FROM (
       VALUES
           (1,2,3,4)
          ,(2,3,4,5)
          ,(3,4,5,6)
          ,(4,5,6,7)
       )n(x1,x2,x3,x4)',
                     'False'
                 );

This produces the following result.

Note that the results are returned in third-normal form. If we wanted to a more traditional (de-normalized) presentation of the results, we can us the PIVOT function. Also note that we can use * to select all the columns.

SELECT Type,
       [0],
       [1],
       [2],
       [3]
FROM
(
    SELECT *
    FROM wct.SVDECOMP(
                         'SELECT
          *
       FROM (
          VALUES
               (1,2,3,4)
              ,(2,3,4,5)
              ,(3,4,5,6)
              ,(4,5,6,7)
          )n(x1,x2,x3,x4)',
                         'False'
                     )
) d
PIVOT
(
    sum(Value)
    for ColNum in ([0], [1], [2], [3])
) as P;

This produces the following result.

In this example we show how to select data from a table. This will return the same results as our first example.

--store data in #a
SELECT *
INTO #A
FROM
(
    VALUES
        (1, 2, 3, 4),
        (2, 3, 4, 5),
        (3, 4, 5, 6),
        (4, 5, 6, 7)
) n (x1, x2, x3, x4);
--Select the data from #a
SELECT *
FROM wct.SVdecomp('SELECT * FROM #A', 'False');

For data in 3^rd normal form, the function expects 3 columns in the resultant table; row number, column number, and the value.

--put data into #B
SELECT *
INTO #B
FROM
(
    VALUES
        (0, 0, 1),
        (0, 1, 2),
        (0, 2, 3),
        (0, 3, 4),
        (1, 0, 2),
        (1, 1, 3),
        (1, 2, 4),
        (1, 3, 5),
        (2, 0, 3),
        (2, 1, 4),
        (2, 2, 5),
        (2, 3, 6),
        (3, 0, 4),
        (3, 1, 5),
        (3, 2, 6),
        (3, 3, 7)
) n (rownum, colnum, ItemValue);
--Select the data from #B
SELECT *
FROM wct.SVdecomp('SELECT * FROM #B', 'True');

In this example, we demonstrate how to reconstruct that input matrix using the calculation UWV'.

SELECT k.*
FROM
(
    SELECT Type as MatrixType,
           wct.NMATRIX2STRING(RowNum, ColNum, Value) as Matrix
    FROM wct.SVdecomp('SELECT * FROM #B', 'True')
    GROUP BY Type
) p
PIVOT
(
    MAX(Matrix)
    FOR MatrixType IN (U, W, V)
) d
    CROSS APPLY wct.MATRIX(wct.MATMULT(wct.MATMULT(U, W), wct.TRANSPOSE(V))) K;

This produces the following result.

See Also

SVD - Economy-sized singular value decomposition using a formatted matrix as input

QRDECOMP - Decompose a de-normalized N x N matrix A into the product of an upper triangular matrix R and an orthogonal matrix Q, such that A = QR.

LUDECOMP - Calculate the LU factorization of an N x N matrix using partial pivoting.