如何利用SQL实现余弦相似度匹配

一、余弦相似度的原理

在利用sql实现余弦相似度匹配之前，先讲一讲实现余弦相似度的原理，相信搞清楚原理之后，你可以用多种方法计算出两个向量之间的余弦相似度。

1.基本原理

余弦相似度是通过计算两个向量的夹角余弦值来评估它们的相似度，也可以说是根据两个空间向量的夹角来评估两个个体的差异度。

由下图可以看出，夹角越接近0°，余弦值越接近于1，这时它们之间的相似性越高，反之，夹角越接近180°，余弦值越接近于-1，这时它们之间的余弦相似度越低，当然等于-1不完全等同于他们之间没有相似度，这个得视情况而定。

余弦相似度也可以用余弦距离表示，余弦距离通常定义为

1-\cos \left ( \theta \right )

 ，也就是用 1 减去它们的余弦相似度来得到一个表示距离的数值，该数值范围在[0,2]之间，值越小表示两个向量越 “接近”，相似度越高。

这里假设有两个向量

\underset{a}{\rightarrow}

和

\underset{b}{\rightarrow}

，

\underset{a}{\rightarrow}

= \left ( a_{1} ,a_{2},...,a_{n}\right )

，向量

\underset{b}{\rightarrow}

= \left ( b_{1} ,b_{2},...,b_{n}\right )

,则

\underset{a}{\rightarrow}

、

\underset{b}{\rightarrow}

两向量的余弦相似度为：

\cos \left ( \theta \right )=\frac{\underset{a}{\rightarrow} \cdot \underset{b}{\rightarrow}} {​{\left | \underset{a}{\rightarrow} \right |}\cdot{\left | \underset{b}{\rightarrow} \right |}}=\frac{\sum_{i=1}^{n}a_{i}b_{i}}{\sqrt{\sum_{i=1}^{n}a_{i}^{2}}\sqrt{\sum_{i=1}^{n}b_{i}^{2}}}

从上述公式可以看出，要计算两个向量的余弦相似度，只需要计算出两个向量的点积与模即可，接下来我们就分别计算两个向量的点积与模。

1.1.向量的点积

两个向量的点积可以解释为，一个向量的模长与另一个向量在此向量方向上投影的长度的乘积，假设有两个向量

\underset{a}{\rightarrow}

= \left ( a_{1} ,a_{2},...,a_{n}\right )

，向量

\underset{b}{\rightarrow}

= \left ( b_{1} ,b_{2},...,b_{n}\right )

，向量的点积也就是

\underset{a}{\rightarrow}\cdot \underset{b}{\rightarrow}=\left | \underset{a}{\rightarrow} \right |\cdot \left | \underset{b}{\rightarrow} \right |\cos \left ( \theta \right )

，其计算公式为：

\underset{a}{\rightarrow}\cdot \underset{b}{\rightarrow}

=\sum_{i=1}^{n}a_{i}\cdot b_{i}=a_{1}\cdot b_{1}+a_{2}\cdot b_{2}+\cdots +a_{n}\cdot b_{n}

上述公式中

a_{i}

，

b_{i}

为空间向量的坐标。

1.2.向量的模

从上例可以看出，模其实就是向量的长度（也称为范数），向量的长度是用欧几里得距离（Euclidean distance）算出，假设有个向量

\underset{a}{\rightarrow}

= \left ( a_{1} ,a_{2},...,a_{n}\right )

，则

\underset{a}{\rightarrow}

模的计算方法为：

\left | \underset{a}{\rightarrow} \right |=\sqrt{a_{1}^{2}+ a_{2}^{2}+ \cdots +a_{n}^{2}}

这和欧氏距离的计算方法比较相似，所以上手很简单。

二、利用SQL计算相似度

通过上面的学习你应该已经搞清楚了余弦相似度的基本原理，接下来我们就开始利用sql来进行余弦相似度的计算。

上述可知，我们通体进行的都是向量的计算，所以在进行相似度计算之前，要先将数据转换成向量的形式，这里以 My sql 为例（以下简称为sql），而sql并不会直接将数据转换为向量形式，所以我们也不能真正的进行向量之间的运算，只能将数据转换成类似于向量的形式（如int类型），所以在进行计算之前，应先将数据转换为 int 或float类型。

以下做一个简单的示例，在这里我们创建一个简单的数据表table_a ，在向其中添加一些数据:

-- 创建table_a表
create table table_a(
    field1 int,
    field2 int,
    field3 char
);

-- 向其中添加多条数据
insert into table_a (field1, field2, field3)
values
    (3, 4, '否'),
    (6, 8, '是'),
    (2, 1, '是');

根据建表语句，可以看出其中有三列数据，两列int类型数据，一列char类型，接下来我们要做的就是将第三列 field3 转换为 int类型，并进行相似度计算。

1.数据类型转换

在这里我们可以重新创建一个中间表，来将 field3 列转换为数据类型，并保存到新数据表中：

create table_b as
select field1,
       field2,
       case when field3='是' then 1
            when field3='否' then 0 end as field3
  from table_a;

这条 SQL 语句的主要作用是基于已有的 table_a 表创建一个新的表 table_b，并且在创建新表的同时对从 table_a 中选取的数据进行了一定的转换操作。具体来说，它从 table_a 表中选取了 field1 和 field2 字段的原始数据，然后对 field3 字段进行了条件判断转换，将字符型的 '是' 转换为数值 1，'否' 转换为数值 0，并将转换后的数据填充到新表 table_b 对应的字段中。这样就将第三列 field3 的文本分类数据转化为数据1和0，接下来就可以计算相似度了。

2.相似度计算

2.1.点积的计算

想要计算余弦相似度，先要计算两个向量的点积与模，表 table_b 中的 field1、field2 和 field3 可以分别看做是三个向量，则由点积计算的公式可以知道他们的点积为：

select sum(field1 * field2 * field3) as dot_product
from table_b;

这里先将每行记录的 field1、field2 和 field3 三个字段的值相乘，对于每一行记录都会进行这样的操作，得到该行三个字段的乘积结果。

SUM 是聚合函数，用于对前面乘法运算得到的每一行的乘积结果进行求和操作，使用 SUM 函数对所有行的乘积结果进行求和，将最终的点积值以 dot_product 作为列名返回。

2.2.模的计算

点积计算完之后，接下来我们通过SQRT 函数计算向量的模，SQRT 函数是求平方根函数。

select sqrt(sum(field1 * field1)) as field1_norm
from table_b;

这里先使用乘法运算符 * 将 field1 的值与其自身相乘（即求平方），然后通过聚合函数 SUM 对所有行的平方值进行求和，最后使用 SQRT 函数（求平方根函数）计算出总和的平方根，也就是 field1 这个 “向量” 的模，通过 AS field1_norm 给结果列命名为 field1_norm。

同理可以得出 field2 与 field3 的模，这里不再赘述，接下来只需要将上述结果合起来相除便可。

select dot_product/field1_norm as similar
  from (
select sum(field1 * field2 * field3) as dot_product
     , sqrt(sum(field1 * field1))*sqrt(sum(field2 * field2))*sqrt(sum(field3 * field3)) as field1_norm
from table_b) as t;

到这里相似度的计算就算是结束了，不过需要注意的是，这里的相似度只是用余弦相似度公式计算出来的数值而已，而使用的也并不是真正的向量，只是将sql中的字段转换为类似向量的一种形式而已，所以仍要注意的是，这种方法只针对数值型数据可行，使用之前尤其要注意这一点，而且因为数据量级过大会导致计算量较大，所以并不适用于大批量的数据。