使用 SQL 函数 - OceanBase 数据库 V4.3.3

本文介绍了 OceanBase 支持的向量函数及使用注意事项。

使用注意事项

维度不同的向量数据不允许做下述运算，会报错 different vector dimensions %d and %d。
当结果超出浮点数值域时，会报错 value out of range: overflow / underflow。
向量索引支持计算 L2 距离和内积距离。具体请参见创建向量索引。
向量索引搜索支持调用本文档中的 L2_distance、Cosine_distance、Inner_product、Negative_inner_product 距离函数。

准备工作

使用向量索引前，需要通过设置 ob_vector_memory_limit_percentage 开启向量功能。

ALTER SYSTEM SET ob_vector_memory_limit_percentage = 30;

距离函数

距离函数用于计算两个向量之间的距离，具体计算方式依据不同的距离算法而不同。

L2_distance

欧几里得距离反映的是被比较的向量坐标之间的距离--基本上是两个向量之间的直线距离。使用勾股定理应用于向量坐标来计算：

勾股定理

函数语法如下：

l2_distance(vector v1, vector v2)

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

返回值为 distance(double) 距离值。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t1(c1 vector(3));
INSERT INTO t1 VALUES('[1,2,3]');
SELECT l2_distance(c1, [1,2,3]), l2_distance([1,2,3],[1,1,1]), l2_distance('[1,1,1]','[1,2,3]') FROM t1;

返回结果如下：

+--------------------------+------------------------------+----------------------------------+
| l2_distance(c1, [1,2,3]) | l2_distance([1,2,3],[1,1,1]) | l2_distance('[1,1,1]','[1,2,3]') |
+--------------------------+------------------------------+----------------------------------+
|                        0 |             2.23606797749979 |                 2.23606797749979 |
+--------------------------+------------------------------+----------------------------------+
1 row in set

L1_distance

曼哈顿距离用于计算两个点在标准的坐标系中的绝对轴距总和。计算公式为：

曼哈顿距离

函数语法如下：

l1_distance(vector v1, vector v2)

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

返回值为 distance(double) 距离值。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t2(c1 vector(3));
INSERT INTO t2 VALUES('[1,2,3]');
INSERT INTO t2 VALUES('[1,1,1]');
SELECT l1_distance(c1, [1,2,3]) FROM t2;

返回结果如下：

+--------------------------+
| l1_distance(c1, [1,2,3]) |
+--------------------------+
|                        0 |
|                        3 |
+--------------------------+
2 rows in set

Cosine_distance

余弦相似度（cosine similarity）是衡量两个向量的角度差异，它反映了两个向量在方向上的相似度，与向量的长度（大小）无关。余弦相似度的取值范围为 [-1, 1]，其中 1 表示向量完全相同的方向，0 表示正交，-1 表示完全相反的方向。

余弦相似度的计算方式为：

余弦相似度

由于余弦相似度度量越接近于 1 表示越相似，因此有时也使用余弦距离（或余弦不相似度）作为向量间距离的一种衡量方式，余弦距离可以通过 1 减去余弦相似度来计算：

余弦距离

余弦距离的取值范围是 [0, 2]，其中 0 表示完全相同的方向（无距离），而 2 表示完全相反的方向。

函数语法如下：

cosine_distance(vector v1, vector v2)

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

返回值为 distance(double) 距离值。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t3(c1 vector(3));
INSERT INTO t3 VALUES('[1,2,3]');
INSERT INTO t3 VALUES('[1,2,1]');
SELECT cosine_distance(c1, [1,2,3]) FROM t3;

返回结果如下：

+------------------------------+
| cosine_distance(c1, [1,2,3]) |
+------------------------------+
|                            0 |
|          0.12712843905603044 |
+------------------------------+
2 rows in set

Inner_product

内积又称为点积或数量积，表示两个向量之间的一种乘积。在几何意义上，内积表示两个向量的方向关系和大小关系。内积的计算方式为：

