5154

在数据库管理和数据分析中，识别和处理重复数据是一项至关重要的任务，重复数据不仅会占用额外的存储空间，还可能导致数据分析结果不准确、报表逻辑混乱，甚至影响业务决策的正确性，掌握在数据库查询中高效地识别重复数据的方法，是每一位数据库开发者和分析师必备的技能，本文将系统地介绍几种主流且实用的方法,帮助您在不同场景下准确地判断数据是否存在重复。

什么是数据重复？

在开始查询之前，我们首先需要明确“重复”的定义,数据重复可以分为两种情况：

1. 完全重复行：表中的两条或多条记录在所有列上的值都完全相同。
2. 部分列重复：记录在一个或多个关键列（如用户邮箱、身份证号、订单号）上的值相同，但其他列可能不同，这种情况更为常见，一个用户可能注册了多个账户,但其身份证号是唯一的。

根据业务需求,我们需要针对不同类型的重复数据采取不同的查询策略。

使用 GROUP BY 和 HAVING 子句

这是最基础、最经典也是最容易理解的查找重复数据的方法，其核心思想是：按照可能重复的列进行分组，然后统计每个分组的记录数量，如果数量大于1,则说明该列的值存在重复。

场景：查找 users 表中重复的电子邮箱。

假设我们有如下 users 表结构：

查询语句：

SELECT
    email,
    COUNT(*) AS duplicate_count
FROM
    users
GROUP BY
    email
HAVING
    COUNT(*) > 1;

查询结果：

解析：

• GROUP BY email：将所有记录按照 email 列的值进行分组。
• COUNT(*)：计算每个分组中的记录数量。
• HAVING COUNT(*) > 1：筛选出记录数量大于1的分组，即存在重复的 email。

优点：

• 语法简单,直观易懂。
• 对于大数据集，通常有较好的性能，因为数据库引擎对 GROUP BY 做了深度优化。

扩展： 如果你想查看哪些是完全重复的行，可以在 GROUP BY 子句中列出所有列：

SELECT
    id, name, email, COUNT(*)
FROM
    users
GROUP BY
    id, name, email
HAVING
    COUNT(*) > 1;

使用窗口函数

窗口函数（Window Functions）提供了比 GROUP BY 更为强大和灵活的分析能力，它可以在不改变原有行数的情况下，对数据进行分组和计算。COUNT() 配合 OVER() 子句是查找重复数据的利器。

场景：不仅要知道哪些邮箱重复，还想直接看到所有重复的行数据,并给它们打上标记。

查询语句：

WITH RankedUsers AS (
    SELECT
        id,
        name,
        email,
        COUNT(*) OVER (PARTITION BY email) AS duplicate_count
    FROM
        users
)
SELECT
    id,
    name,
    email
FROM
    RankedUsers
WHERE
    duplicate_count > 1
ORDER BY
    email;

查询结果：

解析：

• PARTITION BY email：类似于 GROUP BY email，它将数据按 email 分区（分组）,但不会合并行。
• COUNT(*) OVER (...)：对每个 email 分区内的行进行计数，并将这个计数值作为新列 duplicate_count 添加到每一行。
• WITH RankedUsers AS (...)：使用公共表表达式（CTE）来创建一个临时的结果集,使查询更清晰。
• WHERE duplicate_count > 1：在外部查询中,轻松筛选出所有属于重复分组的原始行。

优点：

• 保留了原始行的所有信息,方便进行后续的查看或处理。
• 非常灵活，可以结合 ROW_NUMBER(), RANK() 等其他窗口函数实现更复杂的去重逻辑。

方法对比与选择

为了更清晰地帮助您选择,下表对比了这两种主要方法：

如何处理重复数据？

找到重复数据后，下一步通常是处理它们，最常见的操作是“保留一条，删除其余”,窗口函数在此场景下同样是最佳选择。

场景：保留 id 最大的那条记录，删除 users 表中 email 重复的其他记录。

操作步骤（强烈建议先 SELECT 预览，再 DELETE）：

1. 预览将要删除的数据：

WITH UsersToDelete AS (
    SELECT
        id,
        ROW_NUMBER() OVER(PARTITION BY email ORDER BY id DESC) AS rn
    FROM
        users
)
SELECT * FROM UsersToDelete WHERE rn > 1;

这里 ROW_NUMBER() 为每个 email 分区内的行按 id 降序编号，rn > 1 的就是我们打算删除的记录。

2. 执行删除操作：

确认预览结果无误后，将 SELECT * 改为 DELETE。

WITH UsersToDelete AS (
    SELECT
        id,
        ROW_NUMBER() OVER(PARTITION BY email ORDER BY id DESC) AS rn
    FROM
        users
)
DELETE FROM UsersToDelete WHERE rn > 1;

相关问答FAQs

在处理海量数据时，GROUP BY 和窗口函数哪个性能更好？

答：这是一个复杂的问题，没有绝对的答案，性能取决于多个因素，包括数据库类型（MySQL, PostgreSQL, SQL Server等）、表结构、索引以及数据的具体分布。

• 对于纯粹的“找出哪些值重复”这类聚合统计，GROUP BY 的性能通常非常稳定且高效,因为数据库优化器对其有成熟的优化策略。
• 当你需要查看重复行的全部数据，或者执行基于分区的复杂操作（如去重），窗口函数通常更优，因为它可能只需要对表进行一次扫描，而 GROUP BY 后再 JOIN 可能需要多次扫描。 在实际应用中，最好的方法是在你的具体环境中对两种方法进行测试和比较（使用 EXPLAIN 或 EXPLAIN ANALYZE 命令分析执行计划）,以做出最优选择。

如果一张表没有主键或唯一ID，如何删除完全重复的行？

答：这是一个棘手但常见的问题，因为没有唯一标识符，数据库无法精确地定位要删除的“某一行”，直接使用 DELETE 是危险的,最安全通用的方法是：

1. 创建一个临时表,结构与原表相同。
2. 将原表中的不重复数据插入到临时表，可以使用 SELECT DISTINCT * FROM original_table; 或者更精细的窗口函数方法来确保只插入唯一的行。
3. 删除原表（或重命名原表作为备份）。
4. 将临时表重命名为原表名。 这种方法虽然步骤较多，但操作安全，适用于几乎所有数据库系统，对于特定数据库，如 PostgreSQL，可以利用其隐藏的系统列 ctid 来定位和删除重复行,但这会降低代码的可移植性。