大数组比较
我在 SQL 数据库中有大约 25.000 个不同的名称，并且希望对所有这些名称执行编辑距离比较，以便标准化，例如 John Doe & 。约翰·多伊.
当数据库只有大约 1000 个名称时，我曾经将所有不同的名称存储在一个数组中。然后我会在该数组上使用两个 for 循环，从而将数组中的每个元素与其他元素进行比较。当编辑距离给出大于 0.9 的匹配时，我将执行 SQL 查询，在所有记录中用一个值替换另一个值。
对于我更大的数据库，这是不可能的了。你们会怎么做？
ps：我也对任何多线程解决方案感到好奇，因为这个过程现在需要很长时间。
pps：我正在用 Java 编码
I have ~25.000 distinct names in an SQL database, and would like to perform edit-distance comparison on all of these in order to normalize e.g. John Doe & Jhon Doe.
When the db was only around 1000 names I used to store all distinct names in an array. Then I would use two for-loops on that array, thereby comparing each element in the array to each of the others. When the edit-distance gave a match of say >0.9 I would execute an SQL-query substituting one value for the other in all records.
With my much larger database this is not possible anymore. What would you guys do?
ps: I'm also curious about any multithreaded solutions to this because the process is taking ages now.
pps: I'm coding in Java
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
计算每个名字的 soundex 并将其存储在数据库中怎么样？您甚至可以在数据库端执行此操作，例如有 a MySQL SOUNDEX 函数。
计算每个名称的 soundex 后，您所要做的就是按相同的 soundex 对行进行分组。
编辑：
如果 soundex 对于您的应用程序来说太粗糙，您可以首先通过比较它们的 soundex 来选择候选者，并对每组候选者使用您常用的指标。
What about computing the soundex of each of your names and possibly storing it in the database? You can even do that on DB side, for instance there's a MySQL SOUNDEX function.
After computing the soundex of each name, all you have to do is group the rows by identical soundex.
EDIT:
If soundex is too coarse for your application, you can first select candidates by comparing their soundexes, and use your usual metric on each set of candidates.
没有办法绕过成对匹配：尽可能有效的方式。
如果您需要更快地进行记录链接，请尝试使用比编辑距离需要更少计算量的字符串距离度量（博纳奇距离，Jaro–Winkler距离等）
您还可以使用另一个指标作为预处理步骤，然后计算编辑距离来确认或拒绝匹配。
There is no way around pairwise matching: the way as efficient as it gets.
If you need to do your record linkage faster, try using a string distance metrics that requires less computations than the edit distance (Bonacci distance, Jaro–Winkler distance, etc.)
You could also use another metric as a preprocessing step, and then compute edit distance to confirm or deny the match.