SQL Vector Functions and Operators

NuoDB provides functions to compute a value representing the similarity between two vectors. These functions use vector algebra functions to calculate distances or angles between the vectors.

Syntax

Function Syntax Description

vector_l1_distance(<vector1>,<vector2>)

L1 distance (also called Manhattan distance) between the two vectors.
vector1 and vector2 must have the same dimension.

Formula used: VECTOR_L1_DISTANCE(A,B)=SUM(ABS(A[i]-B[i])).

Return type: DOUBLE

Example:

SELECT vector_l1_distance(CAST('[-1,2,3]' as VECTOR(3,DOUBLE)), CAST('[1,1,1]' as VECTOR(3,DOUBLE))) FROM DUAL;

 [VECTOR_L1_DISTANCE]
 ---------------------
           5

vector_l2_distance(<vector1>, <vector2>)

L2 distance (also called Euclidean distance) between the two vectors.
vector1 and vector2 must have the same dimension.

Formula used: VECTOR_L2_DISTANCE(A,B)=SQRT(SUM((A[i]-B[i])^2)).

Return type: DOUBLE

Example:

SELECT vector_l2_distance(CAST('[-1,2,3]' as VECTOR(3,DOUBLE)), CAST('[1,1,1]' as VECTOR(3,DOUBLE))) FROM DUAL;

 [VECTOR_L2_DISTANCE]
 ---------------------
           3

vector_inner_product(<vector1>, <vector2>)

For normalized vectors (length 1) this is identical to the vector_cosine_distance. vector1 and vector2 must have the same dimension.

Formula used: VECTOR_INNER_PRODUCT(A,B)=SUM(A[i]*B[i]).

Return type: DOUBLE

Example:

SELECT vector_inner_product(CAST('[-1,2,3]' as VECTOR(3,DOUBLE)), CAST('[1,1,1]' as VECTOR(3,DOUBLE))) FROM DUAL;

 [VECTOR_INNER_PRODUCT]
 -----------------------
           4

vector_cosine_distance(<vector1>, <vector2>)

For normalized vectors this is the same as the vector_inner_product, but the vector_inner_product is faster to compute. vector1 and vector2 must have the same dimension.

Formula used:
VECTOR_COSINE_DISTANCE(A,B)= SUM(v1[i] * v2[i]) / (||v1|| * ||v2||) = SUM(v1[i] * v2[i]) / (sqrt(SUM(v1[i]^2)) * sqrt(SUM(v2[i]^2))))

Return type: DOUBLE in the range -1 to 1.

Example:

SELECT vector_cosine_distance(CAST('[-1,2,3]' as VECTOR(3,DOUBLE)), CAST('[1,1,1]' as VECTOR(3,DOUBLE))) FROM DUAL;

 [VECTOR_COSINE_DISTANCE]
 -------------------------
    0.6172133998483678

This function can only be used if neither of the vectors is the vector with all components zero.

These functions are often used in vector similarity search applications to determine similar embeddings vectors. For vector_l1_distance and vector_l2_distance, smaller values indicate that the vectors are closer to each other (more similar). For vector_cosine_distance, a value of 1 means the vectors point in the same direction, 0 means they are orthogonal, and -1 means they point in opposite directions.

Example

Compare colors from the RGB color wheel and find the closest match to the orange-ish color [255,100,0] using vector similarity search.

CREATE TABLE COLORS(NAME STRING, RGB VECTOR(3,DOUBLE));
INSERT INTO COLORS VALUES
('red', '[255,0,0]'), ('green', '[0,255,0]'), ('blue', '[0,0,255]');

SELECT name, VECTOR_COSINE_DISTANCE(RGB, '[255,100,0]')
FROM COLORS ORDER BY VECTOR_COSINE_DISTANCE(RGB, '[255,100,0]') DESC LIMIT 1;

 NAME  [VECTOR_COSINE_DISTANCE]
 ----- -------------------------
  red     0.9309731984870635

SQL Vector Functions and Operators

Syntax

Example

See Also