PostgreSQL模糊匹配

Question

我有两个表，包含以下字段：

• 名字
• 姓
• 中间名
• 状态
• Zip
• SSN
• 道布
• 电话

我试图找到两个表之间的匹配记录和最有可能匹配的记录，但由于输入错误、数据丢失、名称拼写的变化等原因，这些记录不完全匹配。

一些数据丢失了。但是对于所有存在的数据，两个表对于每个数据元素都具有相同的格式/数据类型。

理想情况下，我希望为结果建立某种加权机制。

如果SSN是一个直接匹配，那么我们就有一个匹配。但我也想考虑一下，如果有一个用户输入错误和2位数字混淆或类似的情况。

我在皮卡上有什么选择？

如果我运行多个变体(示例)，直线匹配就可以了。

• 社会匹配
• 姓道布，Zip
• 姓道布，国家
• 姓道布

然而，我希望部署一个更完整的解决方案，并正在寻找关于如何继续进行的任何技巧。

Answer 1

这叫做概率记录链接 (实际上它有几个名字)。

您要做的第一件事是标准化每一列的值，以便它们是直接可比较的。例如，日期应采用ISO格式并进行裁剪。

简单的方法

计算匹配列的数量：

select
 n.id as needle_id,
 h.id as haystack_id,
 case when n.col1 = h.col1 then 1 else 0 end 
 + case when some_comparison_function(n.col2, h.col2) then 1 else 0 end
 + ...
 as relevance
from 
 needles n
join 
 haystack h -- haystack table could be the same as needles table
on  -- only compare rows where at least one column matches
 n.col1 = h.col1 
 or some_comparison_function(n.col2, h.col2)
 or ...
order by 
 relevance desc;

更难但更正确的方法

这在数学上是证明最优的。它根据稀有值计算列的权重。

1. 选择两个应该相等但不同的值的概率。例如，两个记录应该具有相同的SSN，但是有一个错误。减去这个值的是您的m-prob (称它为99%)。
2. 对于每一列，计算每个值的相对频率。这是你的u-prob
3. 对于每一个潜在的匹配(needle.dob vs haystack.dob)，如果他们同意：m-prob / u-prob，则计算优势比；如果他们不同意，则计算优势比：(1 - m-prob) / (1 - u-prob)。
4. 乘以所有赔率比，得到总赔率。
5. 计算匹配概率：total_odds / (1 + total_odds)
6. 如果概率超过阈值，则匹配，否则不匹配

Answer 2

我认为fuzzystrmatch和/或pg_trgm模块是您要寻找的。