SQL functions for linear algebra. More...

Functions
float8	norm1 (float8[] x)
	1-norm of a vector More...

float8	norm2 (float8[] x)
	2-norm of a vector More...

float8	dist_inf_norm (float8[] x, float8[] y)
	Infinity-norm of the difference between two vectors. More...

float8	dist_pnorm (float8[] x, float8[] y, float8 p)
	p-norm of the difference between two vectors More...

float8	dist_norm1 (float8[] x, float8[] y)
	1-norm of the difference between two vectors More...

float8	dist_norm2 (float8[] x, float8[] y)
	2-norm of the difference between two vectors More...

float8	cosine_similarity (float8[] x, float8[] y)
	cosine similarity score between two vectors More...

float8	squared_dist_norm2 (float8[] x, float8[] y)
	Squared 2-norm of the difference between two vectors. More...

float8	dist_angle (float8[] x, float8[] y)
	Angle between two vectors. More...

float8	dist_tanimoto (float8[] x, float8[] y)
	Tanimoto distance between two vectors. More...

float8	dist_jaccard (text[] x, text[] y)
	Jaccard distance between two vectors (treated as sets) More...

closest_column_result	_closest_column (float8[] m, float8[] x, regproc dist, text dist_dn)

closest_column_result	closest_column (float8[] m, float8[] x, regproc dist="squared_dist_norm2")
	Given matrix \( M \) and vector \( \vec x \) compute the column of \( M \) that is closest to \( \vec x \). More...

closest_column_result	closest_column (float8[] m, float8[] x)

closest_columns_result	_closest_columns (float8[] m, float8[] x, integer num, regproc dist, text dist_dn)
	Given matrix \( M \) and vector \( \vec x \) compute the columns of \( M \) that are closest to \( \vec x \). More...

closest_columns_result	closest_columns (float8[] m, float8[] x, integer num, regproc dist)

closest_columns_result	closest_columns (float8[] m, float8[] x, integer num)

float8[]	avg_vector_transition (float8[] state, float8[] x)

float8[]	avg_vector_merge (float8[] state_left, float8[] state_right)

float8[]	avg_vector_final (float8[] state)

aggregate float8[]	avg (float8[] x)
	Compute the average of vectors. More...

float8[]	normalized_avg_vector_transition (float8[] state, float8[] x)

float8[]	normalized_avg_vector_final (float8[] state)

aggregate float8[]	normalized_avg (float8[] x)
	Compute the normalized average of vectors. More...

float8[]	matrix_agg_transition (float8[] state, float8[] x)

float8[]	matrix_agg_final (float8[] state)

aggregate float8[]	matrix_agg (float8[] x)
	Combine vectors to a matrix. More...

float8[]	matrix_column (float8[] matrix, integer col)
	Return the column of a matrix. More...

set< record >	deconstruct_2d_array (float8[] in_array)
	Construct a M-column N-row table of 2-D array. More...

set< record >	__deconstruct_lower_triangle (float8[] in_array)
	Construct a M-column M-row table of using lower triangle of 2-D array. More...

float8[]	array_to_1d (float8[] in_array)
	Return the input array as 1-D. More...

float8[]	array_to_2d (float8[] in_array)
	Return A 2-D matrix that the number of rows is encoded as the first element of the input array and the number of cols second. More...

float8[]	index_2d_array (float8[] in_2d_array, integer index)
	Return A 1-D form of the specified row of the given 2-D array. More...

float8[]	get_row (float8[] in_2d_array, integer index)
	Get an indexed row of the given matrix (2-D array) More...

float8[]	get_col (float8[] in_2d_array, integer index)
	Get an indexed col of the given matrix (2-D array) More...

Detailed Description

See also: For an overview of linear-algebra functions, see the module description Norms and Distance functions.

Function Documentation

set<record> __deconstruct_lower_triangle ( float8[] in_array )

Parameters

in_array 2-D array

closest_column_result _closest_column	(	float8[]	m,
		float8[]	x,
		regproc	dist,
		text	dist_dn
	)

closest_columns_result _closest_columns	(	float8[]	m,
		float8[]	x,
		integer	num,
		regproc	dist,
		text	dist_dn
	)

This function does essentially the same as closest_column(), except that it allows to specify the number of closest columns to return. The return value is a composite value:

columns_ids INTEGER[] - The 0-based indices of the num columns of \( M \) that are closest to \( x \). In case of ties, the first such indices are returned.
distances DOUBLE PRECISION[] - The distances between the columns of \( M \) with indices in columns_ids and \( x \). That is, distances[i] contains \( \operatorname{dist}(\vec{m_j}, \vec x) \), where \( j = \) columns_ids[i].

float8 [] array_to_1d ( float8[] in_array )

Parameters

in_array 1-D or 2-D array

float8 [] array_to_2d ( float8[] in_array )

Parameters

in_array Input array with first 2 elements describing dimensions

aggregate float8 [] avg ( float8[] x )

Given vectors \( x_1, \dots, x_n \), compute the average \( \frac 1n \sum_{i=1}^n x_i \).

Parameters

x	Point \( x_i \)

Returns: Average \( \frac 1n \sum_{i=1}^n x_i \)

float8 [] avg_vector_final ( float8[] state )

float8 [] avg_vector_merge	(	float8[]	state_left,
		float8[]	state_right
	)

float8 [] avg_vector_transition	(	float8[]	state,
		float8[]	x
	)

