MTRACE

Updated 2024-03-06 21:15:26.110000

Syntax

SELECT [westclintech].[wct].[MTRACE](
  <@Matrix_TableName, nvarchar(max),>
 ,<@ColumnNames, nvarchar(max),>
 ,<@GroupedColumnName, nvarchar(4000),>
 ,<@GroupedColumnValue, sql_variant,>)

Description

Use the scalar function MTRACE to calculate the trace of a de-normalized matrix. The trace of a matrix (A) is the sum of the diagonal elements. For example:

\textbf{A}=\begin{bmatrix}a_{11}&a_{12}&a_{13}\\a_{21}&a_{22}&a_{23}\\a_{31}&a_{32}&a_{33}\end{bmatrix}

\text{tr}\left(\textbf{A}\right)=a_{11}+a_{22}+a_{33}

Arguments

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

@Matrix_ ColumnNames

the name, as text, of the columns in the table or view specified by @Matrix_TableName that contains the matrix values to be used in the calculation. Data returned from the @Matrix_ColumnNames must be of the type float or of a type that implicitly converts to float.

@Matrix_ TableName

the name, as text, of the table or view that contains the matrix values. @Matrix_ TableName cannot reference a table variable or a common table expression.

Return Type

float

Remarks

Use the MTRACE_q function for more complex queries.

Use MTRACEN for a table in 3rd normal form.

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 the trace from a temporary table containing one matrix.

SET NOCOUNT ON;

SELECT *

INTO #c

FROM

(

    VALUES

        (-78, -35, 98, -65, 49),

        (48, -53, -1, -18, -12),

        (35, -70, -77, 44, 73),

        (37, -75, -13, -55, 97),

        (-56, 36, 84, -34, 52)

) n (x1, x2, x3, x4, x5);

SELECT wct.MTRACE('#c', 'x1,x2,x3,x4,x5', '', NULL) as TRACE;

DROP TABLE #c;

This produces the following result.

{"columns":[{"field":"TRACE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"TRACE":"-211"}]}

Using the same data, we can calculate the trace directly from the derived table.

SELECT wct.MTRACE(

                     '(VALUES

      (-78,-35,98,-65,49),

      (48,-53,-1,-18,-12),

      (35,-70,-77,44,73),

      (37,-75,-13,-55,97),

      (-56,36,84,-34,52)

      )n(x1,x2,x3,x4,x5)',

                     'x1,x2,x3,x4,x5',

                     '',

                     NULL

                 ) as TRACE;

This produces the following result.

{"columns":[{"field":"TRACE","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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,

    x1 float,

    x2 float,

    x3 float,

    PRIMARY KEY (

                    Matrix,

                    rn

                )

);

INSERT INTO #c

VALUES

(500, 1, 88, 32, -19);

INSERT INTO #c

VALUES

(500, 2, -14, -7, -87);

INSERT INTO #c

VALUES

(500, 3, -99, -43, 30);

INSERT INTO #c

VALUES

(501, 1, -10, -69, 25);

INSERT INTO #c

VALUES

(501, 2, -94, 83, -40);

INSERT INTO #c

VALUES

(501, 3, 0, -1, 20);

INSERT INTO #c

VALUES

(502, 1, -73, -55, 1);

INSERT INTO #c

VALUES

(502, 2, -84, -41, -92);

INSERT INTO #c

VALUES

(502, 3, -70, -69, -70);

INSERT INTO #c

VALUES

(503, 1, -41, -90, 86);

INSERT INTO #c

VALUES

(503, 2, -90, 88, 24);

INSERT INTO #c

VALUES

(503, 3, 52, 60, -37);

SELECT Matrix,

       wct.MTRACE('#c', 'x1,x2,x3', 'Matrix', n.Matrix) as Trace

FROM

(SELECT DISTINCT Matrix FROM #c) n;

DROP TABLE #c;

This produces the following result.

{"columns":[{"field":"Matrix","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"Trace","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"Matrix":"500","Trace":"111"},{"Matrix":"501","Trace":"93"},{"Matrix":"502","Trace":"-184"},{"Matrix":"503","Trace":"10"}]}