Matrix Functions and Operators

the {{ocienthyperscaledatawarehouse}} 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 primary key, 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 0rmcbcyysu 0ej2rmrcqy for details about working with matrix values, see docid\ ogtviwl gtbgv0chhrh 3 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 argmin of a one dimensional matrix or vector the index starts at 0 vector argmin( r{1, 1, 5, 0}) 1 vector argmax vector argmax(matrix) returns the argmax of a one dimensional matrix or vector the index starts at 0 vector argmax( c{1, 1, 5, 0}) 2 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 (1) |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 (1) golub, gene h , and charles f van loan matrix computations 3rd ed, johns hopkins university press, 1996 related links docid 89kk83 gif3icefcy1kuw docid\ e2c2vckzh8dfsbp1wngpr docid\ xunz45zvbfsvnbhzy99v5 docid\ ogtviwl gtbgv0chhrh 3 docid 4ycq1d8tkfmlsacorynf6