语法如下：

inner_product(vector v1, vector v2)

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

返回值为 distance(double) 距离值。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t4(c1 vector(3));
INSERT INTO t4 VALUES('[1,2,3]');
INSERT INTO t4 VALUES('[1,2,1]');
SELECT inner_product(c1, [1,2,3]) FROM t4;

返回结果如下：

+----------------------------+
| inner_product(c1, [1,2,3]) |
+----------------------------+
|                         14 |
|                          8 |
+----------------------------+
2 rows in set

Negative_inner_product

Negative_inner_product 用于计算两个向量之间的负内积，计算方式为：

负内积

语法如下：

negative_inner_product(vector v1, vector v2)

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

返回值为 distance(double) 距离值。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t4(c1 vector(3));
INSERT INTO t4 VALUES('[1,2,3]');
INSERT INTO t4 VALUES('[1,2,1]');
SELECT negative_inner_product(c1, [1,2,3]) FROM t4;

返回结果如下：

+-------------------------------------+
| negative_inner_product(c1, [1,2,3]) |
+-------------------------------------+
|                                 -14 |
|                                  -8 |
|                                 -14 |
|                                  -8 |
+-------------------------------------+
4 rows in set

Vector_distance

vector_distance 用于计算两个向量之间的距离，通过指定参数来选择不同的距离算法。

语法如下：

vector_distance(vector v1, vector v2 [, string metric])

vector v1/v2 参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

metric 参数用于指定距离算法，可选：

如果不指定，默认算法为 euclidean。
如果指定，可选择的值有且只有
- euclidean。表示欧式距离，和 L2_distance 含义相同。
- manhattan。表示曼哈顿距离，和 L1_distance 含义相同。
- cosine。表示余弦距离，和 Cosine_distance 含义相同。
- dot。表示内积，和 Inner_product 含义相同。

返回值说明如下：

返回值为 distance(double) 距离值。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t5(c1 vector(3));
INSERT INTO t5 VALUES('[1,2,3]');
INSERT INTO t5 VALUES('[1,2,1]');
SELECT vector_distance(c1, [1,2,3], euclidean) FROM t5;

返回结果如下：

+-----------------------------------------+
| vector_distance(c1, [1,2,3], euclidean) |
+-----------------------------------------+
|                                       0 |
|                                       2 |
+-----------------------------------------+
2 rows in set

算术函数

算术函数提供向量类型与向量类型、单级数组类型、特殊字符串类型，以及单级数组类型与单级数组类型、特殊字符串类型的加（+）、减（-）和乘（*）法算术计算。计算方式为逐元素计算，如加法计算为：

语法如下：

v1 + v2
v1 - v2
v1 * v2

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。**注意：**两个参数不可以同时为字符串类型，必须有一个参数为向量或单级数组类型。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

当两个参数中至少有一个参数为向量类型时，返回值为和向量参数相同的向量类型。
当两个参数中只有单级数组类型时，返回值为 array(float) 类型。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t6(c1 vector(3));
INSERT INTO t6 VALUES('[1,2,3]');
SELECT [1,2,3] + '[1.12,1000.0001, -1.2222]', c1 - [1,2,3] FROM t6;

返回结果如下：

+---------------------------------------+--------------+
| [1,2,3] + '[1.12,1000.0001, -1.2222]' | c1 - [1,2,3] |
+---------------------------------------+--------------+
| [2.12,1002,1.7778]                    | [0,0,0]      |
+---------------------------------------+--------------+
1 row in set

比较函数

比较函数提供向量类型与向量类型、单级数组类型、特殊字符串类型的比较计算，包括 =、!=、>、<、>=、<= 几种比较符号。计算方式为逐元素的字典序比较。

语法如下：

v1 = v2
v1 != v2
v1 > v2
v1 < v2
v1 >= v2
v1 <= v2

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。

注意