closest_column_result closest_column	(	float8[]	m,
		float8[]	x,
		regproc	dist = `"squared_dist_norm2"`
	)

Parameters

M	Matrix \( M = (\vec{m_0} \dots \vec{m_{l-1}}) \in \mathbb{R}^{k \times l} \)
x	Vector \( \vec x \in \mathbb R^k \)
dist	The metric \( \operatorname{dist} \). This needs to be a function with signature `DOUBLE PRECISION[] x DOUBLE PRECISION[] -> DOUBLE PRECISION`.

Returns

A composite value:

columns_id INTEGER - The 0-based index of the column of \( M \) that is closest to \( x \). In case of ties, the first such index is returned. That is, columns_id is the minimum element in the set \( \arg\min_{i=0,\dots,l-1} \operatorname{dist}(\vec{m_i}, \vec x) \).
distance DOUBLE PRECISION - The minimum distance between any column of \( M \) and \( x \). That is, \( \min_{i=0,\dots,l-1} \operatorname{dist}(\vec{m_i}, \vec x) \).

closest_column_result closest_column	(	float8[]	m,
		float8[]	x
	)

closest_columns_result closest_columns	(	float8[]	m,
		float8[]	x,
		integer	num,
		regproc	dist
	)

closest_columns_result closest_columns	(	float8[]	m,
		float8[]	x,
		integer	num
	)

float8 cosine_similarity	(	float8[]	x,
		float8[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( \frac{\langle \vec x, \vec y \rangle} {\| \vec x \| \cdot \| \vec y \|} \)

set<record> deconstruct_2d_array ( float8[] in_array )

Parameters

in_array 2-D array

float8 dist_angle	(	float8[]	x,
		float8[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( \arccos\left(\frac{\langle \vec x, \vec y \rangle} {\| \vec x \| \cdot \| \vec y \|}\right) \)

float8 dist_inf_norm	(	float8[]	x,
		float8[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( \| x - y \|_\infty = \max_{i=1}^n \|x_i - y_i\| \)

float8 dist_jaccard	(	text[]	x,
		text[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_m) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( 1 - \frac{|x \cap y|}{|x \cup y|} \)

float8 dist_norm1	(	float8[]	x,
		float8[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( \| x - y \|_1 = \sum_{i=1}^n |x_i - y_i| \)

float8 dist_norm2	(	float8[]	x,
		float8[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( \| x - y \|_2 = \sqrt{\sum_{i=1}^n (x_i - y_i)^2} \)

float8 dist_pnorm	(	float8[]	x,
		float8[]	y,
		float8	p
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)
p	Scalar \( p > 0 \)

Returns: \( \| x - y \|_p = (\sum_{i=1}^n \|x_i - y_i\|^p)^{\frac{1}{p}} \)

float8 dist_tanimoto	(	float8[]	x,
		float8[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( 1 - \frac{\langle \vec x, \vec y \rangle} {\| \vec x \|^2 \cdot \| \vec y \|^2 - \langle \vec x, \vec y \rangle} \)

float8 [] get_col	(	float8[]	in_2d_array,
		integer	index
	)

Parameters

in_2d_array	Input 2-D array
index

float8 [] get_row	(	float8[]	in_2d_array,
		integer	index
	)

Parameters

in_2d_array	Input 2-D array
index

float8 [] index_2d_array	(	float8[]	in_2d_array,
		integer	index
	)

Parameters

in_2d_array	Input 2-D array
index

aggregate float8 [] matrix_agg ( float8[] x )

Given vectors \( \vec x_1, \dots, \vec x_n \in \mathbb R^m \), return matrix \( ( \vec x_1 \dots \vec x_n ) \in \mathbb R^{m \times n}\).

Parameters

x	Vector \( x_i \)

Returns: Matrix with columns \( x_1, \dots, x_n \)

float8 [] matrix_agg_final ( float8[] state )

float8 [] matrix_agg_transition	(	float8[]	state,
		float8[]	x
	)

float8 [] matrix_column	(	float8[]	matrix,
		integer	col
	)

Parameters

matrix	Two-dimensional matrix
col	Column of the matrix to return (0-based index)

float8 norm1 ( float8[] x )

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)

Returns: \( \| x \|_1 = \sum_{i=1}^n |x_i| \)

float8 norm2 ( float8[] x )

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)

Returns: \( \| x \|_2 = \sqrt{\sum_{i=1}^n x_i^2} \)

aggregate float8 [] normalized_avg ( float8[] x )

Given vectors \( x_1, \dots, x_n \), define \( \widetilde{x} := \frac 1n \sum_{i=1}^n \frac{x_i}{\| x_i \|} \), and compute the normalized average \( \frac{\widetilde{x}}{\| \widetilde{x} \|} \).

Parameters

x	Point \( x_i \)

Returns: Normalized average \( \frac{\widetilde{x}}{\| \widetilde{x} \|} \)

float8 [] normalized_avg_vector_final ( float8[] state )

float8 [] normalized_avg_vector_transition	(	float8[]	state,
		float8[]	x
	)

float8 squared_dist_norm2	(	float8[]	x,
		float8[]	y
	)

Parameters

x	Vector \( \vec x = (x_1, \dots, x_n) \)
y	Vector \( \vec y = (y_1, \dots, y_n) \)

Returns: \( \| x - y \|_2^2 = \sum_{i=1}^n (x_i - y_i)^2 \)

Functions

Detailed Description

Function Documentation