Oracle 聚合函數與 NVL 函數完整使用指南

Oracle 聚合函數與 NVL 函數完整使用指南

聚合函數是 SQL 查詢中進行資料統計分析的核心工具，配合 NVL 處理空值，能靈活解決各類報表需求。本文系統整理 COUNT、MAX、MIN、SUM、AVG 五大聚合函數語法、特性與實戰技巧。

一、五大核心聚合函數

二、進階用法與常見陷阱

1. 搭配 GROUP BY 分組統計

SELECT deptno, COUNT(*), AVG(sal), MAX(sal), MIN(sal), SUM(sal)
FROM emp
GROUP BY deptno;

2. 搭配 HAVING 過濾分組

SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 2000;

3. DISTINCT 去重聚合

SELECT COUNT(DISTINCT job) FROM emp;  -- 統計不重複職稱種類數

4. 空值處理關鍵：NVL / NVL2 / COALESCE

聚合函數自動忽略 NULL，但某些業務邏輯需將 NULL 視為 0 或其他預設值：

-- 將 comm 空值視為 0 參與加總與平均
SELECT SUM(NVL(comm, 0)), AVG(NVL(comm, 0)) FROM emp;

-- NVL2: 值非 NULL 回傳第二參數，NULL 回傳第三參數
SELECT ename, NVL2(comm, '有獎金', '無獎金') FROM emp;

-- COALESCE: 回傳第一個非 NULL 引數 (ANSI 標準，支援多參數)
SELECT COALESCE(comm, bonus, 0) FROM emp;

三、實戰案例：薪資分析報表

SELECT 
  d.dname 部門,
  COUNT(e.empno) 員工數,
  ROUND(AVG(NVL(e.sal,0)), 2) 平均薪資,
  MAX(e.sal) 最高薪資,
  MIN(e.sal) 最低薪資,
  SUM(NVL(e.sal,0)) 薪資總額,
  COUNT(DISTINCT e.job) 職稱種類數
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY 部門;

四、效能優化提示

• 聚合欄位若有索引，MIN/MAX 可透過 INDEX FULL SCAN (MIN/MAX) 極速取值。
• 大表統計考慮使用 APPROX_COUNT_DISTINCT (12c+) 近似計數，大幅降低資源消耗。
• 避免在聚合欄位上使用函數或運算導致索引失效；必要時建立函數索引。

記住：COUNT(*) 統計列數最快；COUNT(column) 需檢查 NULL；SUM/AVG 對 NULL 自動忽略，若業務需視為 0 必須显式 NVL；NVL2/COALESCE 提供更豐富的空值邏輯控制。