mysql - 查找和处理重复用户

Question

在具有以下格式和示例数据的大型用户数据库中，我们试图识别重复的人:

id   first_name    last_name   email
---------------------------------------------------
 1   chris         baker       
 2   chris         baker       chris@gmail.com
 3   chris         baker       chris@hotmail.com
 4   chris         baker       crayzyguy@crazy.com  
 5   carl          castle      castle@npr.org
 6   mike          rotch       fakeuser@sample.com  

我正在使用以下查询:

SELECT 
    GROUP_CONCAT(id) AS "ids",
    CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
    COUNT(*) AS "duplicate_count" 
FROM 
    users 
GROUP BY 
    name 
HAVING 
    duplicate_count > 1

这很好用；我得到一个重复列表，其中包含相关行的 ID 号。

我们会将与重复项相关的任何关联数据重新分配给实际人员(set user_id = 2 where user_id = 3)，然后删除重复的用户行。

问题出现在我们第一次制作此报告后，因为我们在手动验证它们确实是重复的之后清理列表 - 有些不是重复的。有 2 名 Chris Bakers 是合法用户。

我们不希望在随后的重复报告中一直看到 Chris Baker，直到时间结束，所以我正在寻找一种方法来标记用户 id 1 和用户 id 4 不是彼此重复的，以供将来的报告使用，但它们可能被以后添加的新用户复制。

我尝试了什么

我在用户表中添加了一个 is_not_duplicate 字段，但是如果将新的重复“Chris Baker”添加到数据库中，则会导致这种情况不会显示在重复报告中； is_not_duplicate 不正确地排除了其中一个帐户。我的 HAVING 语句不会满足 > 1 阈值，直到有 -two- Chris Baker 的重复，加上标记为 is_not_duplicate 的“真实”一个.

问题总结

如何在不循环结果或多个查询的情况下将异常构建到上述查询中？

子查询很好，但数据集的大小使每个查询都重要，我希望解决方案尽可能地高效。

Answer 1

尝试添加 is_not_duplicate bool 字段并修改您的代码如下:

SELECT 
    GROUP_CONCAT(id) AS "ids",
    CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
    COUNT(*) AS "duplicate_count",
    SUM(is_not_duplicate) AS "real_count"
FROM 
    users 
GROUP BY 
    name 
HAVING 
    duplicate_count > 1
AND
    duplicate_count - real_count > 0

新添加的重复项将具有 is_not_duplicate=0 因此该名称的 real_count 将小于 duplicate_count 并且将显示该行