PolarDB：pg_similarity（相似距離計算）

前提條件

支援的PolarDB PostgreSQL版（相容Oracle）的版本如下：

Oracle文法相容 2.0（核心小版本2.0.14.18.0及以上）。

SHOW polar_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)