news 2026/11/4 15:15:22

SQL数据去重实战：从基础到高级优化方案

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
SQL数据去重实战：从基础到高级优化方案

1. 数据去重问题的典型场景

上周处理一个电商订单报表时，我遇到了一个典型的数据重复问题：同一个用户ID在短时间内产生了多条相同商品ID的购买记录。这种看似简单的重复数据背后，可能隐藏着系统漏洞（如重复提交）、业务规则变化（如允许重复购买）或数据采集异常（如日志重复记录）。面对这类问题，单纯用DISTINCT或GROUP BY往往无法满足实际业务需求。

数据去重在实际工作中远比教科书上的例子复杂得多。我们经常需要处理这些情况：

  • 部分字段重复（如用户ID+时间戳相同，但其他字段不同）
  • 逻辑重复（如不同格式的同一地址"北京市海淀区"和"北京海淀区"）
  • 跨表重复（如多个数据源合并时的实体对齐）
  • 时序重复（如周期性上报的相同设备状态）

2. 基础去重方法的局限性分析

2.1 DISTINCT关键字的陷阱

新手最常犯的错误是盲目使用SELECT DISTINCT *。最近我review的一个查询，对百万级数据表使用全字段DISTINCT，导致数据库内存爆满。实际上，DISTINCT会对所有选定列的组合进行哈希计算，当包含长文本字段时性能急剧下降。

-- 错误示范（性能杀手） SELECT DISTINCT * FROM user_activities; -- 改进方案（明确指定去重字段） SELECT DISTINCT user_id, session_id FROM user_activities;

2.2 GROUP BY的隐藏成本

GROUP BY配合聚合函数是更灵活的去重方式，但要注意：

  1. 非分组字段必须使用聚合函数
  2. 大数据量时可能产生临时表
  3. 结果集排序可能不符合预期
-- 获取每个用户最新登录记录 SELECT user_id, MAX(login_time) AS last_login, -- 使用MAX获取任意一个非聚合字段值（需确认业务允许） MAX(ip_address) AS sample_ip FROM user_logins GROUP BY user_id;

3. 高级去重技术方案

3.1 窗口函数方案

当需要基于特定规则保留重复数据中的某条记录时，ROW_NUMBER()窗口函数是首选方案。去年优化一个CRM系统时，我用这种方法将客户去重查询速度提升了8倍。

WITH ranked_contacts AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id, contact_type ORDER BY update_time DESC ) AS rn FROM customer_contacts ) SELECT * FROM ranked_contacts WHERE rn = 1;

关键点：PARTITION BY定义重复判定条件，ORDER BY决定保留哪条记录

3.2 哈希指纹技术

对于需要比较整行数据是否重复的场景，可以计算哈希值作为唯一指纹：

-- MySQL示例 SELECT *, MD5(CONCAT_WS('|', col1, col2, col3)) AS row_hash FROM products GROUP BY row_hash; -- PostgreSQL更高效的方案 SELECT DISTINCT ON (md5((t.*)::text)) * FROM products t;

4. 复杂业务场景解决方案

4.1 模糊去重案例

处理用户提交的地址数据时，我开发了一套标准化+相似度匹配的组合方案：

WITH normalized_data AS ( SELECT id, -- 地址标准化处理 REGEXP_REPLACE(LOWER(address), '[^a-z0-9]', '') AS clean_addr FROM user_addresses ), similar_groups AS ( SELECT a.id AS id1, b.id AS id2, -- 计算相似度（示例使用简单重叠率） (LENGTH(a.clean_addr) + LENGTH(b.clean_addr) - LENGTH(REPLACE(a.clean_addr, b.clean_addr, ''))) / GREATEST(LENGTH(a.clean_addr), LENGTH(b.clean_addr)) AS similarity FROM normalized_data a JOIN normalized_data b ON a.id < b.id WHERE a.clean_addr LIKE CONCAT('%', b.clean_addr, '%') OR b.clean_addr LIKE CONCAT('%', a.clean_addr, '%') ) -- 获取代表记录（业务规则决定） SELECT DISTINCT FIRST_VALUE(id1) OVER (PARTITION BY least(id1,id2)) AS representative_id FROM similar_groups WHERE similarity > 0.8;

4.2 增量去重策略

在数据仓库ETL过程中，我常用这种模式处理增量数据去重：

-- 步骤1：创建临时表存储去重逻辑 CREATE TEMPORARY TABLE deduplicated_staging AS SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY business_key ORDER BY CASE WHEN is_deleted = 1 THEN 0 ELSE 1 END DESC, update_time DESC ) AS rn FROM staging_table t WHERE batch_id = 123; -- 步骤2：仅插入非重复记录 INSERT INTO target_table SELECT * FROM deduplicated_staging WHERE rn = 1 ON CONFLICT (business_key) DO UPDATE SET field1 = EXCLUDED.field1, field2 = EXCLUDED.field2;

5. 性能优化关键指标

处理10GB级别的用户行为数据时，我总结出这些优化经验：

  1. 索引策略：

    • 为PARTITION BY字段创建复合索引
    • 函数索引支持哈希去重（如PostgreSQL的表达式索引）

  2. 内存控制：

    • 设置合理的work_mem/tempdb大小
    • 对大数据集采用分批处理

  3. 执行计划检查重点：

    • 避免窗口函数的全表排序（Using filesort）
    • 确保分区裁剪生效（Partition pruning）
    • 关注哈希聚合的内存使用（HashAggregate）
