sql-handling-duplicates

SQL - 处理重复数据

数据库中的重复数据可能导致不正确的报告、不一致的状态以及其他严重问题。虽然适当的数据库范式（normalization）和约束（UNIQUE、PRIMARY KEY）是防止重复数据的最佳方法，但你通常需要从现有数据中查找并删除重复项。

方法 1：使用 GROUP BY 查找重复项

查找哪些值是重复的一个简单方法是使用 GROUP BY 和 HAVING。此方法有助于你识别问题，但不会给出要删除的具体行。

假设有一个 Users 表，其中 Email 列应该是唯一的，但实际上并非如此。

SELECT
    Email,
    COUNT(Email) AS Occurrences
FROM Users
GROUP BY Email
HAVING COUNT(Email) > 1;

此查询将返回所有出现多次的电子邮件列表以及它们出现的次数。

方法 2：使用 DISTINCT 选取唯一行

DISTINCT 关键字用于从查询结果集中仅检索唯一行。这对于报告很有用，但不会从源表中删除重复项。

SELECT DISTINCT Email, Name FROM Users;

清理表的常见策略是将唯一行选择到一个新的临时表中，截断原始表，然后将干净的数据重新插入。

方法 3：使用窗口函数删除重复项（高级）

删除重复项并保留一行记录的最强大和最常用方法是使用窗口函数，特别是 ROW_NUMBER()。

策略是：

1. 为每条记录分配一个行号，根据定义重复项的列（例如 Email）进行分区。
2. 在每个分区内排序，以决定保留哪一行（例如，具有最新 CreatedAt 日期或最小 ID 的那一行）。
3. 删除所有行号大于 1 的行。

示例

让我们根据 Email 删除重复用户，保留 ID 最小的那个。

-- 此语法适用于 PostgreSQL 和 SQL Server

WITH NumberedUsers AS (
    SELECT
        ID,
        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID ASC) as rn
    FROM Users
)
DELETE FROM Users
WHERE ID IN (
    SELECT ID FROM NumberedUsers WHERE rn > 1
);

在此查询中：

• PARTITION BY Email 子句按相同的电子邮件对行进行分组。
• ORDER BY ID ASC 确保在每个组中，ID 最小的行获得 rn = 1。
• 外部的 DELETE 语句随后删除所有行号（rn）大于 1 的行，从而有效地删除了每个电子邮件的除第一次出现以外的所有重复项。

预防是最好的治疗

清理完重复项后，防止它们再次发生至关重要。为相关列添加 UNIQUE 约束。

ALTER TABLE Users
ADD CONSTRAINT UQ_Users_Email UNIQUE (Email);