Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server matrix trace


MTRACEN

Updated: 30 September 2013


Use MTRACEN to calculate the trace of a matrix in 3rd normal form. The trace of a matrix (A) is the sum of the diagonal elements. For example:
 
 
Syntax
SELECT [wctMath].[wct].[MTRACEN](
  <@Matrix_TableName, nvarchar(max),>
 ,<@Key1Columnname, nvarchar(4000),>
 ,<@Key2ColumnName, nvarchar(4000),>
 ,<@DataColumnName, nvarchar(4000),>
 ,<@GroupedColumnName, nvarchar(4000),>
 ,<@GroupedColumnValue, sql_variant,>)
GO
Arguments
@Matrix_TableName
the name, as text, of the table or view that contains the matrix values to be used in the calculation.
@Matrix_Key1ColumnName
the name, as text, of the column in the table or view specified by @Matrix_TableName that contains the 'row number' value used in the array.
@Matrix _Key2ColumnName
the name, as text, of the column in the table or view specified by @Matrix _TableName that contains the 'column number' value used in the array.
@Matrix _DataColumnName
the name, as text, of the column in the table or view specified by @Matrix _TableName that contains the matrix values to be used in the product. Data returned from the @Matrix_DataColumnName must be of the type float or of a type that implicitly converts to float.
@Matrix_GroupedColumnName
the name, as text, of the column in the table or view specified by @Matrix_TableName which will be used for grouping the results.
@Matrix_GroupedColumnValue
the column value to do the grouping on.
 
Return Types
float
Remarks
·         Use the MTRACEN_q function for more complex queries.
·         Use MTRACE for a de-normalized table.
·         If the matrix is not square then an error will be returned.
·         If the matrix diagonal contains NULL, then NULL will be returned.
·         The function returns an error if the matrix diagonal contains a non-numeric value.
Examples
In this example, we calculate trace from a temporary table containing one matrix.
SET NOCOUNT ON
 
CREATE TABLE #c(
      rn          int,
      cn          int,
      z           float
      )
     
INSERT INTO #c VALUES (1,1,-78)
INSERT INTO #c VALUES (1,2,-35)
INSERT INTO #c VALUES (1,3,98)
INSERT INTO #c VALUES (1,4,-65)
INSERT INTO #c VALUES (1,5,49)
INSERT INTO #c VALUES (2,1,48)
INSERT INTO #c VALUES (2,2,-53)
INSERT INTO #c VALUES (2,3,-1)
INSERT INTO #c VALUES (2,4,-18)
INSERT INTO #c VALUES (2,5,-12)
INSERT INTO #c VALUES (3,1,35)
INSERT INTO #c VALUES (3,2,-70)
INSERT INTO #c VALUES (3,3,-77)
INSERT INTO #c VALUES (3,4,44)
INSERT INTO #c VALUES (3,5,73)
INSERT INTO #c VALUES (4,1,37)
INSERT INTO #c VALUES (4,2,-75)
INSERT INTO #c VALUES (4,3,-13)
INSERT INTO #c VALUES (4,4,-55)
INSERT INTO #c VALUES (4,5,97)
INSERT INTO #c VALUES (5,1,-56)
INSERT INTO #c VALUES (5,2,36)
INSERT INTO #c VALUES (5,3,84)
INSERT INTO #c VALUES (5,4,-34)
INSERT INTO #c VALUES (5,5,52)
     
SELECT wct.MTRACEN('#c','rn','cn','z','',NULL) as TRACE
 
DROP TABLE #c

This produces the following result.
                 TRACE
----------------------
                  -211

Using the same data, we can calculate the trace directly from the derived table.
SELECT wct.MTRACEN('(VALUES
      (1,1,-78),
      (1,2,-35),
      (1,3,98),
      (1,4,-65),
      (1,5,49),
      (2,1,48),
      (2,2,-53),
      (2,3,-1),
      (2,4,-18),
      (2,5,-12),
      (3,1,35),
      (3,2,-70),
      (3,3,-77),
      (3,4,44),
      (3,5,73),
      (4,1,37),
      (4,2,-75),
      (4,3,-13),
      (4,4,-55),
      (4,5,97),
      (5,1,-56),
      (5,2,36),
      (5,3,84),
      (5,4,-34),
      (5,5,52)
      )n(rn,cn,z)','rn','cn','z','',NULL) as TRACE

This produces the following result.
                 TRACE
----------------------
                  -211

Let's put several matrices into a table and calculate the trace for each.
SET NOCOUNT ON
 
CREATE TABLE #c(
      Matrix      int,
      rn          int,
      cn          int,
      z           float,
      PRIMARY KEY (Matrix, rn,cn)
      )
 
INSERT INTO #c VALUES (500,1,1,88)
INSERT INTO #c VALUES (500,1,2,32)
INSERT INTO #c VALUES (500,1,3,-19)
INSERT INTO #c VALUES (500,2,1,-14)
INSERT INTO #c VALUES (500,2,2,-7)
INSERT INTO #c VALUES (500,2,3,-87)
INSERT INTO #c VALUES (500,3,1,-99)
INSERT INTO #c VALUES (500,3,2,-43)
INSERT INTO #c VALUES (500,3,3,30)
INSERT INTO #c VALUES (501,1,1,-10)
INSERT INTO #c VALUES (501,1,2,-69)
INSERT INTO #c VALUES (501,1,3,25)
INSERT INTO #c VALUES (501,2,1,-94)
INSERT INTO #c VALUES (501,2,2,83)
INSERT INTO #c VALUES (501,2,3,-40)
INSERT INTO #c VALUES (501,3,1,0)
INSERT INTO #c VALUES (501,3,2,-1)
INSERT INTO #c VALUES (501,3,3,20)
INSERT INTO #c VALUES (502,1,1,-73)
INSERT INTO #c VALUES (502,1,2,-55)
INSERT INTO #c VALUES (502,1,3,1)
INSERT INTO #c VALUES (502,2,1,-84)
INSERT INTO #c VALUES (502,2,2,-41)
INSERT INTO #c VALUES (502,2,3,-92)
INSERT INTO #c VALUES (502,3,1,-70)
INSERT INTO #c VALUES (502,3,2,-69)
INSERT INTO #c VALUES (502,3,3,-70)
INSERT INTO #c VALUES (503,1,1,-41)
INSERT INTO #c VALUES (503,1,2,-90)
INSERT INTO #c VALUES (503,1,3,86)
INSERT INTO #c VALUES (503,2,1,-90)
INSERT INTO #c VALUES (503,2,2,88)
INSERT INTO #c VALUES (503,2,3,24)
INSERT INTO #c VALUES (503,3,1,52)
INSERT INTO #c VALUES (503,3,2,60)
INSERT INTO #c VALUES (503,3,3,-37)
 
SELECT Matrix
,wct.MTRACEN('#c','rn','cn','z','Matrix',n.Matrix) as TRACE
FROM (SELECT DISTINCT Matrix from #c)n
 
DROP TABLE #c

This produces the following result.
     Matrix                  TRACE
----------- ----------------------
        500                    111
        501                     93
        502                   -184
        503                     10
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service