SQL Reference

Matrix Functions and Operators

Matrix Functions

_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,2,3} }

Constructor (2)

matrix_from_text(string)

Creates a matrix from the given string

matrix_from_text('{ {1,2,3}, {1,2,3} }')

{ {1,2,3}, {1,2,3} }

+ (matrix addition)

matrix + matrix

Performs matrix addition

{ {1,2,3}, {4,5,6} } + { {4,5,6}, {1,2,3} }

{ {5,7,9}, {5,7,9} }

- (matrix subtraction)

matrix - matrix

Performs matrix subtraction

{ {1,2,3}, {4,5,6} } - { {4,5,6}, {1,2,3} }

{ {-3,-3,-3}, {3,3,3} }

* (scalar multiplication)

numeric * matrix

Performs scalar multiplication

2 * { {4,5,6}, {1,2,3} }

{ {2,4,6}, {8,10,12} }

* (matrix multiplication)

matrix * matrix

Performs matrix multiplication

{ {1,2,3}, {4,5,6} } * { {1,2}, {3,4}, {5,6} }

{ {22,28}, {49,64} }

/ (scalar division)

matrix / numeric

Performs scalar division

{ {1,2,3}, {4,5,6} } / 2

{ {0.5,1,1.5}, {2,2.5,3} }

transpose

transpose(matrix)

Returns transpose of the matrix

transpose( { {1,2,3}, {4,5,6} } )

{ {1,4}, {2,5}, {3,6} }

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, 1}, {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.

vector_argmin(_R{1, -1, 5, 0})

1

vector argmax

vector_argmax(matrix)

Returns the argmax of a 1D matrix or vector.

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 1-dimensional matrices 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.69314...

log loss

log_loss(matrix, matrix)

Returns the log loss of two 1-dimensional matrices 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.52573…

logits loss

logits_loss(matrix, matrix)

Returns the logits loss of two 1-dimensional matrices 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.44332…​

hinge loss

hinge_loss(matrix, matrix)

Returns the hinge loss of two 1-dimensional matrices 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 1D matrices/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 1D matrix/vector

abs( _R{ 1,2,3 } )

abs( _C{ 1,2,3 } )

sqrt(14)

LUPQ decomp

LUPQ_decomp(matrix)

Returns LUPQ decomposition of a square matrix A as a tuple of 4 matrices where PAQ = LU

LUP decomp

LUP_decomp(matrix)

Returns LUP decomposition of a square matrix as a tuple of 3 matrices

QR decomp

QR_decomp(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 matrices

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 given dimension

identity_matrix(3)

{ {1,0,0}, {0,1,0}, {0,0,1} }

null matrix

null_matrix(unsigned int, unsigned int)

Returns a null matrix of the given (row, col) dimensions

null_matrix(2, 3)

NULL

zero matrix

zero_matrix(unsigned int, unsigned int)

Returns a zero matrix of the given (row, col) dimensions

zero_matrix(2, 3)

{ {0,0,0}, {0,0,0} }

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 given row range and column range.

  • 1-Indexed
  • 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.
  • 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 indices will throw 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,3}, {5,6} }



{ {1,2,3}, {4,5,6} }[1:NULL,2:3]

NULL



Updated 21 Nov 2023
Did this page help you?
PREVIOUS
Math Functions
NEXT
Scalar Data Conversion Functions
Docs powered by Archbee