pg_similarity（相似距离计算）

前提条件

支持的PolarDB PostgreSQL版的版本如下：

PostgreSQL 14（内核小版本14.10.18.0及以上）。

SELECT version();

概述

pg_similarity插件用于相似距离计算。除了传统的运算符（例如，=和<>）之外，还可以使用pg_similarity定义的~~~和!两个运算符（都表示一个相似度函数）进行查询。pg_similarity拥有以下三个主要组件：

• 函数：实现文献中可用的相似度算法的一组函数。这些函数可以用作UDF，也是实现相似度运算符的基础。

• 运算符：基于相似度函数定义的一组运算符。这些运算符使用相似度函数来获取相似度阈值，并将其值与用户定义的阈值进行比较，以判断它们是否匹配。

• 会话级变量：一组存储相似度函数参数的变量，可以在运行时定义。

函数与运算符

pg_similarity插件支持了大部分知名的相似度算法，每个算法都适用于特定领域。提供的算法如下所示：

• L1距离（L1 Distance，也称为城市街区距离或曼哈顿距离）

• 余弦距离（Cosine Distance）

• Dice系数（Dice Coefficient）

• 欧几里得距离（Euclidean Distance）

• 汉明距离（Hamming Distance）

• 杰卡德系数（Jaccard Coefficient）

• 贾罗距离（Jaro Distance）

• 贾罗-温克勒距离（Jaro-Winkler Distance）

• 莱文斯坦距离（Levenshtein Distance）

• 匹配系数（Matching Coefficient）

• 孟格-埃尔坎系数（Monge-Elkan Coefficient）

• 尼德曼-温奇系数（Needleman-Wunsch Coefficient）

• 重叠系数（Overlap Coefficient）

• Q-gram距离（Q-Gram Distance）

• 史密斯-沃特曼系数（Smith-Waterman Coefficient）

• 史密斯-沃特曼-戈托系数（Smith-Waterman-Gotoh Coefficient）

• 声码距离（Soundex Distance）

pg_similarity函数和运算符的行为由多个参数控制。这些参数可以被归为三类：分词器（tokenizer）、阈值（threshold）和归一化（normalized）。

• 分词器：控制字符串如何被分词。所有分词都是小写（该选项可以在编译时设置，参见源代码中的PGS_IGNORE_CASE）。取值范围如下：

  • alnum（默认）：分隔符是任何非字母数字字符，即只有标准C语言环境中的字母字符和数字（0-9）会被接受为分词。例如，字符串Euler_Taveira_de_Oliveira 22/02/2011可被分词为Euler、Taveira、de、Oliveira、22、02、2011。

  • gram：一个n-gram是指长度为n的子序列。通过滑动窗口技术（即通过一个字符滑动一个长度为n的窗口）从字符串中提取n-gram。例如，字符串euler taveira（使用n=3）可被分词为eul、ule、ler、er 、r t、 ta、tav、ave、vei、eir和ira。有些会将 e、 eu、ra 和a 添加到分词集中，即为完整n-grams（该选项可以在编译时设置，参见源代码中的PGS_FULL_NGRAM）。

  • word：分隔符是空白字符（空格、换页符、换行符、回车符、水平制表符和垂直制表符）。例如，字符串Euler Taveira de Oliveira 22/02/2011可被分词为Euler、Taveira、de、Oliveira和22/02/2011。

  • camelcase：分隔符是大写字符，但它们也包含作为第一个分词字符。例如，字符串EulerTaveira de Oliveira被分词为Euler、Taveira de 和Oliveira。

• 阈值：控制比较字符串被判定为匹配的临界值。对于每对字符串，如果计算出的值（使用相应的相似度函数）大于或等于阈值，则认为匹配。取值范围为：0.0~1.0。默认值为0.7。

• 归一化：控制相似度系数/距离是否被归一化（如果相似度系数被归一化，则系数的范围会在[0,1]之间）。归一化值会被运算符自动用来匹配字符串，即该参数只有在使用相似度函数时才有意义。默认值为true。

使用方法

创建插件

CREATE EXTENSION pg_similarity;

在运行时设置参数

• 修改参数pg_similarity.levenshtein_threshold的值。

  --- 查看参数pg_similarity.levenshtein_threshold当前的值
  SHOW pg_similarity.levenshtein_threshold;

  返回结果如下：

   pg_similarity.levenshtein_threshold 
  -------------------------------------
   0.7
  (1 row)

  修改参数pg_similarity.levenshtein_threshold的值后查看。

  SET pg_similarity.levenshtein_threshold TO 0.5;
  SHOW pg_similarity.levenshtein_threshold;

  返回结果如下：

   pg_similarity.levenshtein_threshold 
  -------------------------------------
   0.5
  (1 row)

