【SQL】基于SQL 去重方法对比 -- 精华版一、引言二、基于SQL去重方法完整对比1. MySQL去重方法及优劣势1.1 ​DISTINCT关键字1.2 GROUP BY子句1.3 UNION系列操作1.4 子查询 自关联2. Hive去重方法及优劣势2.1 DISTINCT关键字2.2 ​GROUP BY子句2.3 ​ROW_NUMBER窗口函数2.4 UNION系列操作2.5 近似去重算法二、方法对比与适用场景三、总结与场景建议1. ​MySQL场景2. Hive场景3.通用优化策略一、引言近期参加了数据岗位的一些面试如下图近几年的面试数据非常多的同学在简历上会写熟悉、精通SQL但一旦进行原理性对应数据开发岗或者实操性数据分析、数据产品岗的沟通和测试往往表现的不尽如人意。所以打算再开一个【SQL】的专栏分享一些SQL的知识和技巧。SQL专题往期内容【SQL】基于多源SQL 去重方法对比 – 精华版【SQL】常见SQL 行列转换的方法汇总 - 精华版【SQL】MySql常见的性能优化方式【SQL】SQL同环比计算的多种实现方式【SQL】COUNT… FILTER 的适用场景【SQL】SQL的日期与时间函数【SQL】SQL-常见窗口函数有哪些-上篇二、基于SQL去重方法完整对比1. MySQL去重方法及优劣势1.1 ​DISTINCT关键字方法直接对字段组合去重语法简单。优势操作直观适合小数据集或快速测试。劣势性能差大数据量时触发全表扫描效率低。功能局限无法筛选特定行如保留最新记录。SELECTDISTINCTtest_idFROMtest;-- 单字段去重SELECTCOUNT(DISTINCTtest_id)FROMtest;-- 去重计数1.2 GROUP BY子句方法分组后取唯一值常配合子查询统计总数。优势效率较高单字段去重时比DISTINCT更快。​支持聚合可结合COUNT、MAX等函数。劣势结果不稳定非GROUP BY字段可能返回随机值MySQL特有。​复杂度高多字段分组时计算资源消耗大。SELECTtest_idFROMtestGROUPBYtest_id;SELECTCOUNT(test_id)FROM(SELECTtest_idFROMtestGROUPBYtest_id)tmp;1.3 UNION系列操作方法合并多表数据自动去重需注意性能问题。​优势适合跨表数据合并场景。劣势资源消耗大UNION去重需全局排序大数据量性能差。-- UNION自动去重性能低SELECTtest_idFROMtest_2023UNIONSELECTtest_idFROMtest_2024-- UNION ALL DISTINCT分阶段处理SELECTDISTINCTuser_idFROM(SELECTuser_idFROMorders_2023UNIONALLSELECTuser_idFROMorders_2024)tmp;1.4 子查询 自关联方法通过条件排除重复记录保留特定行。优势精准控制保留逻辑如保留时间最新的记录。劣势性能差嵌套查询复杂度高不适合大规模数据。SELECT*FROMtest t1WHERENOTEXISTS(SELECT1FROMtest t2WHEREt1.test_idt2.test_idANDt1.timet2.time);2. Hive去重方法及优劣势2.1 DISTINCT关键字方法语法与MySQL一致底层优化效果更佳。优势适合小规模数据或快速验证。劣势性能瓶颈大数据量时仍需全表扫描需配合分区或列式存储优化。SELECTDISTINCTuser_idFROMuser;2.2 ​GROUP BY子句方法分组去重支持多字段组合。​优势高效稳定结合MapReduce优化性能优于DISTINCT。聚合灵活支持COUNT、SUM等函数。劣势无法灵活筛选组内特定行。SELECTuser_idFROMuserGROUPBYuser_id;2.3 ​ROW_NUMBER窗口函数方法按分区排序后取唯一值适合复杂逻辑。​优势灵活性强可指定保留最新、最旧或特定排序规则的数据。适用性广适合“一对多”关系数据去重。劣势性能要求高需合理设置分区和排序字段以避免性能问题。SELECTuser_id,log_timeFROM(SELECT*,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYlog_timeDESC)rnFROMuser)tmpWHERErn12.4 UNION系列操作方法合并跨分区或跨表数据需权衡资源消耗。​优势适合增量数据整合或历史表合并。劣势资源占用高UNION去重需全局排序可能占用大量内存。-- UNION自动去重SELECTuser_idFROMuser_1UNIONSELECTuser_idFROMuser_2;-- UNION ALL DISTINCT分阶段处理SELECTDISTINCTuser_idFROM(SELECTuser_idFROMuser_1UNIONALLSELECTuser_idFROMuser_2)tmp;2.5 近似去重算法方法通过概率算法快速估算去重值如HyperLogLog一般日常涉及较少。​优势极速计算适合超大规模数据如TB级日志。劣势结果非精确仅适用于统计场景不适用于业务明细查询。SELECTAPPROX_COUNT_DISTINCT(user_id)FROMuser;-- 误差率约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列式存储。