作者
digoal
日期
2017-05-24
标签
PostgreSQL , 文本相似 , pg_similarity , pg_trgm , rum , fuzzymatch gin , smlar
背景
文本相似算法，结合PostgreSQL的开放索引框架GIN，可以实现各种相似算法的文本高效检索。
PostgreSQL中常见的文本相似搜索插件：rum, pg_trgm, fuzzymatch, pg_similarity, smlar。
其中pg_similarity支持的算法达到了17种。
Introduction
pg_similarity is an extension to support similarity queries on PostgreSQL.
The implementation is tightly integrated in the RDBMS in the sense that it defines operators
so instead of the traditional operators (= and <>) you can use ~~~ and ~!~ (any of these
operators represents a similarity function).
pg_similarity has three main components:
Functions:
a set of functions that implements similarity algorithms available in the literature.
These functions can be used as UDFs and, will be the base for implementing the similarity operators;
Operators:
a set of operators defined at the top of similarity functions.
They use similarity functions to obtain the similarity threshold and,
compare its value to a user-defined threshold to decide if it is a match or not;
Session Variables:
a set of variables that store similarity function parameters. Theses variables can be defined at run time.
- L1 Distance (as known as City Block or Manhattan Distance);
- Cosine Distance;
- 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 Distance;
- Smith-Waterman Coefficient;
- Smith-Waterman-Gotoh Coefficient;
- Soundex Distance.
用法如下
| Algorithm | Function | Operator | Use Index? | Parameters |
|---|---|---|---|---|
| L1 Distance | block(text, text) returns float8 | ~++ | yes |
pg_similarity.block_tokenizer (enum) pg_similarity.block_threshold (float8) pg_similarity.block_is_normalized (bool) |
| Cosine Distance | cosine(text, text) returns float8 | ~## | yes |
pg_similarity.cosine_tokenizer (enum) pg_similarity.cosine_threshold (float8) pg_similarity.cosine_is_normalized (bool) |
| Dice Coefficient | dice(text, text) returns float8 | ~-~ | yes |
pg_similarity.dice_tokenizer (enum) pg_similarity.dice_threshold (float8) pg_similarity.dice_is_normalized (bool) |
| Euclidean Distance | euclidean(text, text) returns float8 | ~!! | yes |
pg_similarity.euclidean_tokenizer (enum) pg_similarity.euclidean_threshold (float8) pg_similarity.euclidean_is_normalized (bool) |
| Hamming Distance | hamming(bit varying, bit varying) returns float8 hamming_text(text, text) returns float8 |
~@~ | no |
pg_similarity.hamming_threshold (float8) pg_similarity.hamming_is_normalized (bool) |
| Jaccard Coefficient | jaccard(text, text) returns float8 | ~?? | yes |
pg_similarity.jaccard_tokenizer (enum) pg_similarity.jaccard_threshold (float8) pg_similarity.jaccard_is_normalized (bool) |
| Jaro Distance | jaro(text, text) returns float8 | ~%% | no |
pg_similarity.jaro_threshold (float8) pg_similarity.jaro_is_normalized (bool) |
| Jaro-Winkler Distance | jarowinkler(text, text) returns float8 | ~@@ | no |
pg_similarity.jarowinkler_threshold (float8) pg_similarity.jarowinkler_is_normalized (bool) |
| Levenshtein Distance | lev(text, text) returns float8 | ~== | no |
pg_similarity.levenshtein_threshold (float8) pg_similarity.levenshtein_is_normalized (bool) |
| Matching Coefficient | matchingcoefficient(text, text) returns float8 | ~^^ | yes |
pg_similarity.matching_tokenizer (enum) pg_similarity.matching_threshold (float8) pg_similarity.matching_is_normalized (bool) |
| Monge-Elkan Coefficient | mongeelkan(text, text) returns float8 | ~|| | no |
pg_similarity.mongeelkan_tokenizer (enum) pg_similarity.mongeelkan_threshold (float8) pg_similarity.mongeelkan_is_normalized (bool) |
| Needleman-Wunsch Coefficient | needlemanwunsch(text, text) returns float8 | ~#~ | no |
pg_similarity.nw_threshold (float8) pg_similarity.nw_is_normalized (bool) |
| Overlap Coefficient | overlapcoefficient(text, text) returns float8 | ~** | yes |
pg_similarity.overlap_tokenizer (enum) pg_similarity.overlap_threshold (float8) pg_similarity.overlap_is_normalized (bool) |
| Q-Gram Distance | qgram(text, text) returns float8 | ~~~ | yes |
pg_similarity.qgram_threshold (float8) pg_similarity.qgram_is_normalized (bool) |
| Smith-Waterman Coefficient | smithwaterman(text, text) returns float8 | ~=~ | no |
pg_similarity.sw_threshold (float8) pg_similarity.sw_is_normalized (bool) |
| Smith-Waterman-Gotoh Coefficient | smithwatermangotoh(text, text) returns float8 | ~!~ | no |
pg_similarity.swg_threshold (float8) pg_similarity.swg_is_normalized (bool) |
| Soundex Distance | soundex(text, text) returns float8 | ~*~ | no |
参考
http://pgsimilarity.projects.pgfoundry.org/
https://github.com/eulerto/pg_similarity
https://www.pgcon.org/2009/schedule/attachments/108_pg_similarity.pdf
http://www.sigaev.ru/git/gitweb.cgi?p=smlar.git;a=summary
https://github.com/postgrespro/rum
https://www.postgresql.org/docs/9.6/static/fuzzystrmatch.html
https://www.postgresql.org/docs/9.6/static/pgtrgm.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
