PolarDB:pg_similarity

前提条件

この機能は、次のエンジンを実行するPolarDB for PostgreSQL(Compatible with Oracle) クラスターでサポートされています。

PolarDB for PostgreSQL (Oracle互換) 2.0 (バージョン2.0.14.18.0以降)

SHOW polar_version;

概要

pg_similarity extensionは、類似距離の計算に使用されます。 =と <> を含む従来の演算子に加えて、~~~ と! 類似性クエリのpg_similarityによって定義されている これら2つの演算子の各々は、類似性関数を表す。 pg_similarityには3つの主要コンポーネントが含まれます。

• 関数: 文献で利用可能な類似性アルゴリズムを実装する関数のセット。 これらの関数はUDFとして使用することができ、類似性演算子を実装するためのベースになります。

• 演算子: 類似性関数の最上部に定義される演算子のセット。 それらは、類似性関数を使用して類似性閾値を取得し、その値をユーザ定義閾値と比較して、それが一致するかどうかを判定します。

• セッション変数: 類似性関数パラメータを格納する変数のセット。 これらの変数は、実行時に定義することができます。

関数と演算子

pg_similarityは、多くのよく知られた類似アルゴリズムをサポートします。 各アルゴリズムは特定のドメインに適しています。 以下のアルゴリズムが提供されます。

• L1距離 (シティブロックまたはマンハッタン距離として知られている);

• コサイン距離;

• ダイス系数;

• ユークリッド距離;

• ハミング距離;

• Jaccardの係数;

• ジャロ距離;

• ウィンクラー-距離;

• レーベンシュタイン距離;

• マッチング係数;

• モンジュ-エルカン係数;

• Needleman-Wunsch Coefficient;

• 重なり合う係数;

• Qグラムの间隔;

• スミス-ウォーターマン係数;

• スミス-ウォーターマン-ゴトー係数;

• サウンデックス距離。

一部のパラメーターは、pg_similarity関数と演算子の動作を制御します。 これらのパラメータは、トークナイザ、閾値、および正規化の3つのカテゴリに分類することができます。

• tokenizer: 文字列のトークン化方法を制御します。 有効な値は、alnum、gram、word、camelcaseです。 デフォルト値はalnumです。 すべてのトークンは小文字です。 このオプションは、コンパイル時に設定できます。 詳細については、ソースコードの「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のウィンドウを1文字ずつ文字列全体にスライドさせるスライディングバイワン技術を使用することにより、文字列から抽出できます。 たとえば、文字列 "euler taveira" (n = 3を使用) は、"eul" 、"ule" 、"ler" 、"er" 、"r t" 、"ta" 、"tav" 、"ave" 、"vei" 、"eir" 、"ira" としてトークン化されます。 フルn-gramを有効にすると、"e" 、"eu" 、"ra" 、"a" がトークンのセットに追加されます。 このオプションは、コンパイル時に設定できます。 ソースコードの「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です。

• normalized: 類似性係数または距離が (0.0と1.0の間で) 正規化されるかどうかを制御します。 正規化された値は、文字列を一致させるために演算子によって自動的に使用されます。 デフォルト値は true です。

使用法

拡張機能の作成

CREATE EXTENSION pg_similarity;

実行時のパラメーターの設定

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

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;

テストテーブルの作成

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');

ジャロ関数の使用

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)

levenshtein演算子の使用

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)

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)

結果の比較

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)