This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Vector functions

Vector functions perform calculations on vectors expressed as ARRAY inputs of types INT, FLOAT, or NUMERIC. The results of these calculations are returned as measurements in FLOAT.

1: COSINE_SIMILARITY
2: DOT_PRODUCT
3: VECTOR_L2
4: VECTOR_MAGNITUDE

Vector functions perform calculations on vectors expressed as ARRAY inputs of types INT, FLOAT, or NUMERIC. The results of these calculations are returned as measurements in FLOAT.

You can use these functions for semantic comparison to determine the vector similarity, or distance, between data vectors.

1 - COSINE_SIMILARITY

Returns the cosine similarity of two n-dimensional vectors. Cosine similarity is the cosine of the angle between two non-zero vectors. A returned value of 1 means that the vectors are identical, a value of 0 means the vectors are orthogonal, and a value of -1 means that the vectors are opposites.

Behavior type

Immutable

Syntax

COSINE_SIMILARITY( vector_arr1, vector_arr2 )

Arguments

vector_arr1: N-dimensional vector, type ARRAY of INT, FLOAT, NUMERIC
vector_arr2: N-dimensional vector, type ARRAY of INT, FLOAT, NUMERIC

Note

NULL and NaN values are treated equivalently. If any element in the input array is NULL or NaN, the function returns NULL.

For input vector arrays that have a norm of 0, the following logic is used:

If both vectors are 0, then a value of 1 is returned as they are identical.
If only one of the vectors is 0, then a value of 0 is returned as the vectors are orthogonal (zero similarity).

Returns

FLOAT

Examples

The following example computes the cosine similarity for two vector arrays:

SELECT id, vector_arr1, vector_arr2, COSINE_SIMILARITY(vector_arr1, vector_arr2) AS similarity FROM vectors_array;
id |  vector_arr1  |  vector_arr2  |    similarity     
----+---------------+---------------+-------------------
  1 | [1.0,2.0,3.0] | [4.0,5.0,6.0] | 0.974631846197076
  2 | [1.5,2.5,3.5] | [4.5,5.5,6.5] | 0.985871023540107
  3 | [0.0,1.0,0.0] | [0.0,0.0,1.0] |                 0
  4 | [2.0,0.0,0.0] | [0.0,2.0,0.0] |                 0
  5 | [1.0,1.0,1.0] | [1.0,1.0,1.0] |                 1
(5 rows)

The following example computes the cosine similarity for a 5D vector:

SELECT COSINE_SIMILARITY(Array[1.0, 2.0, 3.0, 4.0, 5.0], Array[1.0, 2.0, 3.0, 3.0, 4.0]);
COSINE_SIMILARITY
-------------------
  0.99321708930132
(1 row)

2 - DOT_PRODUCT

Returns the dot product of two n-dimensional vectors, represented as two equal length ARRAYs. Dot products may sometimes be referred to as scalar products.

Behavior type

Immutable

Syntax

DOT_PRODUCT( vector_arr1, vector_arr2 )

Arguments

vector_arr1: N-dimensional vector, type ARRAY of INT, FLOAT, NUMERIC
vector_arr2: N-dimensional vector, type ARRAY of INT, FLOAT, NUMERIC

Note

NULL and NaN values are treated equivalently. If any element in the input array is NULL or NaN, the function returns NULL.

Returns

FLOAT

Examples

The following example computes dot products for two vector arrays:

SELECT id, vector_arr1, vector_arr2, DOT_PRODUCT(vector_arr1, vector_arr2) AS dot_product FROM vectors_array;
id |  vector_arr1  |  vector_arr2  | dot_product 
----+---------------+---------------+-------------
  1 | [1.0,2.0,3.0] | [4.0,5.0,6.0] |          32
  2 | [1.5,2.5,3.5] | [4.5,5.5,6.5] |       43.25
  3 | [0.0,1.0,0.0] | [0.0,0.0,1.0] |           0
  4 | [2.0,0.0,0.0] | [0.0,2.0,0.0] |           0
  5 | [1.0,1.0,1.0] | [1.0,1.0,1.0] |           3