• SET pg_similarity.cosine_tokenizer TO camelcase;

• SET pg_similarity.euclidean_is_normalized TO false;

使用jaro函数

1. 创建测试表foo和bar，并插入测试数据。

   • CREATE TABLE foo (a TEXT);
     INSERT INTO foo VALUES
       ('Euler'),
       ('Oiler'),
       ('Euler Taveira de Oliveira'),
       ('Maria Taveira dos Santos'),
       ('Carlos Santos Silva');

   • CREATE TABLE bar (b TEXT);
     INSERT INTO bar VALUES
       ('Euler T. de Oliveira'),
       ('Euller'),
       ('Oliveira, Euler Taveira'),
       ('Sr. Oliveira');

2. 使用jaro函数。

   SELECT a, b, jaro(a, b) FROM foo, bar;

   返回结果如下：

   SELECT a, b, jaro(a, b) FROM foo, bar;
                a             |            b            |        jaro
   ---------------------------+-------------------------+--------------------
    Euler                     | Euler T. de Oliveira    |               0.75
    Euler                     | Euller                  | 0.9444444444444444
    Euler                     | Oliveira, Euler Taveira | 0.6057971014492753
    Euler                     | Sr. Oliveira            | 0.5055555555555555
    Oiler                     | Euler T. de Oliveira    | 0.4722222222222222
    Oiler                     | Euller                  |                0.7
    Oiler                     | Oliveira, Euler Taveira |  0.672463768115942
    Oiler                     | Sr. Oliveira            | 0.6722222222222223
    Euler Taveira de Oliveira | Euler T. de Oliveira    | 0.7980701754385964
    Euler Taveira de Oliveira | Euller                  | 0.6777777777777777
    Euler Taveira de Oliveira | Oliveira, Euler Taveira | 0.7731884057971014
    Euler Taveira de Oliveira | Sr. Oliveira            | 0.5922222222222222
    Maria Taveira dos Santos  | Euler T. de Oliveira    | 0.6023504273504273
    Maria Taveira dos Santos  | Euller                  | 0.3055555555555556
    Maria Taveira dos Santos  | Oliveira, Euler Taveira | 0.5350241545893719
    Maria Taveira dos Santos  | Sr. Oliveira            | 0.6342592592592593
    Carlos Santos Silva       | Euler T. de Oliveira    | 0.5421052631578946
    Carlos Santos Silva       | Euller                  | 0.3128654970760234
    Carlos Santos Silva       | Oliveira, Euler Taveira | 0.6066615814899567
    Carlos Santos Silva       | Sr. Oliveira            | 0.5077276524644945
   (20 rows)

运算符~==

1. 创建测试表格foo和bar，并插入测试数据，请参见使用jaro函数。

2. 不同条件下使用运算符~==。

   • 当pg_similarity.levenshtein_threshold参数值为0.5。

     SHOW pg_similarity.levenshtein_threshold;
      pg_similarity.levenshtein_threshold
     -------------------------------------
      0.5
     (1 row)
     
     SELECT a, b, lev(a,b) FROM foo, bar WHERE a ~== b;

     返回结果如下：

                  a             |          b           |        lev
     ---------------------------+----------------------+--------------------
      Euler                     | Euller               | 0.8333333333333334
      Oiler                     | Euller               |                0.5
      Euler Taveira de Oliveira | Euler T. de Oliveira |               0.76
     (3 rows)

   • 当pg_similarity.levenshtein_threshold参数值为0.7。

     SET pg_similarity.levenshtein_threshold = 0.7;
     SELECT a, b, lev(a,b) FROM foo, bar WHERE a ~== b;

     返回结果如下：

                  a             |          b           |        lev
     ---------------------------+----------------------+--------------------
      Euler                     | Euller               | 0.8333333333333334
      Euler Taveira de Oliveira | Euler T. de Oliveira |               0.76
     (2 rows)

运算符效果比较

1. 创建测试表foo和bar，并插入测试数据，请参见使用jaro函数。

2. 比较不同运算符的效果。

   • ~@@

     SELECT * FROM bar WHERE b ~@@ 'euler'; -- jaro-winkler operator

     返回结果如下：

               b
     ----------------------
      Euler T. de Oliveira
      Euller
     (2 rows)

   • ~~~

     SELECT * FROM bar WHERE b ~~~ 'euler'; -- qgram operator

     返回结果如下：

      b
     ---
     (0 rows)

   • ~==

     SELECT * FROM bar WHERE b ~== 'euler'; -- levenshtein operator

     返回结果如下：

        b
     --------
      Euller
     (1 row)

   • ~##

     SELECT * FROM bar WHERE b ~## 'euler'; -- cosine operator

     返回结果如下：

      b
     ---
     (0 rows)