SQL Reference

Matrix Functions and Operators

Matrix Functions

Matrixes store values in DOUBLE format.

_R{} and _C{} are vector notation. For example, _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 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 sum of elements in a 1D matrix/vector

vector_sum( _R{ 1,2,3 } )

vector_sum( _C{ 1,2,3 } )

6.0

vector min

vector_min(matrix)

Returns minimum of elements in a 1D matrix/vector

vector_min( _R{ 1,2,3 } )

vector_min( _C{ 1,2,3 } )

1.0

vector max

vector_max(matrix)

Returns maximum of elements in a 1D 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 1D 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 1D matrix or vector. The index starts at 0.

vector_argmax(_C{1, -1, 5, 0})

2

softmax

softmax(matrix)

Returns the softmax of a 1D 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 matrices

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)

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 given row and column. 1-Indexed. Accessing an 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

Related Links

Array Functions and Operators

Math Functions and Operators

Tuple Functions and Operators

Data Types

Query Ocient

Updated 10 May 2024
Did this page help you?
PREVIOUS
Math Functions and Operators
NEXT
Network Type Functions
Docs powered by Archbee