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 and vector $\vec x$ compute the column of 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 and vector $\vec x$ compute the columns of 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 that are closest to . In case of ties, the first such indices are returned.
distances DOUBLE PRECISION[] - The distances between the columns of with indices in columns_ids and . That is, distances[i] contains $\operatorname{dist}(\vec{m_j}, \vec x)$ , where 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

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 that is closest to . 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 and . 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

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

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

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