PostgreSQL similarity()函数得分异常，恳请技术解释原因

PostgreSQL 的 similarity() 函数（来自 pg_trgm 扩展）和 Snowflake 的 JAROWINKLER_SIMILARITY 用的是完全不同的相似度计算逻辑，这是两者得分差异巨大的核心原因：

1. PostgreSQL similarity() 的三元组算法原理 #

similarity() 基于三元组（trigram）匹配，步骤如下：

• 对每个字符串预处理：在开头加两个空格、结尾加一个空格，然后拆分出所有连续的3字符组合（即三元组）。
• 相似度计算公式：匹配的三元组数量 / 两个字符串的三元组并集数量

逐个分析你的测试案例： #

案例1：similarity('GTudH', 'tud') = 0.1

• GTudH 生成的三元组：' G', ' GT', 'GTu', 'Tud', 'udH', 'dH '（共6个）
• tud 生成的三元组：' t', ' tu', 'tud', 'ud '（共4个）
• 大小写不敏感匹配下，只有 'Tud' 和 'tud' 是共同三元组，匹配数为1
• 三元组并集数量：6 + 4 - 1 = 9
• 最终得分：1 / 9 ≈ 0.1，和你得到的结果一致。

案例3：similarity('GTudH', 'gdh') = 0.25

• gdh 生成的三元组：' g', ' gd', 'gdh', 'dh '（共4个）
• 大小写不敏感匹配下，共同三元组是 ' G'（对应 ' g'）、'dH '（对应 'dh '），共2个
• 三元组并集数量：6 + 4 - 2 = 8
• 最终得分：2 / 8 = 0.25，和结果完全吻合。

2. Snowflake JAROWINKLER_SIMILARITY 的逻辑 #

Jaro-Winkler 算法是基于字符顺序匹配的相似度算法，它会重点关注：

• 两个字符串中匹配字符的数量与位置
• 连续匹配的子串长度（会给开头连续匹配的子串额外加权）
  所以对于你的测试：
• GTUDH 和 TUD：TUD 是中间连续匹配的子串，得分86
• GTUDH 和 GTU：GTU 是开头连续匹配的子串，得分90
• GTUDH 和 GDH：只有首尾字符匹配，得分51
  这符合我们直觉上的相似度判断。

3. 如何在PostgreSQL中得到类似Snowflake的结果 #

如果需要在PostgreSQL中实现Jaro-Winkler相似度，可以：

• 使用第三方扩展 pg_jaro（提供Jaro和Jaro-Winkler算法实现）
• 自行编写函数实现Jaro-Winkler逻辑
• 若仍想使用 pg_trgm，可以结合 % 模糊匹配操作符（它基于三元组匹配，能识别子串相似性），而不是仅依赖 similarity() 的得分。

内容的提问来源于stack exchange，提问作者SumanP89