對 整個 SELECT 清單 去重,而非單一欄位。
SELECT DISTINCT City, Description FROM Employee;
-- 結果 5 筆:
-- New York Manager
-- Vancouver Tester
-- Toronto Programmer
-- Vancouver Manager
-- New York Tester
SELECT Coder,
COUNT(DISTINCT course) AS distinct_courses,
COUNT(*) AS total_rows
FROM offerings
GROUP BY Coder;
-- 結果:
CODER DISTINCT_COURSES TOTAL_ROWS
1 2 3
4 2 2
8 2 2
11 1 1
13 2 2
3 3 3
DISTINCT 內部以排序/雜湊去重,大數據量極耗資源COUNT(DISTINCT col) 配合近似算法 (APPROX_COUNT_DISTINCT 12c+)GROUP BY + 聚合函數替代 DISTINCT-- 低效: 先分組再 HAVING
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB IN ('PRESIDENT','MANAGER');
-- 高效: 先 WHERE 過濾再分組
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB IN ('PRESIDENT','MANAGER') GROUP BY JOB;
減少參與分組的資料量,顯著降低 CPU/記憶體消耗。