Oracle，如何从表中删除几乎相同的行？

Question

多年来，由于拼写错误，我们的数据表中添加了一些重复项。举个例子，有人将名字拼错了，把O'leary写成了Oleary。我们的系统认为这是一个完全不同的名字，并且没有任何投诉，但在大多数情况下，这是输入两次相同联系人的结果（我没有建造这个系统）。

现在我想做的是删除所有这些重复项，但我很难编写一个查询来显示它们。我尝试使用UTL_MATCH进行实验，并编写了一个查询，如果我提供名称，它将返回所有类似的名称。

select first_name from customers 
where UTL_MATCH.edit_distance_similarity(first_name,'Oleary') > 60
order by first_name;

但我希望建立一个查询，可以自动返回所有可能的重复项，而不必提供名称。请问有人能指导我正确的方向吗？

- K.I

---

问题陈述中存在逻辑不一致。可能会有三个名字，称为fn1、fn2、fn3，其中fn1和fn3都与fn2“足够相似”，但彼此之间不“足够相似”。在这种情况下，您可以保留fn2并删除其他两个，或者保留fn1和fn3但删除中间的一个。然后fn3可能类似于fn4，但fn4与fn1不“足够”相似，等等。在您考虑任何解决方案之前，需要更明确地指定问题陈述。 - user5683823

很抱歉，mathguy，也许我们的智商差异阻碍了理解（我不明白你刚才说的话），但是我已尽力简单地解释我的问题，我看到下面有一些几乎回答了我的问题，所以似乎我并没有完全失败。谢谢你的回答。 - K.I

举例说明：同一个人的三个不同版本的名字，拼写不同（出于任何原因），但是相同的人。名字分别为JADA、JEDA或GEDA。“Similarly”表示有多少个共同字母。JADA到JEDA是75%（它们超过60%相似），JEDA和GEDA也是75%，但是JADA和GEDA只有50%相似。如果您使用查询并在distance_similarity中使用“JEDA”，那么两个其他名称都将被选中。但是，如果您使用“JADA”进行比较，“GEDA”将不会被选中。因此，“所有重复项”的概念并没有很好地定义。 - user5683823

按照同样的逻辑，如果应用“朋友的朋友是我的朋友”的原则，你可能会遇到这样的情况：ABC类似于DBC，DBC类似于DEC，DEC类似于DEF。这是否意味着ABC类似于DEF？ - user5683823

哈！现在我明白了 :) 谢谢！我认为如果有人实现了你的想法，下面提出的查询将变得更加高效，但也许这次并不需要，因为我可以完成工作，而且只需要做一次，所以速度并不那么重要。 - K.I

我按地点细分了我的查询，这很好，我将不得不运行它多次，但正如我所说 - 只运行一次。 - K.I

Answer 1

您可以使用此功能进行“join”操作：

select c1.first_name, c2.first_name
from customers c1 join
     customers c2
     on UTL_MATCH.edit_distance(c1.first_name, c2.first_name) <= 3
order by c1.first_name;

注意：

• 我更喜欢使用edit_distance()而不是edit_distance_similarity()，因为我能理解单位。
• 连接会很慢，非常慢，所以希望你的行数不要太多。
• 很可能会有很多错误匹配，所以要小心。

- Gordon Linoff

---

我只会在连接子句中添加 and c1.first_name < c2.first_name 以避免无用的重复（和精确匹配）。 - Raphaël Althaus

谢谢你的回答！我已经尝试了非常类似的方法，但在五分钟后不得不取消我的查询，因为它仍在运行。我觉得我的代码有问题。有没有办法提高它的速度？ - K.I

@K.I. . . . 从表格中选择一个小块，比如以“A”开头的名字，看看情况如何。 - Gordon Linoff

Answer 2

这样做从技术上讲是可行的。

select c1.first_name, c2.first_name
  from customers c1
       cross join customers c2
 where utl_match.edit_distance_similarity( c1.first_name, c2.first_name ) > 60
 order by c1.first_name

然而，除非您的“customers”表非常（非常）小，否则它将非常缓慢，因为您正在比较表中每一行与表中其他每一行（并且您的编辑距离相似性截止很低）。为了加快速度，您可能需要对数据进行假设或执行其他可作为初步筛选的操作。例如，如果您假设任何重复项都以相同的第一个字符或相同的前几个字符开头（忽略标点符号），那么您可以显着减少需要匹配的成对数量，但有可能会错过“Kustin”是“Justin”打字错误的副本的事实，其中第一个字符不同。要求 c1.customer_id>也将是另一个合理的筛选条件，假设您不需要每个成对重复（即，“Kustin / Justin”行可以存在而没有等效的“Justin / Kustin”行）。

- Justin Cave

---

谢谢你的回答！我尝试了一个非常类似的方法，但在5分钟后不得不取消我的查询，它仍在运行，我认为我的代码有问题。有什么办法可以提高它的速度吗？ - K.I

@K.I - 就像我说的那样，对于每个表中的每一行与表中的每一行进行比较会非常慢。您需要想出一些合理的方法来限制您需要考虑的组合，这些方法基于您对数据的某些了解。我提到了一些可能或不可能对您有用的可能性。不幸的是，很难猜测什么可能是您的数据的合理启发式。也许您的客户有地址信息，您只能比较同一邮政编码下的客户，例如。或者您可以让它在夜间运行数小时。 - Justin Cave