建设大型网站中国互联网数据平台
在实际业务中，数据去重是一个非常常见的需求，特别是在日志数据、用户操作记录或交易记录等领域。去重不仅仅是删除重复数据，更重要的是按照业务规则保留最有价值的数据记录。
本文将探讨如何在 SQL 中高效地处理重复数据，通过 DISTINCT、ROW_NUMBER() 和 GROUP BY 等方法解决不同场景下的数据去重问题。
一、常见的去重场景
- 用户登录记录：保留每个用户的最新登录记录，删除其他重复记录。
- 订单数据：针对重复订单记录，保留金额最大的订单或最新的订单记录。
- 日志分析：在日志表中去除重复的操作记录，只保留最近一次操作。
二、数据示例
假设有一个用户登录记录表 user_logins，其结构如下：
| id | user_id | login_time | device |
|---|---|---|---|
| 1 | 101 | 2024-01-01 08:30:00 | PC |
| 2 | 101 | 2024-01-02 10:00:00 | Mobile |
| 3 | 102 | 2024-01-01 09:15:00 | PC |
| 4 | 103 | 2024-01-01 14:45:00 | Tablet |
| 5 | 101 | 2024-01-03 11:00:00 | Mobile |
| 6 | 102 | 2024-01-04 16:30:00 | Mobile |
目标：
- 保留每个用户最近一次的登录记录，删除其他重复数据。
三、常用去重方法
1. 使用 DISTINCT 进行简单去重
DISTINCT 用于去除表中完全重复的行，适用于整个记录重复的场景。
SELECT DISTINCT user_id, device
FROM user_logins;
适用场景：
- 适合字段完全相同的简单重复数据。
- 局限性：
DISTINCT只能消除完全重复的行，如果存在时间戳不同但用户相同的记录，将无法处理。
2. 使用 GROUP BY 与聚合函数
通过 GROUP BY 分组和 MAX 函数，可以保留每组中的最新记录。
SELECT user_id, MAX(login_time) AS latest_login
FROM user_logins
GROUP BY user_id;
适用场景：
- 按用户分组，保留每个用户最新的登录时间。
- 局限性：只能返回最大（最新）时间，无法保留完整记录中的其他字段（如设备类型）。
优化版本：使用子查询保留完整记录
SELECT *
FROM user_logins u
WHERE login_time = (SELECT MAX(login_time)FROM user_loginsWHERE user_id = u.user_id
);
- 说明：子查询筛选出每个用户最新的登录时间，再通过主查询返回完整记录。
3. 使用 ROW_NUMBER() 进行去重
ROW_NUMBER() 是 SQL 窗口函数，可以为每组记录生成唯一的序号，从而方便地保留最新或特定排名的记录。
WITH ranked_logins AS (SELECT *,ROW_NUMBER() OVER (PARTITION BY user_idORDER BY login_time DESC) AS rnFROM user_logins
)
SELECT id, user_id, login_time, device
FROM ranked_logins
WHERE rn = 1;
适用场景：
- 适合去除分组内的重复记录，保留每组中最新的一条记录。
- 优势：可以返回完整的记录而不丢失其他字段。
- 局限性：需要数据库支持窗口函数（如 MySQL 8.0+、PostgreSQL、SQL Server）。
4. 使用 DELETE 删除重复数据
如果要直接在表中删除重复记录，可以使用 ROW_NUMBER() 标记重复数据，然后删除排名大于 1 的行。
WITH ranked_logins AS (SELECT id,ROW_NUMBER() OVER (PARTITION BY user_idORDER BY login_time DESC) AS rnFROM user_logins
)
DELETE FROM user_logins
WHERE id IN (SELECT id FROM ranked_logins WHERE rn > 1
);
- 说明：只保留每个用户最新的一条登录记录，删除其余记录。
四、性能对比与优化策略
性能对比
| 方法 | 执行速度 | 复杂度 | 适用场景 |
|---|---|---|---|
DISTINCT | 快 | 简单 | 去除完全重复的行 |
GROUP BY + MAX | 中 | 一般 | 按分组保留最新或最早的记录 |
ROW_NUMBER() | 快 | 一般 | 分组去重并保留完整记录 |
DELETE + ROW_NUMBER() | 中 | 复杂 | 删除分组内多余记录 |
优化建议
- 索引优化：在去重字段（如
user_id和login_time）上创建索引，可以显著提高查询速度。
CREATE INDEX idx_user_login ON user_logins(user_id, login_time DESC);
- 批量处理：对于超大数据量表，使用批量删除或分批次去重，避免锁表或性能瓶颈。
DELETE FROM user_logins
WHERE id IN (SELECT idFROM user_loginsWHERE login_time < NOW() - INTERVAL 30 DAY
);
- 避免全表扫描：在查询时尽量减少无关字段，只查询需要去重的字段，减少数据库 I/O 操作量。
五、实战案例：每日用户登录记录去重
需求描述：
- 保留每个用户最近一次的登录记录，删除多余的历史记录。
WITH ranked_logins AS (SELECT id,ROW_NUMBER() OVER (PARTITION BY user_idORDER BY login_time DESC) AS rnFROM user_logins
)
DELETE FROM user_logins
WHERE id IN (SELECT id FROM ranked_logins WHERE rn > 1
);
结果：
- 每个用户仅保留一条最近的登录记录。
六、总结
DISTINCT适合简单重复数据的去重。GROUP BY+ 聚合函数 是最常用的去重方式，适合按特定规则分组去重。ROW_NUMBER()提供了更强的灵活性，可以按业务规则保留最优记录，删除其他重复数据。- 性能优化：结合索引与批量处理策略，能有效提升大数据量表的去重效率。
通过合理选择去重策略，可以确保数据的唯一性和完整性，同时提升数据库查询性能，保证业务系统的稳定高效运行。