-- 分页批处理示例 DECLARE @batch_size INT = 10000; DECLARE @max_id INT = (SELECT MAX(id) FROM large_table); DECLARE @current_id INT = 0; WHILE @current_id < @max_id BEGIN WITH current_batch AS ( SELECT * FROM large_table WHERE id > @current_id AND id <= @current_id + @batch_size ), deduped AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY event_time DESC) AS rn FROM current_batch ) INSERT INTO results_table SELECT * FROM deduped WHERE rn = 1; SET @current_id = @current_id + @batch_size; END

6. 特殊数据库的差异化方案

6.1 MySQL的特定优化

-- 利用派生表条件推送 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY log_time DESC) AS rn FROM device_logs WHERE log_time > '2023-01-01' ) AS t WHERE rn = 1;

6.2 PostgreSQL的独有特性

-- 使用DISTINCT ON语法（更简洁） SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, order_date DESC; -- 利用CTE MATERIALIZED优化复杂去重 WITH RECURSIVE dup_groups AS MATERIALIZED ( SELECT min(id) AS master_id, array_agg(id) AS dup_ids FROM products GROUP BY md5(concat(name,coalesce(manufacturer,''))) ) UPDATE products p SET master_id = d.master_id FROM dup_groups d WHERE p.id = ANY(d.dup_ids) AND p.id != d.master_id;

7. 实战中的经验教训

  1. 去重时机的选择：

    • 早期去重减少后续处理量
    • 晚期去重保留原始信息
    • 我通常采用"尽早过滤明显重复，最后处理业务重复"的策略

  2. 业务规则优先： 曾遇到一个案例：技术上去重的订单数据，实际上包含合法的用户重复购买。解决方案是增加业务状态校验：

    WHERE NOT (order_status = 'CANCELLED' AND EXISTS ( SELECT 1 FROM orders o2 WHERE o2.user_id = orders.user_id AND o2.product_id = orders.product_id AND o2.order_status = 'COMPLETED' ))

  3. 可视化验证： 对于复杂去重规则，我习惯先用抽样数据验证：

    -- 查看将被去重的记录样本 SELECT * FROM ( SELECT *, COUNT(*) OVER (PARTITION BY key_fields) AS dup_count FROM target_table ) AS t WHERE dup_count > 1 LIMIT 100;

处理数据去重问题时，最关键的是先明确业务上"什么是重复"。有一次我花了三天时间优化的去重查询，最后发���业务部门对"重复客户"的定义与IT理解完全不同。现在我会坚持先与业务方确认这些边界条件：

  • 时间窗口（如30天内相同操作算重复）
  • 字段组合（如仅比较核心字段忽略元数据）
  • 状态影响（如已完成的订单不参与去重）
版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/11/4 15:14:56

CLI-Anything：把重复性工作统一收口到命令行的工程方法论

如果你跟我一样&#xff0c;每天在终端里敲命令的时间比在浏览器里还多&#xff0c;那“CLI-Anything”这个名字应该会让你心头一动。它不是一个具体的软件&#xff0c;而是一套“把任何重复性工作统一收口到命令行”的方法论和工具组合——选对参数解析库&#xff0c;设计清晰…

作者头像 李华
网站建设 2026/11/4 15:14:51

AI与元宇宙技术在智慧文博中的创新应用

1. 项目背景与行业意义 2025智慧文博标杆案例的认证&#xff0c;标志着AI与元宇宙技术在文化遗产领域的应用进入新阶段。这个由五部委联合认证的项目&#xff0c;实际上反映了当前文博行业数字化转型的三大核心诉求&#xff1a; 第一是解决传统展览的时空限制问题。故宫博物院…

作者头像 李华
网站建设 2026/11/4 15:14:50

重读LeNet-5：理解CNN底层机制的不可替代沙盒

1. 为什么今天还要重读LeNet——它不是古董&#xff0c;而是解码CNN的密钥很多人第一次听说LeNet&#xff0c;是在教科书里被盖上“最古老CNN”的印章&#xff0c;然后匆匆翻过&#xff1b;也有人在面试前突击背下“LeNet-5有7层、用Sigmoid、输入3232”就以为掌握了它。但我在…

作者头像 李华
网站建设 2026/11/4 15:14:38

Hindsight：面向生产环境的LLM可观测性与调试工具链

1. 项目概述&#xff1a;Hindsight 不是“事后诸葛亮”&#xff0c;而是一套可落地的 LLM 操作系统级工具链 最近在几个技术社区和内部研发群聊里&#xff0c;反复看到 “hindsight” 这个词被拎出来讨论——不是作为哲学概念&#xff0c;也不是指代某家初创公司的融资新闻&…

作者头像 李华
网站建设 2026/11/4 15:14:00

资金管理实战：预算规划、现金流监控与风险控制

1. 资金管理的基本框架&#xff1a;从零开始构建财务掌控力第一次领到工资时&#xff0c;我和大多数年轻人一样&#xff0c;觉得资金管理就是"别把钱花光"。直到连续三个月透支信用卡&#xff0c;我才意识到问题没那么简单。资金管理本质上是对财务资源的系统化调度&…

作者头像 李华
网站建设 2026/11/4 15:10:40

SSE与WebSocket：实时通信技术对比与选型指南

1. 实时通信技术概述在Web开发领域&#xff0c;实现服务器与客户端之间的实时数据推送一直是重要课题。传统HTTP协议基于请求-响应模式&#xff0c;这种单向通信机制难以满足实时性要求高的场景。为此&#xff0c;业界发展出多种实时通信技术&#xff0c;其中Server-Sent Event…

作者头像 李华