【SQL】基于多源SQL 去重方法对比 -- 精华版

一、引言

• 近期参加了数据岗位的一些面试（如下图：近几年的面试数据），非常多的同学在简历上会写熟悉、精通SQL，但一旦进行原理性（对应数据开发岗）或者实操性（数据分析、数据产品岗）的沟通和测试，往往表现的不尽如人意。所以打算再开一个【SQL】的专栏，分享一些SQL的知识和技巧。
• SQL专题往期内容：
  • 【SQL】基于多源SQL 去重方法对比 – 精华版
  • 【SQL】常见SQL 行列转换的方法汇总 - 精华版
    

二、基于SQL去重方法完整对比

1. MySQL去重方法及优劣势

1.1 ​DISTINCT关键字

• 方法：直接对字段组合去重，语法简单。
• 优势：操作直观，适合小数据集或快速测试。
• 劣势：
  • 性能差：大数据量时触发全表扫描，效率低。
  • 功能局限：无法筛选特定行（如保留最新记录）。

SELECT DISTINCT test_id FROM test;         -- 单字段去重
SELECT COUNT(DISTINCT test_id) FROM test;  -- 去重计数

1.2 GROUP BY子句

• 方法：分组后取唯一值，常配合子查询统计总数。
• 优势：
  • 效率较高：单字段去重时比DISTINCT更快。
  • ​支持聚合：可结合COUNT、MAX等函数。
• 劣势：
  • 结果不稳定：非GROUP BY字段可能返回随机值（MySQL特有）。
  • ​复杂度高：多字段分组时计算资源消耗大。

SELECT test_id FROM test GROUP BY test_id;
SELECT COUNT(test_id) FROM (SELECT test_id FROM test GROUP BY test_id) tmp;

1.3 UNION系列操作

• 方法：合并多表数据自动去重，需注意性能问题。
• ​优势：适合跨表数据合并场景。
• 劣势：
  • 资源消耗大，UNION去重需全局排序，大数据量性能差。

-- UNION自动去重，性能低 
SELECT test_id FROM test_2023 
UNION 
SELECT test_id FROM test_2024-- UNION ALL + DISTINCT分阶段处理 
SELECT DISTINCT user_id FROM (SELECT user_id FROM orders_2023 UNION ALL SELECT user_id FROM orders_2024) tmp;

1.4 子查询 + 自关联

• 方法：通过条件排除重复记录，保留特定行。
• 优势：精准控制保留逻辑（如保留时间最新的记录）。
• 劣势：
  • 性能差：嵌套查询复杂度高，不适合大规模数据。

SELECT * FROM test t1 
WHERE NOT EXISTS (SELECT 1 FROM test t2 WHERE t1.test_id = t2.test_id AND t1.time < t2.time
);

2. Hive去重方法及优劣势

2.1 DISTINCT关键字

• 方法：语法与MySQL一致，底层优化效果更佳。
• 优势：适合小规模数据或快速验证。
• 劣势：
  • 性能瓶颈：大数据量时仍需全表扫描，需配合分区或列式存储优化。

SELECT DISTINCT user_id FROM user ;

2.2 ​GROUP BY子句

• 方法：分组去重，支持多字段组合。
• ​优势：
  • 高效稳定：结合MapReduce优化，性能优于DISTINCT。
  • 聚合灵活：支持COUNT、SUM等函数。
• 劣势：无法灵活筛选组内特定行。

SELECT user_id  FROM user GROUP BY user_id ;

2.3 ​ROW_NUMBER窗口函数

• 方法：按分区排序后取唯一值，适合复杂逻辑。
• ​优势：
  • 灵活性强：可指定保留最新、最旧或特定排序规则的数据。
  • 适用性广：适合“一对多”关系数据去重。
• 劣势：
  • 性能要求高：需合理设置分区和排序字段以避免性能问题。

SELECT user_id, log_time 
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_time DESC) rn FROM user
) tmp 
WHERE rn = 1

2.4 UNION系列操作

• 方法：合并跨分区或跨表数据，需权衡资源消耗。
• ​优势：适合增量数据整合或历史表合并。
• 劣势：
  • 资源占用高：UNION去重需全局排序，可能占用大量内存。

-- UNION自动去重
SELECT user_id FROM user_1 UNION SELECT user_id FROM user_2;
-- UNION ALL + DISTINCT分阶段处理
SELECT DISTINCT user_id FROM (SELECT user_id FROM user_1 UNION ALL SELECT user_id FROM user_2) tmp;

2.5 近似去重算法

• 方法：通过概率算法快速估算去重值，如HyperLogLog，一般日常涉及较少。
• ​优势：
  • 极速计算：适合超大规模数据（如TB级日志）。
• 劣势：
  • 结果非精确：仅适用于统计场景，不适用于业务明细查询。

SELECT APPROX_COUNT_DISTINCT(user_id) FROM user;  -- 误差率约1%

二、方法对比与适用场景

方法	MySQL适用性	Hive适用性	优势	劣势
DISTINCT	小数据量简单去重	小数据量	简单	数据量大性能差，无法筛选特定行
GROUP BY	高效单字段	高效多字段组合	支持聚合，效率高	mysql不稳定
ROW_NUMBER	不支持	复杂去重	灵活性强，支持排序逻辑	资源消耗高
UNION系列	跨表合并去重	跨分区/表合并去重	处理多源数据	性能低，资源消耗高

三、总结与场景建议

1. ​MySQL场景

• 简单查询：GROUP BY、DISTINCT均可
• ​跨表合并：使用UNION ALL + DISTINCT分阶段处理。
• 保留最新记录：通过子查询+自关联实现。

2. Hive场景

• 常规去重：GROUP BY（性能稳定）、ROW_NUMBER（保留特定排名）。
• ​增量数据：UNION ALL + ROW_NUMBER避免全表扫描。
• ​超大数据统计：用APPROX_COUNT_DISTINCT平衡性能与精度。

3.通用优化策略

• 索引/分区：MySQL加索引，Hive用分区表提升效率。
• 资源管理：Hive合理配置MapReduce资源，避免OOM。
• 存储格式：Hive优先选择ORC/Parquet列式存储。