当前位置: 首页 > news >正文

数据去重与重复数据的高效处理策略

在实际业务中,数据去重是一个非常常见的需求,特别是在日志数据、用户操作记录或交易记录等领域。去重不仅仅是删除重复数据,更重要的是按照业务规则保留最有价值的数据记录

本文将探讨如何在 SQL 中高效地处理重复数据,通过 DISTINCTROW_NUMBER()GROUP BY 等方法解决不同场景下的数据去重问题。


一、常见的去重场景

  1. 用户登录记录:保留每个用户的最新登录记录,删除其他重复记录。
  2. 订单数据:针对重复订单记录,保留金额最大的订单或最新的订单记录。
  3. 日志分析:在日志表中去除重复的操作记录,只保留最近一次操作。

二、数据示例

假设有一个用户登录记录表 user_logins,其结构如下:

iduser_idlogin_timedevice
11012024-01-01 08:30:00PC
21012024-01-02 10:00:00Mobile
31022024-01-01 09:15:00PC
41032024-01-01 14:45:00Tablet
51012024-01-03 11:00:00Mobile
61022024-01-04 16:30:00Mobile

目标

  • 保留每个用户最近一次的登录记录,删除其他重复数据。

三、常用去重方法


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()复杂删除分组内多余记录

优化建议
  1. 索引优化:在去重字段(如 user_idlogin_time)上创建索引,可以显著提高查询速度。
CREATE INDEX idx_user_login ON user_logins(user_id, login_time DESC);
  1. 批量处理:对于超大数据量表,使用批量删除或分批次去重,避免锁表或性能瓶颈。
DELETE FROM user_logins
WHERE id IN (SELECT idFROM user_loginsWHERE login_time < NOW() - INTERVAL 30 DAY
);
  1. 避免全表扫描:在查询时尽量减少无关字段,只查询需要去重的字段,减少数据库 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
);

结果

  • 每个用户仅保留一条最近的登录记录。

六、总结

  1. DISTINCT 适合简单重复数据的去重。
  2. GROUP BY + 聚合函数 是最常用的去重方式,适合按特定规则分组去重。
  3. ROW_NUMBER() 提供了更强的灵活性,可以按业务规则保留最优记录,删除其他重复数据。
  4. 性能优化:结合索引与批量处理策略,能有效提升大数据量表的去重效率。

通过合理选择去重策略,可以确保数据的唯一性和完整性,同时提升数据库查询性能,保证业务系统的稳定高效运行。

http://www.lryc.cn/news/515608.html

相关文章:

  • Spring Boot自动装配代码详解
  • 渗透测试-非寻常漏洞案例
  • 122. 买卖股票的最佳时机 II
  • Python爬虫入门指南:从零开始抓取数据
  • Android使用JAVA调用JNI原生C++方法
  • ros常用命令记录
  • UE5材质节点VertexNormalWs/PixelNormalWS
  • 友元和运算符重载
  • 【数据库事务锁的类型:读锁/写锁、悲观锁/乐观锁、表锁/页锁/行锁】
  • 【Motion Builder】配置c++插件开发环境
  • 多线程访问FFmpegFrameGrabber.start方法阻塞问题
  • MySQL使用记录
  • 【视觉SLAM:六、视觉里程计Ⅰ:特征点法】
  • Python 数据结构揭秘:栈与队列
  • 常见的框架漏洞
  • 在C++中实现一个能够捕获弹窗、检查内容并在满足条件时点击按钮的程序;使用python的方案
  • 《Vue3实战教程》26:Vue3Transition
  • 【架构设计(一)】常见的Java架构模式
  • 自定义有序Map
  • Jenkins(持续集成与自动化部署)
  • redis7基础篇2 redis的哨兵模式2
  • windows终端conda activate命令行不显示环境名
  • SpringBoot 2.6 集成es 7.17
  • 加固服务器有什么用?
  • Personal APP
  • 探索最新的编程技术趋势:AI 编程助手和未来的编程方式
  • Android:文件管理:打开文件意图
  • 从纯虚类到普通类:提升C++ ABI兼容性的策略
  • QT中如何限制 限制QLineEdit只能输入字母,或数字,或某个范围内数字等限制约束?
  • Tailwind CSS 使用简介