一、引言

• 近期參加了數據崗位的一些面試（如下圖：近幾年的面試數據），非常多的同學在簡歷上會寫熟悉、精通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列式存儲。