Matrix Functions and Operators | Ocient Documentation

The Ocient Hyperscale Data Warehouse™ 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 CREATE TABLE SQL Statement Examples.

For details about working with matrix values, see Data Types.

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	qrdecomp(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 index 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.

[Array Functions and Operators](Array Functions and Operators)

Math Functions and Operators

[Tuple Functions and Operators](Tuple Functions and Operators)

Data Types

Query Ocient

Matrix Functions

Matrix Operators

Operator Examples

Bibliography

Related Links