L2_DISTANCE

Calculates the Euclidean (L2) distance between two vectors, measuring the straight-line distance between them in vector space.

Syntax

L2_DISTANCE(vector1, vector2)

Arguments

• vector1: First vector (ARRAY(FLOAT NOT NULL))
• vector2: Second vector (ARRAY(FLOAT NOT NULL))

Returns

Returns a FLOAT value representing the Euclidean (L2) distance between the two vectors. The value is always non-negative:

• 0: Identical vectors
• Larger values: Vectors that are farther apart

Description

The L2 distance, also known as Euclidean distance, measures the straight-line distance between two points in Euclidean space. It is one of the most common metrics used in vector similarity search and machine learning applications.

The function:

1. Verifies that both input vectors have the same length
2. Computes the sum of squared differences between corresponding elements
3. Returns the square root of this sum

The mathematical formula implemented is:

L2_distance(v1, v2) = √(Σ(v1ᵢ - v2ᵢ)²)

Where v1ᵢ and v2ᵢ are the elements of the input vectors.

info

• This function performs vector computations within Databend and does not rely on external APIs.

Examples

Create a table with vector data:

CREATE OR REPLACE TABLE vectors (
    id INT,
    vec ARRAY(FLOAT NOT NULL)
);

INSERT INTO vectors VALUES
    (1, [1.0000, 2.0000, 3.0000]),
    (2, [1.0000, 2.2000, 3.0000]),
    (3, [4.0000, 5.0000, 6.0000]);

Find the vector closest to [1, 2, 3] using L2 distance:

SELECT 
    id,
    vec, 
    L2_DISTANCE(vec, [1.0000, 2.0000, 3.0000]) AS distance
FROM 
    vectors
ORDER BY 
    distance ASC;

+----+-------------------------+----------+
| id | vec                     | distance |
+----+-------------------------+----------+
| 1  | [1.0000,2.0000,3.0000]  | 0.0      |
| 2  | [1.0000,2.2000,3.0000]  | 0.2      |
| 3  | [4.0000,5.0000,6.0000]  | 5.196152 |
+----+-------------------------+----------+