linalg.sql_in File Reference

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

__deconstruct_lower_triangle()

set<record> __deconstruct_lower_triangle

(

float8 []

in_array

)

Parameters

in_array

2-D array

_closest_column()

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

_closest_columns()

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].

array_to_1d()

float8 [] array_to_1d

(

float8 []

in_array

)

Parameters

in_array

1-D or 2-D array

array_to_2d()

float8 [] array_to_2d

(

float8 []

in_array

)

Parameters

in_array

Input array with first 2 elements describing dimensions

avg()

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 \)

avg_vector_final()

float8 [] avg_vector_final

(

float8 []

state

)

avg_vector_merge()

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

avg_vector_transition()

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

closest_column() [1/2]

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() [2/2]

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

closest_columns() [1/2]

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

closest_columns() [2/2]

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

cosine_similarity()

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 \|} \)

deconstruct_2d_array()

set<record> deconstruct_2d_array

(

float8 []

in_array

)

Parameters

in_array

2-D array

dist_angle()

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) \)

dist_inf_norm()

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\| \)

dist_jaccard()

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|} \)

dist_norm1()

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| \)

dist_norm2()

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} \)

dist_pnorm()

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}} \)

dist_tanimoto()

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} \)

get_col()

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

Parameters

in_2d_array	Input 2-D array
index

get_row()

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

Parameters

in_2d_array	Input 2-D array
index

index_2d_array()

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

Parameters

in_2d_array	Input 2-D array
index

matrix_agg()

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 \)

matrix_agg_final()

float8 [] matrix_agg_final

(

float8 []

state

)

matrix_agg_transition()

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

matrix_column()

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

Parameters

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

norm1()

float8 norm1

(

float8 []

x

)

Parameters

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

Returns

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

norm2()

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} \)

normalized_avg()

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} \|} \)

normalized_avg_vector_final()

float8 [] normalized_avg_vector_final

(

float8 []

state

)

normalized_avg_vector_transition()

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

squared_dist_norm2()

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 \)