以下是关于 PostgreSQL 9.6 中的 contrib/pg_trgm 中的单词相似度的笔记。发行说明点击这里。

在contrib/pg_trgm中添加对“词语相似性”的支持（亚历山大·科罗特科夫，阿图尔·扎基罗夫）。

这些函数和操作符可以测量一个字符串与另一个字符串中最相似的单词之间的相似度。

首先，什么是相似度搜索？

这是一种搜索的方法，通过计算文档之间的相似度（即它们有多么相似），选择具有超过指定阈值的相似度的文本。

使用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”，所以两个字符串之间的相似度为0.2。
如果我们以比”like”更长的”PostgreSQL”来计算相似度，

postgres(1)=# select similarity('PostgreSQL', 'Hello, I like PostgreSQL');
 similarity
------------
       0.44
(1 row)

而且，从相邻的词组中可以看出，与“like”相比，“PostgreSQL”在字符串“Hello, I like PostgreSQL”中的闭合比例更大，因此相似度更高。

在相似度搜索中，它擅长搜索“两个词有多相似”（考虑拼写错误和写法变化等因素进行搜索），但对于搜索“特定文本是否包含某个字符串”并不适合。

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”计算还是以”like”计算结果都是相同的。

postgres(1)=# select word_similarity('PostgreSQL', 'Hello, I like PostgreSQL');
 word_similarity
-----------------
               1
(1 row)

这样，通过Word Similarity搜索，您可以进行类似性搜索，即在一段文字中搜索特定字符串是否可能存在。
另外，Word Similarity和Similarity搜索不区分大小写。

当真要使用的时候

SQL示例

在进行字符串相似度计算时，通常会使用similarity()和word_similarity()函数。实际上，在使用Word Similarity进行SQL编写时，可以使用操作符%>或<%，如下所示。

SELECT * FROM hoge WHERE col %> 'PostgreSQL';
SELECT * FROM hoge WHERE 'PostgreSQL' <% col;

另外，由于从PostgreSQL 9.6开始，相似度阈值的更改方法已经变为基于GUC，因此可以使用以下SQL语句来更改阈值。

SET pg_trgm.word_similarity_threshold to 1;

通过上述方法，只有相似度为1的字符串才能在词语相似度搜索中被选择。

使用示例

pg_trgm.word_similarity_threshold = 1 可以翻译为：

pg_trgm.词相似度阈值 = 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)