EUCLIDEAN_ DISTANCE
On this page
Returns the scalar Euclidean distance between two vector input values.
Syntax
EUCLIDEAN_DISTANCE(vector_expression, vector_expression)
Arguments
-
vector_
: An expression that evaluates to a vector.expression The vector must be encoded as a blob containing packed single-precision or double-precision floating-point numbers in little-endian byte order.
Remarks
To execute this function, the host processor must support AVX2 instruction set extensions.
If the result of EUCLIDEAN_
is infinity, negative infinity, or not a number (NaN), NULL
will be returned instead.
EUCLIDEAN_
is computationally equivalent to SQRT(DOT_
.EUCLIDEAN_
function is more efficient than the latter.
Examples
Example: SELECT Using EUCLIDEAN_
The following example executes EUCLIDEAN_
on two rows containing vectors.HEX()
built-in function is also used to return a readable form of the binary output.
Create a table with two BLOB
-typed columns:
CREATE TABLE euc_t (a BLOB, b BLOB);
Using the JSON_
INSERT INTO euc_t VALUES (JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'), JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'));
To demonstrate the contents of the table, use the HEX()
built-in function to return a readable form of the binary data:
SELECT HEX(a), HEX(b) FROM euc_t;
+--------------------------+--------------------------+
| HEX(a) | HEX(b) |
+--------------------------+--------------------------+
| 3333333FCDCC4C3E9A99D93F | 0000803F0000003F00000040 |
+--------------------------+--------------------------+
Query the table using the EUCLIDEAN_
function in a SELECT
statement:
SELECT EUCLIDEAN_DISTANCE(a, b) FROM euc_t;
+------------------------------+
| EUCLIDEAN_DISTANCE(a, b) |
+------------------------------+
| 0.5196152239171921 |
+------------------------------+
Example: EUCLIDEAN_
The following example uses JSON_
as input parameters to the EUCLIDEAN_
built-in function:
SELECT EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]'));
+--------------------------------------------------------------------------------------------+
| EUCLIDEAN_DISTANCE(JSON_ARRAY_PACK('[1.0, 0.5, 2.0]'), JSON_ARRAY_PACK('[0.7, 0.2, 1.7]')) |
+--------------------------------------------------------------------------------------------+
| 0.5196152239171921 |
+--------------------------------------------------------------------------------------------+
Example: Computing A Large Number of Euclidean Distances Efficiently, When there are Repeating Inputs
Suppose x
and y
are lists of vectors, where x
contains a large number of items.x
, you want to calculate the euclidean distance for each vector in y
, where the vector in x
is the first euclidean distance input and the vector in y
is the second euclidean distance input.x
and y
into two tables perform a cross join.
SELECT x.vector, EUCLIDEAN_DISTANCE(x.vector, y.vector)FROM x,y;
When EUCLIDEAN_
is calculated using a vector in x.
for each vector in y.
, the vector in x.
is loaded from memory to CPU only once.
Related Topics
Last modified: June 20, 2023