php - 在一些不完整的数据中查找单个用户记录？

Question

我正在构建一个用户必须注册的系统。这些用户也可能是更大客户数据库的一部分，我想将注册与更大客户数据库中的用户 ID 联系起来。

客户数据库有些不完整。有些客户只有一个电话号码，它可能在不同的地方有空格，这取决于输入它的人/内容。其他客户只有一个电子邮件地址，并且由于它是手写的，然后由其他人在以后处理，所以很可能有拼写错误。真是一场噩梦。

我想找到与用户在我正在构建的系统上输入的内容最接近的记录。该数据非常简单明了，将得到验证。该数据:

• 名字
• 姓氏
• 出生日期 (YYYY-MM-DD)
• 电子邮件地址
• 电话号码

我最初的想法是使用 Levenshtein 距离算法计算每个字段的“字符串距离”，除非它们为空，然后按总分排序。下面的代码中没有显示，以保持美观和可读性，但我显然也会修剪(甚至可能只是删除)所有空格。

作为伪代码:

SELECT c.customerID
FROM   customers c
WHERE  ( c.first_name IS NULL OR ( Levenshtein(c.first_name, $first_name) < 3 ) )
AND    ( c.last_name IS NULL OR ( Levenshtein(c.last_name, $last_name) < 3 ) )
AND    ( c.email IS NULL OR ( Levenshtein(c.email, $email) < 3 ) )
AND    ( c.telephone IS NULL OR ( Levenshtein(c.telephone, $telephone) < 3 ) )

仅供引用，我正在为这两个数据库使用 PHP (Laravel) 和 MySQL。

我是在正确的轨道上还是应该使用 Levenshtein 以外的东西？我应该比较所有领域分数的某种组合吗？

Answer 1

轨道绝对正确，但我要添加一些注释。

准备数据

首先，为了匹配，我建议转换数据以消除可能的所有噪声，f.i.将字符串转换为大写、删除空格、删除电话号码中的所有非数字等。

在不完整的数据中寻找最接近的匹配项

其次，设置任意阈值(如上面的“小于 3”)会使它有点僵硬。尽管对 CPU 的要求更高，但您最好按“差异因子”对结果进行排序:

SELECT c.customerID
FROM   customers c
ORDER BY
Levenshtein(c.first_name, $first_name)) +
Levenshtein(c.last_name, $last_name) +
Levenshtein(c.email, $email) +
Levenshtein(c.telephone, $telephone) asc
LIMIT 0,1;

显然，当差异高得离谱时，您可以添加一些不匹配的安全措施，但您明白了。如果两个对象都缺少同一字段中的数据(例如，都缺少电子邮件)，该方法仍然可以。当只缺少一侧时就会出现问题 - 然后我们会因为差异而受到很大的打击。我们可能会将查询进一步复杂化以避免它:

ORDER BY
(if(c.first_name is null OR c.first_name = '' OR $first_name = '', 0, Levenshtein(c.first_name, $first_name))) +
...

为简洁起见缩短为一行 - 我们仅在有数据要比较时才计算 Lev 距离。

缺点

对于所有匹配差异因子大于 X 的记录，您可能会想到某种标志来让人类决定。在花了一些时间回顾之后，我相信您会想出更多的规则来自动化它。