DuckDB 字符串相似性计算

文章开始前推荐2个学习环境: 

1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》

2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》

3、PolarDB开源数据库内核、应用等学习图谱:  https://www.aliyun.com/database/openpolardb/activity 

https://duckdb.org/docs/sql/functions/char

目前DuckDB支持几个字符串相似性计算的函数, 如下:

levenshtein(string, string)  
editdist3(string, string)  
大小写敏感, 从一个字符串变成另一个字符串至少需要修改几个字符. 多字节字符的话, 差异是计算多少个字节 (single-character);  
levenshtein('duck','db')	3  
editdist3('duck','db')	  3

mismatches(string, string)  
hamming(string, string)  
大小写敏感, 字符串的实际存储长度必须相同. 按位置逐个比较, 返回两个字符串的差异个数.   
hamming('duck','luck')	1  
mismatches('duck','luck')	1

jaccard(string, string)	  
The Jaccard similarity between two strings. Different case is considered different. Returns a number between 0 and 1.	  
大小写敏感, 返回结果范围0~1;   
jaccard('duck','luck')	0.6  
算法参考:  
https://github.com/eulerto/pg_similarity/blob/master/jaccard.c

jaro_similarity(string, string)	  
The Jaro similarity between two strings. Different case is considered different. Returns a number between 0 and 1.	  
大小写敏感, 返回结果范围0~1;   
jaro_similarity('duck','duckdb')	0.88  
算法参考:  
https://github.com/eulerto/pg_similarity/blob/master/jaro.c

jaro_winkler_similarity(string, string)	  
大小写敏感, 返回结果范围0~1;   
The Jaro-Winkler similarity between two strings. Different case is considered different. Returns a number between 0 and 1.	  
jaro_winkler_similarity('duck','duckdb')	0.93  
算法参考:  
https://github.com/eulerto/pg_similarity/blob/master/jaro.c

PostgreSQL的文本相似算法支持比duckdb更丰富, 配置也更丰富. 例如jaccard支持多种tokenizer(word,gram,case,num) 

https://github.com/eulerto/pg_similarity

《17种文本相似算法与GIN索引 - pg_similarity》

《PostgreSQL 相似搜索插件介绍大汇总 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》

如果你有文本分析较为复杂的诉求, 建议还是选择PostgreSQL/PolarDB for PG

欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.  
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.