MTRACEN
Updated 2024-03-06 21:18:17.857000
Syntax
SELECT [westclintech].[wct].[MTRACEN](
<@Matrix_TableName, nvarchar(max),>
,<@Key1Columnname, nvarchar(4000),>
,<@Key2ColumnName, nvarchar(4000),>
,<@DataColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>)
Description
Use the scalar function 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:
\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 _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_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_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_GroupedColumnValue
the column value to do the grouping on.
@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_TableName
the name, as text, of the table or view that contains the matrix values to be used in the calculation.
Return Type
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.
{"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.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.
{"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,
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.
{"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"}]}