Matrix Functions and Operators

the supports sql based tools for working with multi dimensional data, including functions to construct, transform, and analyze matrixes and vectors matrix functions include arithmetic operations, linear algebra (e g , transpose, inverse, eigenvalues), and statistical tools matrixes store their values in double format to define a matrix data type in sql, use the syntax matrix\[rows]\[columns] , where the bracketed numbers specify the number of matrix rows and columns example this example creates a basic table matrix example with a matrix column col matrix that contains matrixes with two rows and three columns create table matrix example ( id int, col matrix matrix\[2]\[3] not null ); insert data for two matrixes into the matrix column insert into matrix example (id, col matrix) values (1, {{1, 2, 3}, {4, 5, 6}}), (2, {{0, 0, 0}, {7, 8, 9}}); for more ddl examples that use matrixes, see docid\ veeoxedu opmz9xm0qqac for details about working with matrix values, see docid\ czxgepf3prq9ufwhwbiuy matrix functions in the examples, r{} and c{} are vector notation, meaning r{ 1,2,3 } is a row vector with values 1 , 2 , and 3 function syntax purpose example result constructor make matrix ixj(i, j, e 00, …​, e ij) creates an ixj matrix with elements e 00 , … ​, e ij make matrix 1x3(1,3,1,2,3) { {1 0,2 0,3 0} } constructor (2) matrix from text(string) creates a matrix from the specified string matrix from text('{ {1,2,3}, {1,2,3} }') { {1 0 ,2 0 ,3 0 }, {1 0 ,2 0 ,3 0 } } + (matrix addition) matrix + matrix performs matrix addition { {1,2,3}, {4,5,6} } + { {4,5,6}, {1,2,3} } { {5 0 ,7 0 ,9 0 }, {5 0 ,7 0 ,9 0 } } (matrix subtraction) matrix matrix performs matrix subtraction { {1,2,3}, {4,5,6} } { {4,5,6}, {1,2,3} } { { 3 0 , 3 0 , 3 0 }, {3 0 ,3 0 ,3 0 } } (scalar multiplication) numeric matrix performs scalar multiplication 2 { {4,5,6}, {1,2,3} } { {2 0 ,4 0 ,6 0 }, {8 0 ,10 0 ,12 0 } } (matrix multiplication) matrix matrix performs matrix multiplication { {1,2,3}, {4,5,6} } { {1,2}, {3,4}, {5,6} } { {22 0 ,28 0 }, {49 0 ,64 0 } } / (scalar division) matrix / numeric performs scalar division { {1,2,3}, {4,5,6} } / 2 { {0 5,1 0 ,1 5}, {2 0 ,2 5,3 0 } } transpose transpose(matrix) returns transpose of the matrix transpose( { {1,2,3}, {4,5,6} } ) { {1 0 ,4 0 }, {2 0 ,5 0 }, {3 0 ,6 0 } } determinant det(matrix) returns the determinant of the matrix as a double det( { {1,2}, {3,4} } ) 2 0 inverse inverse(matrix) returns inverse of a square, invertible matrix inverse( { {1,2}, {3,4} } ) { { 2 0 , 1 0 }, {1 5, 0 5} } trace matrix trace(matrix) returns trace of a square matrix as a double matrix trace( { {1,2,3}, {4,5,6}, {7,8,9} } ) 15 0 vector sum vector sum(matrix) returns the sum of elements in a one dimensional matrix/vector vector sum( r{ 1,2,3 } ) vector sum( c{ 1,2,3 } ) 6 0 vector min vector min(matrix) returns the minimum of elements in a one dimensional matrix/vector vector min( r{ 1,2,3 } ) vector min( c{ 1,2,3 } ) 1 0 vector max vector max(matrix) returns the maximum of elements in a one dimensional matrix/vector vector max( r{ 1,2,3 } ) vector max( c{ 1,2,3 } ) 3 0 vector argmin vector argmin(matrix) returns the 1 based position (index) of the minimum element in a one dimensional matrix or vector (row or column vector) the first element has index 1 vector argmin( r{1, 1, 5, 0}) 2 vector argmax vector argmax(matrix) returns the 1 based position (index) of the maximum element of a one dimensional matrix or vector the first element has index 1 vector argmax( c{1, 1, 5, 0}) 3 softmax softmax(matrix) returns the softmax of a one dimensional matrix or vector softmax( r{1, 1}) {{0 5, 0 5}} cross entropy loss cross entropy loss(matrix, matrix) returns the cross entropy loss of two one dimensional matrixes or vectors cross entropy loss( r{ 0 2, 0 5, 0 8 }, c{ 0,1,0 } ) cross entropy loss( c{ 0 2, 0 5, 0 8}, r{ 0,1,0 } ) cross entropy loss( r{ 0 2, 0 5, 0 8 }, r{ 0,1,0 } ) cross entropy loss( c{ 0 2, 0 5, 0 8 }, c{ 0,1,0} ) 0 69 log loss log loss(matrix, matrix) returns the log loss of two one dimensional matrixes or vectors log loss( r{ 0 2, 0 5, 0 8 }, c{ 0,1,0 } ) log loss( c{ 0 2, 0 5, 0 8}, r{ 0,1,0 } ) log loss( r{ 0 2, 0 5, 0 8 }, r{ 0,1,0 } ) log loss( c{ 0 2, 0 5, 0 8 }, c{ 0,1,0} ) 2 53 logits loss logits loss(matrix, matrix) returns the logits loss of two one dimensional matrixes or vectors logits loss( r{ 0 2, 0 5, 0 8 }, c{ 0,1,0 } ) logits loss( c{ 0 2, 0 5, 0 8}, r{ 0,1,0 } ) logits loss( r{ 0 2, 0 5, 0 8 }, r{ 0,1,0 } ) logits loss( c{ 0 2, 0 5, 0 8 }, c{ 0,1,0} ) 2 44 hinge loss hinge loss(matrix, matrix) returns the hinge loss of two one dimensional matrixes or vectors hinge loss( r{ 0 2, 0 5, 0 8 }, c{ 0,1,0 } ) hinge loss( c{ 0 2, 0 5, 0 8}, r{ 0,1,0 } ) hinge loss( r{ 0 2, 0 5, 0 8 }, r{ 0,1,0 } ) hinge loss( c{ 0 2, 0 5, 0 8 }, c{ 0,1,0} ) 3 5 dot product dot(matrix, matrix) returns dot product of two one dimensional matrixes/vectors dot( r{ 1,2,3 }, c{ 1,2,3 } ) dot( c{ 1,2,3 }, r{ 1,2,3 } ) dot( r{ 1,2,3 }, r{ 1,2,3 } ) dot( c{ 1,2,3 }, c{ 1,2,3 } ) 14 0 magnitude abs(matrix) returns magnitude of one dimensional matrix/vector abs( r{ 1,2,3 } ) abs( c{ 1,2,3 } ) 3 74 lupq decomp lupq decomp(matrix) returns lupq decomposition of a square matrix a as a tuple of 4 matrixes where paq = lu lup decomp lup decomp(matrix) returns lup decomposition of a square matrix as a tuple of 3 matrixes qr decomp qr decomp(matrix) returns qr decomposition of a matrix as a tuple of 2 matrixes svd decomp svd decomp(matrix) returns svd decomposition of a matrix as a tuple of 3 matrixes eigenvalues/vectors eigen(matrix) returns eigenvalues and eigenvalues of a square matrix as a vector of pairs identity matrix identity matrix(unsigned int) returns an identity matrix of the specified dimension identity matrix(3) { {1 0,0 0,0 0}, {0 0,1 0,0 0}, {0 0,0 0,1 0} } null matrix null matrix(unsigned int, unsigned int) returns a null matrix of the specified (row, col) dimensions null matrix(2, 3) null zero matrix zero matrix(unsigned int, unsigned int) returns a zero matrix of the specified (row, col) dimensions zero matrix(2, 3) { {0 0 ,0 0 ,0 0 }, {0 0 ,0 0 ,0 0 } } matrix dimension matrix dim(matrix) returns the dimensions of the specified matrix as a tuple of (row, col) integers when the input matrix is null, the function returns null matrix dim( r{1, 2, 3}) tuple\<int,int>(1, 3) frobenius norm frobenius(matrix) returns the frobenius norm of a matrix this norm is the matrix norm of an m x n matrix defined as the square root of the sum of the absolute squares of its elements |a|^2 is the same as a^2, where a is a real number frobenius(matrix from text('{{3,4},{8,6}}')) 11 180339887498949 matrix operators operator syntax purpose < matrix < matrix less than comparison > matrix > matrix greater than comparison <= matrix <= matrix less than or equal to comparison >= matrix >= matrix greater than or equal to comparison = matrix = matrix equal to comparison <> matrix <> matrix not equal to comparison \[,] matrix\[rowindex,colindex] access matrix at the specified row and column 1 indexed accessing a null matrix or using a null index returns null access out of bounds returns a null \[ , ] matrix\[r1\ c1,r2\ c2] slice matrix at the specified row range and column range the i ndex starts at 1 slicing a null matrix or using a null index returns null start indices omitted or below 1 become 1 end indices omitted or above the number of rows/columns become the number of rows/columns example — matrix 10x10\[ 10, 1 20] is equivalent to matrix\[1 10,1 10] ranges completely out of bounds throw an invalid argument error sequential slices like matrix\[1 1,1 1]\[1 1,1 1] throw an error slices cannot be combined with access matrix\[1,1 10] is not allowed, but matrix 10x10\[1 1,1 10] is trying to slice with non constant indexes throws an error operator examples operator sql predicate result < { {1,2,3}, {4,5,6} } < { {4,5,6}, {1,2,3} } true { {1,2,3}, {4,5,6} } < { {1,2,2}, {1,2,3} } false { {1,2,3}, {4,5,6} } < { {1,2,3}, {4,5,6} } false > { {4,5,6}, {4,5,6} } > { {1,2,3}, {7,8,9} } true { {1,2,3}, {4,5,6} } > { {4,5,6}, {1,2,3} } false { {1,2,3}, {4,5,6} } > { {1,2,3}, {4,5,6} } false <= { {1,2,3}, {4,5,6} } <= { {4,5,6}, {1,2,3} } true { {1,2,3}, {4,5,6} } <= { {1,2,2}, {1,2,3} } false { {1,2,3}, {4,5,6} } <= { {1,2,3}, {4,5,6} } true >= { {4,5,6}, {4,5,6} } >= { {1,2,3}, {7,8,9} } true { {1,2,3}, {4,5,6} } >= { {1,5,6}, {1,2,3} } false { {1,2,3}, {4,5,6} } >= { {1,2,3}, {4,5,6} } true = { {1,2,3}, {4,5,6} } = { {1,2,3}, {4,5,6} } true { {1,2,3}, {4,5,6} } = { {1,2,4}, {4,5,6} } false <> { {1,2,3}, {4,5,6} } <> { {1,2,3}, {4,5,6} } false { {1,2,3}, {4,5,6} } <> { {1,2,4}, {4,5,6} } true matrix access operator { {1,2,3}, {4,5,6} }\[1,2] 2 0 { {1,2,3}, {4,5,6} }\[null,2] null matrix slice operator { {1,2,3}, {4,5,6} }\[1 ,2 3] { {2 0 ,3 0 }, {5 0 ,6 0 } } { {1,2,3}, {4,5,6} }\[1\ null,2 3] null bibliography golub, gene h , and charles f van loan matrix computations 3rd ed, johns hopkins university press, 1996 related links docid\ xuk0z8dmxpgmogszpdw6w docid\ saa2re dvaqcs gtjp3ps docid\ mrrm7nwt xwcg zvmy6 p docid\ czxgepf3prq9ufwhwbiuy docid\ s0dywbqubbanzcnc4z9fx