ApsaraDB RDS for PostgreSQL の文字列類似度マッチング機能の使用方法 - ApsaraDB RDS

fuzzystrmatch 拡張を使用すると、2 つの文字列の類似度を測定できます。この機能は、データベースクエリにおけるスペルミス、名前のバリエーション、発音に基づくマッチングの処理に役立ちます。ApsaraDB RDS for PostgreSQL では、Soundex、Levenshtein、Metaphone、Double Metaphone の 4 種類のアルゴリズムがサポートされています。

拡張の有効化または無効化

-- 有効化
CREATE EXTENSION fuzzystrmatch;

-- 無効化
DROP EXTENSION fuzzystrmatch;

アルゴリズムの選択

アルゴリズム	仕組み	最適な用途
Soundex	発音に基づいて文字列をエンコードし、4 文字のコードを返します。	英語の名前に対する発音一致検索
Levenshtein	ある文字列を別の文字列に変換するために必要な最小編集回数（挿入、削除、置き換え）をカウントします。	タイプミスの検出およびあいまい検索
Metaphone	英語の発音に基づいて文字列をエンコードします。Soundex より精度が高いです。	英単語の発音一致検索
Double Metaphone	主コードと代替コードの 2 種類の発音コードを生成します。	英語以外の名前や発音のバリエーションを持つ単語

Soundex

Soundex は、似た発音の英単語を同じ 4 文字コードにエンコードすることで、正確なスペルではなく発音に基づいた文字列マッチングを可能にします。

関数：

soundex(text) returns text
difference(text, text) returns int

soundex(text) は、文字列の Soundex コード（例： A550）を返します。
difference(text, text) は、2 つの文字列の Soundex コードを比較し、一致する文字位置の数を返します。戻り値は 0（一致なし）から 4（完全に一致）の範囲です。「difference」という名前にもかかわらず、値が大きいほど類似度が高くなります。

例：

SELECT soundex('hello world!');
SELECT soundex('Anne'), soundex('Andrew'), difference('Anne', 'Andrew');
SELECT soundex('Anne'), soundex('Margaret'), difference('Anne', 'Margaret');

CREATE TABLE s (nm text);
INSERT INTO s VALUES ('john');
INSERT INTO s VALUES ('joan');
INSERT INTO s VALUES ('wobbly');
INSERT INTO s VALUES ('jack');

-- 完全な発音一致
SELECT * FROM s WHERE soundex(nm) = soundex('john');

-- 似た発音の名前（difference スコア > 2）
SELECT * FROM s WHERE difference(s.nm, 'john') > 2;

Levenshtein

Levenshtein 距離は、ある文字列を別の文字列に変換するために必要な最小の単一文字編集回数を測定します。距離が小さいほど類似度が高くなります。

たとえば、kitten を sitting に変換するには、以下の 3 回の編集が必要です。

k を s に置き換え：kitten → sitten
e を i に置き換え：sitten → sittin
末尾に g を挿入：sittin → sitting

結果として、Levenshtein 距離は 3 になります。

関数：

levenshtein(text source, text target, int ins_cost, int del_cost, int sub_cost) returns int
levenshtein(text source, text target) returns int
levenshtein_less_equal(text source, text target, int ins_cost, int del_cost, int sub_cost, int max_d) returns int
levenshtein_less_equal(text source, text target, int max_d) returns int

パラメーター：

パラメーター	説明
`source`	ソース文字列。空にすることはできません。最大 255 文字です。
`target`	ターゲット文字列。空にすることはできません。最大 255 文字です。
`ins_cost`	文字を挿入する際のコストです。
`del_cost`	文字を削除する際のコストです。
`sub_cost`	文字を置き換える際のコストです。
`max_d`	考慮する最大距離です。

levenshtein_less_equal は、短距離向けに最適化されたバージョンです。

実際の距離が max_d 以下の場合、正確な距離を返します。
実際の距離が max_d を超える場合、max_d より大きいランダムな値を返します。
max_d が負の値の場合、levenshtein と同一の動作をします。

例：

SELECT levenshtein('GUMBO', 'GAMBOL');
SELECT levenshtein('GUMBO', 'GAMBOL', 2, 1, 1);
SELECT levenshtein_less_equal('extensive', 'exhaustive', 2);
SELECT levenshtein_less_equal('extensive', 'exhaustive', 4);

エンドツーエンドの例 — あいまい名前検索：

CREATE TABLE customers (id INT, name TEXT);
INSERT INTO customers VALUES
  (1, 'John Smith'),
  (2, 'Jon Smythe'),
  (3, 'Jane Doe'),
  (4, 'Jonathan Smithson');

-- 'Jon Smithe' に近い名前を持つ顧客を検索（距離 <= 3）
SELECT * FROM customers
WHERE levenshtein(lower(name), lower('Jon Smithe')) <= 3;

Metaphone

Metaphone は、各英語文字列に対して発音コードを構築します。同じコードを持つ 2 つの文字列は、発音的に類似しているとみなされます。英単語に対しては Soundex よりも精度が高いです。

関数：

metaphone(text source, int max_output_length) returns text

パラメーター：

パラメーター	説明
`source`	ソース文字列。空にすることはできません。最大 255 文字です。
`max_output_length`	返される Metaphone コードの最大長です。コードがこの長さを超える場合、切り捨てられます。

例：

SELECT metaphone('GUMBO', 4);

Double Metaphone

Double Metaphone は、文字列に対して主コードと副コードの 2 種類の発音コードを生成します。ほとんどの場合、これらは同一ですが、発音が異なる英語以外の単語では異なることがあります。

関数：

dmetaphone(text source) returns text      -- 主コード
dmetaphone_alt(text source) returns text  -- 副コード

例：

SELECT dmetaphone('gumbo');
SELECT dmetaphone_alt('gumbo');