萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle DISTINCT 去重實戰與 GROUP BY 進階搭配

DISTINCT 基本用法

對 整個 SELECT 清單 去重,而非單一欄位。

Employee 測試資料 (8 筆,薪資多為 2334.78)

SELECT DISTINCT City, Description FROM Employee;
-- 結果 5 筆:
-- New York  Manager
-- Vancouver Tester
-- Toronto   Programmer
-- Vancouver Manager
-- New York  Tester

DISTINCT 搭配聚合函數

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

GROUP BY 進階:先過濾後分組

-- 低效: 先分組再 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/記憶體消耗。

copyright © 萬盛學電腦網 all rights reserved