两个参数必须有一个参数为向量类型。
两个参数的维度必须相同。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

返回值为 bool 类型。
当任意参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t7(c1 vector(3));
INSERT INTO t7 VALUES('[1,2,3]');
SELECT c1 = '[1,2,3]' FROM t7;

返回结果如下：

+----------------+
| c1 = '[1,2,3]' |
+----------------+
|              1 |
+----------------+
1 row in set

聚合函数

注意

不支持使用向量列作为 GROUP BY 条件，不支持 DISTINCT。

Sum

Sum 函数用于计算表中向量列的和，采用逐元素累加的方式进行计算，以得到和向量。

语法如下：

sum(vector v1)

参数说明如下：

仅支持向量类型。

返回值说明如下：

返回 sum (vector) 值。
NULL 列不计入累加结果。

示例如下：

CREATE TABLE t8(c1 vector(3));
INSERT INTO t8 VALUES('[1,2,3]'),('[2,3,4]'),('[3,4,5]');
SELECT sum(c1) FROM t8;

返回结果如下：

+---------+
| sum(c1) |
+---------+
| [6,9,12] |
+---------+
1 row in set

多行向量逐元素相加：第 1 维 1+2+3=6，第 2 维 2+3+4=9，第 3 维 3+4+5=12，因此和为 [6,9,12]。

Avg

Avg 函数用于计算表中向量列的平均值。

语法如下：

avg(vector v1)

参数说明如下：

仅支持向量类型。

返回值说明如下：

返回 avg (vector) 值。
NULL 列不计入 sum 过程和 count 过程。
输入参数为空时，输出 NULL。

示例如下：

CREATE TABLE t9(c1 vector(3));
INSERT INTO t9 VALUES('[2,4,6]'),('[4,6,8]'),('[6,8,10]');
SELECT avg(c1) FROM t9;

返回结果如下：

+---------+
| avg(c1) |
+---------+
| [4,6,8] |
+---------+
1 row in set

多行向量逐元素求平均：第 1 维 (2+4+6)/3=4，第 2 维 (4+6+8)/3=6，第 3 维 (6+8+10)/3=8，因此平均值为 [4,6,8]。

其他常见向量函数

Vector_norm

Vector_norm 函数用于计算向量的欧几里得范数（模），表示向量和原点之间的欧几里得距离。计算方式为：

欧几里得范数

语法如下：

vector_norm(vector v1)

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。
当存在单级数组类型参数时，该参数元素不允许出现 NULL。

返回值说明如下：

返回 norm(double) 模值。
当参数为 NULL 时，返回 NULL。

示例如下：

CREATE TABLE t10(c1 vector(3));
INSERT INTO t10 VALUES('[1,2,3]');
SELECT vector_norm(c1),vector_norm([1,2,3]) FROM t10;

返回结果如下：

+--------------------+----------------------+
| vector_norm(c1)    | vector_norm([1,2,3]) |
+--------------------+----------------------+
| 3.7416573867739413 |   3.7416573867739413 |
+--------------------+----------------------+
1 row in set

Vector_dims

Vector_dims 函数用于返回向量维度。

语法如下：

vector_dims(vector v1)

参数说明如下：

除了向量类型外，参数可接受任意可强转为向量类型的其他类型，如字符串类型（如 '[1,2,3]'）。

返回值说明如下：

返回 dims(int64) 维度值。
当参数为 NULL 时会报错。

示例如下：

CREATE TABLE t11(c1 vector(3));
INSERT INTO t11 VALUES('[1,2,3]');
INSERT INTO t11 VALUES('[1,1,1]');
SELECT vector_dims(c1), vector_dims('[1,2,3]') FROM t11;

返回结果如下：

+-----------------+------------------------+
| vector_dims(c1) | vector_dims('[1,2,3]') |
+-----------------+------------------------+
|               3 |                      3 |
|               3 |                      3 |
+-----------------+------------------------+
2 rows in set