MySQL - 去重和保留有价值数据？

Question

场景：我有一张表格里面有几个重复的联系人。这些重复的联系人已经被识别出来，我可以直接删除它们，但问题是我不想丢失重复项可能拥有而原始项没有的数据。有什么建议吗？

样本数据：

ID Name Email School Dupe_Flag Key
1  AAA  a@a          X         1 
2  AAB        JKL              1
3  BBB  b@b   MNO    X         2
4  BBC                         2

期望的输出：

ID Name Email School Dupe_Flag Key
1  AAA  a@a          X         1 
2  AAB  a@a   JKL              1
3  BBB  b@b   MNO    X         2
4  BBC  b@b   MNO              2

2条记录之间有什么关系？ : 它们都具有相同的键值，但只有一个列设置了Dupe_Flag，即重复的列。

在上述情况中，ID 1将被删除，但是ID 1的电子邮件信息应该应用于ID 2。

什么是数据？ ：我有几百行和几百个重复项。每行的UPDATE语句很繁琐，不可行。

确定哪些数据优先的业务规则:

如果原始/好记录（Dupe_Flag未设置）的某个列没有数据，并且相应的Dupe记录（具有相同的键值）列具有数据，则应更新该原始记录列。

非常感谢任何帮助/脚本！谢谢大家：）

- ThinkCode

---

我们如何看到您认为是重复的行？我们如何知道在重复项中，哪些列比您想保留的行中对应的列更有价值？ - 没有规范，您无法自动执行操作。 - Roland Bouman

首先，您需要定义自己的业务规则来确定在冲突事件中哪些数据具有优先权。 - Dolph

Dupe_Flag表示这是一个重复记录。哪个数据优先级更高？- 简单明了。如果原始（好的）记录的任何列为空，并且在重复记录中相同的列都不为空，那么我们只需更新这些列。为了清晰起见，我更新了我的问题。谢谢！ - ThinkCode

Answer 1

假设空值为null，像这样输出所需数据：

SELECT
  a.ID,
  IF(a.DupeFlag IS NULL, IF(a.Name IS NULL, b.Name, a.Name), a.Name) AS Name,
  IF(a.DupeFlag IS NULL, IF(a.Email IS NULL, b.Email, a.Email), a.Email) AS Email,
  IF(a.DupeFlag IS NULL, IF(a.School IS NULL, b.School, a.School), a.School) as School,
  a.DupeFlag,
  a.key
FROM
  table a,
  table b
WHERE
  a.Key = b.Key AND
  a.ID != b.ID
GROUP BY
  a.ID

请注意，将此转换为UPDATE语句非常简单。

- Benoît Vidis

---

非常感谢！我的问题仍然存在，因为这只是样本数据，在现实中我有数百列，所以对每一列进行UPDATE语句将行不通 :(再次感谢！ - ThinkCode

Answer 2

我不知道这个问题的具体情况，但最好通过将列设置为“unique”来避免这个问题，这样如果查询尝试创建重复项，它将失败。我认为解决此问题的优雅方法是在数据输入点避免它。

我喜欢使用这个查询来查找重复项：

select * from table group by `Email` having count(Email) > 1

- rook

---

这只是样本数据。我现在已经有了键，只是有些联系人的名字不同，因此我们无法通过程序手段找到重复项。只有手动审核才能确定，这就是问题所在 :(顺便说一下，我将电子邮件设置为唯一值，样本数据仅用于说明目的。谢谢。 - ThinkCode

只有手动审查才能告诉您（或允许您猜测）哪些数据更正确。对于不同的中间名，哪个是正确的？程序无法知道。并且有时候，至少在您的示例中，即使看起来像是重复，它实际上是一个单独的记录（两个名字相同的人）。 - thursdaysgeek

相信我，伙计们，我们已经进行了重复检测。我想知道的只是拯救与重复记录相关的数据的最简单/高效的方法。 - ThinkCode

Answer 3

虽然这个方法使用了一堆嵌套的SELECT语句，而且并不是完整的解决方案，但它应该会激发出其他的想法，或者可能让你朝着正确的方向前进。

select * from 
  (select r1.ID,r1.Name,coalesce(r1.Email,r2.Email) as Email,
  coalesce(r1.School,r2.School) as School,r1.Dupe_Flag,r1.Key from 
  (select * from test1 where Dupe_Flag IS NULL) as r1  left outer join 
  (select * from test1 where Dupe_Flag IS NOT NULL) as r2 on r1.KEY=r2.Key) 
as results

返回：

ID  Name  Email  School  Dupe_Flag  Key
2   AAB   a@a    JKL     NULL       1
4   BBC   b@b    MNO     NULL       2

根据您提供的示例数据。

- furrymitn

---

谢谢！实际上我正在尝试消除每个列的查询需求，因为真实数据有很多列。到目前为止，一个理想的解决方案是使用PYTHON脚本将所有列存储在数组中，并检查任何有价值的数据并更新原始列。 - ThinkCode

Benoit Vidis提供的以下解决方案正好符合您的要求。 - furrymitn

Answer 4

这些行是唯一的，所以没有问题。请重新检查您的示例数据。

- pete