Oracle 聚合函數完全指南：核心函數、NULL 處理與 DISTINCT 應用

什麼是聚合函數？

聚合函數（Aggregate Functions）又稱群組函數，用於對一組資料列進行計算並返回單一結果值。Oracle 提供豐富的聚合函數，廣泛應用於統計報表、資料分析、商業智慧等場景。

常用聚合函數速查表

核心特性：自動忽略 NULL 值

所有聚合函數（COUNT(*) 除外）預設會忽略 NULL 值。這是最常見的誤區來源。

實戰驗證（以 SCOTT.EMP 表為例）

-- EMP 表共 14 筆資料，MGR 欄位有 1 筆 NULL（KING 社長無主管）
SELECT COUNT(*)     AS total_rows,   -- 14，計算所有行
       COUNT(mgr)   AS mgr_count,    -- 13，忽略 NULL
       COUNT(comm)  AS comm_count    -- 4，只有 4 位業務有提成
FROM emp;

TOTAL_ROWS  MGR_COUNT  COMM_COUNT
----------  ---------  ----------
        14         13          4

SUM、AVG 等函數同理

SELECT SUM(comm), AVG(comm), MAX(comm), MIN(comm)
FROM emp;
-- 皆自動忽略 COMM 為 NULL 的 10 筆資料

DISTINCT 關鍵字：去重統計

在聚合函數中使用 DISTINCT 可排除重複值，僅統計唯一值：

-- 錯誤寫法：重複計算部門編號
SELECT COUNT(deptno) FROM emp;  -- 14

-- 正確寫法：統計唯一部門數
SELECT COUNT(DISTINCT deptno) FROM emp;  -- 3

DISTINCT 適用於所有聚合函數

-- 不同薪資等級的平均薪資（去除相同薪資值）
SELECT AVG(DISTINCT sal) FROM emp;

-- 不同職稱的最高薪資
SELECT MAX(DISTINCT sal) KEEP (DENSE_RANK FIRST ORDER BY job) FROM emp;  -- 進階用法

GROUP BY 進階應用

基本分組統計

SELECT deptno, 
       COUNT(*) AS emp_count,
       AVG(sal) AS avg_sal,
       MAX(sal) AS max_sal,
       MIN(sal) AS min_sal,
       SUM(sal) AS total_sal
FROM emp
GROUP BY deptno
ORDER BY deptno;

HAVING 子句：分組後過濾

-- 僅顯示平均薪資超過 2000 的部門
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000;

NULL 值處理進階技巧

使用 NVL/COALESCE 將 NULL 轉為預設值參與統計

-- 將 NULL 提成視為 0 計算平均提成
SELECT AVG(NVL(comm, 0)) AS avg_comm_with_zero
FROM emp;
-- 結果會包含所有 14 位員工，平均值較低

COUNT 的三種寫法差異總結

資料型別適用性對照

效能優化建議

• 索引利用：GROUP BY 欄位若有索引，Oracle 可採用 SORT GROUP BY 或 HASH GROUP BY 優化
• 避免隱含轉換：聚合函數參數型別需一致，防止 CPU 浪費
• 大量資料考慮：適度使用 APPROX_COUNT_DISTINCT（12c+）獲得近似去重計數，大幅提升效能
• 並行查詢：大表聚合可加入 PARALLEL Hint 加速

總結

掌握 Oracle 聚合函數的核心在於理解「自動忽略 NULL」與「DISTINCT 去重」兩大特性。結合 GROUP BY、HAVING 子句，即可構建強大的統計分析查詢。實務上需特別注意 COUNT(*) 與 COUNT(col) 的語意差異，以及 NULL 值對 AVG、SUM 等計算結果的影響，避免業務邏輯錯誤。