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天内相同操作算重复）
• 字段组合（如仅比较核心字段忽略元数据）
• 状态影响（如已完成的订单不参与去重）