PostgreSQL9.6でcontrib/pg_trgmに入るword similarityについてのメモです。リリースノートはこちら。
- Add support for "word similarity" to contrib/pg_trgm (Alexander Korotkov, Artur Zakirov)
These functions and operators measure the similarity between one string and the most similar single word of another string.
そもそもSimilarity検索とは？
文書間の類似度(どれだけ似ているか)を算出して、指定した閾値以上の類似度を持った文章を選択するという検索です。
PostgreSQLのcontrib/pg_trgmを使うと利用できるsimilarity()関数では、3-gramをベースにして、「第一引数の文字列が第二引数の文字列とどれだけにているか」を0から1の小数で算出します。
postgres(1)=# select similarity('like', 'Hello, I like PostgreSQL');
similarity
------------
0.2
(1 row)
上記の例では、第二引数の「Hello, I like PostgreSQL」の中に第一引数の「like」が含まれているので、2つの文字列間の類似度は0.2となっています。
「like」より文字数の長い「PostgreSQL」で類似度を見てみると、
postgres(1)=# select similarity('PostgreSQL', 'Hello, I like PostgreSQL');
similarity
------------
0.44
(1 row)
となり、「like」よりも「PostgreSQL」の方が「Hello, I like PostgrSQL」の文字列に
閉める割合が大きいので、より類似度が高くなっていることが分かります。
Similarity検索では「ある2つの単語がどれだけ似ているか」の検索は得意です(誤字や表記の揺れ等を考慮して検索する、とかに向いてます)が、「ある文章の中に特定の文字列が含まれていそうか」という検索には向いていません。
ではWord Similarity検索とは？
Word Similarity検索ではこれまでのSimilarity検索とは異なり、単語のまとまりを意識して類似度を算出します。
postgres(1)=# select word_similarity('like', 'Hello, I like PostgreSQL');
word_similarity
-----------------
1
(1 row)
Word Similarity検索では、第一引数である「like」という単語をひとまとまりにして、第二引数の文字列と比較します。
「like」は「Hello, I like PostgreSQL」の中にあるので、類似度は1となります。
そのため、「PostgreSQL」で算出しても結果は同じです。
postgres(1)=# select word_similarity('PostgreSQL', 'Hello, I like PostgreSQL');
word_similarity
-----------------
1
(1 row)
このように、Word Similarity検索ではSimilarity検索で苦手だった「ある文章の中に特定の文字列が含まれていそうか」という検索をすることができます。
※ちなみにWord Similarity、Similarity検索では大文字、小文字を区別しません
実際に使うとき
SQL例
similarity()やword_similarity()は文字列間の主に類似度算出をテストするときに使われる関数です。
実際にWord Similarity検索を使ってSQLを書くときは演算子%>または<%を使って以下のようにします。
SELECT * FROM hoge WHERE col %> 'PostgreSQL';
SELECT * FROM hoge WHERE 'PostgreSQL' <% col;
また、PostgreSQL9.6から類似度の閾値の変更方法がGUCベースに変わったので、以下のSQLで閾値を変更します。
SET pg_trgm.word_similarity_threshold to 1;
上記のようにすることで、類似度が1の文字列のみがWord Similarity検索で選択できるようになります。
使い方例
pg_trgm.word_similarity_threshold = 1
このように設定した場合、類似度が1、つまり「検索対象の文字列中で検索キーワードが完全に含まれる行を選択する」という意味になります。
postgres(1)=# TABLE hoge;
col
-------------------
I like PostgerSQL
I like Postgres
I like postgres
I like postgresql
I like PostgreSQL
(5 rows)
postgres(1)=# SELECT * FROM hoge WHERE col %> 'PostgreSQL';
col
-------------------
I like postgresql
I like PostgreSQL
(2 rows)
つまり、これは中間一致検索と同じなので、あえてWord Similarity検索を使う必要はありません。
素直にILIKE演算子を使えばいい思います。
postgres(1)=# SELECT * FROM hoge WHERE col ILIKE '%PostgreSQL%';
col
-------------------
I like postgresql
I like PostgreSQL
(2 rows)
pg_trgm.word_similarity_threshold < 1
設定値を1以下にした場合、「検索対象の文字列中で検索キーワードが含まれていそうな行を選択する」ことができます。「含まれていそう」というのにどれくらいの幅を持たせるかは設定値次第です。
postgres(1)=# set pg_trgm.word_similarity_threshold to 0.8;
SET
postgres(1)=# SELECT * FROM hoge WHERE col %> 'PostgreSQL';
col
-------------------
I like postgresql
I like PostgreSQL
(2 rows)
postgres(1)=# set pg_trgm.word_similarity_threshold to 0.5;
SET
postgres(1)=# SELECT * FROM hoge WHERE col %> 'PostgreSQL';
col
-------------------
I like Postgres
I like postgres
I like postgresql
I like PostgreSQL
(4 rows)
postgres(1)=# set pg_trgm.word_similarity_threshold to 0.4;
SET
postgres(1)=# SELECT * FROM hoge WHERE col %> 'PostgreSQL';
col
-------------------
I like PostgerSQL
I like Postgres
I like postgres
I like postgresql
I like PostgreSQL
(5 rows)
日本語検索との相性
pg_trgmにあるWord Similarity検索では、単語同士がスペースで句切られていることを前提としているので、出来ないことはないですが少し日本語とは相性が悪いです。
postgres(1)=# select word_similarity('東京都', '東京都でオリンピック');
word_similarity
-----------------
0.75
(1 row)