(5 rows)

The following example computes the dot product for a 5D vector:

SELECT DOT_PRODUCT(Array[1.0, 2.0, 3.0, 4.0, 5.0], Array[5.0, 4.0, 3.0, 2.0, 1.0]);
DOT_PRODUCT 
------------
         35

3 - VECTOR_L2

Returns the Euclidean, or L2, distance between two n-dimensional vectors.

Behavior type

Immutable

Syntax

VECTOR_L2( vector_arr1, vector_arr2 )

Arguments

vector_arr1: N-dimensional vector, type ARRAY of INT, FLOAT, NUMERIC
vector_arr2: N-dimensional vector, type ARRAY of INT, FLOAT, NUMERIC

Note

NULL and NaN values are treated equivalently. If any element in the input array is NULL or NaN, the function returns NULL.

Returns

FLOAT

Examples

The following example computes the distance for two vector arrays:

SELECT id, vector_arr1, vector_arr2, VECTOR_L2(vector_arr1, vector_arr2) AS distance FROM vectors_array;
id |  vector_arr1  |  vector_arr2  |     distance     
----+---------------+---------------+------------------
  1 | [1.0,2.0,3.0] | [4.0,5.0,6.0] | 5.19615242270663
  2 | [1.5,2.5,3.5] | [4.5,5.5,6.5] | 5.19615242270663
  3 | [0.0,1.0,0.0] | [0.0,0.0,1.0] |  1.4142135623731
  4 | [2.0,0.0,0.0] | [0.0,2.0,0.0] | 2.82842712474619
  5 | [1.0,1.0,1.0] | [1.0,1.0,1.0] |                0
(5 rows)

The following example computes the distance between two 5D vectors:

SELECT VECTOR_L2(Array[1.0, 2.0, 3.0, 4.0, 5.0], Array[1.0, 2.0, 3.0, 3.0, 4.0]);
VECTOR_L2 
-----------------
1.4142135623731
(1 row)

4 - VECTOR_MAGNITUDE

Calculates the magnitude of an n-dimensional vector. The magnitude of a vector is its length expressed as a positive scalar.

Behavior type

Immutable

Syntax

VECTOR_MAGNITUDE( vector_arr )

Arguments

vector_arr: N-dimensional vector, type ARRAY of INT, FLOAT, NUMERIC

Note

NULL and NaN values are treated equivalently. If any element in the input array is NULL or NaN, the function returns NULL.

Returns

FLOAT

Examples

The following example computes the vector magnitude for two input arrays using two function calls of VECTOR_MAGNITUDE():

SELECT id, vector_arr1, VECTOR_MAGNITUDE(vector_arr1) AS mag1, vector_arr2, VECTOR_MAGNITUDE(vector_arr2) AS mag2
FROM vectors_array;
id |  vector_arr1  |      mag1         |  vector_arr2  |     mag2      
----+---------------+------------------+---------------+------------------
  1 | [1.0,2.0,3.0] | 3.74165738677394 | [4.0,5.0,6.0] | 8.77496438739212
  2 | [1.5,2.5,3.5] | 4.55521678957215 | [4.5,5.5,6.5] | 9.63068014212911
  3 | [0.0,1.0,0.0] |                1 | [0.0,0.0,1.0] |                1
  4 | [2.0,0.0,0.0] |                2 | [0.0,2.0,0.0] |                2
  5 | [1.0,1.0,1.0] | 1.73205080756888 | [1.0,1.0,1.0] | 1.73205080756888
(5 rows)

The following example computes the vector magnitude for a 5D vector:

SELECT VECTOR_MAGNITUDE(Array[1.0, 2.0, 3.0, 4.0, 5.0]);
VECTOR_MAGNITUDE   
------------------
7.41619848709566
(1 row)