Oracle 聚合函數完全手冊：AVG、SUM、VARIANCE、COUNT、MIN/MAX 參數詳解與實戰

聚合函數是 SQL 統計分析的核心工具。Oracle 提供豐富的聚合函數族群，支援 DISTINCT/ALL 修飾符、多種資料型別與空值處理語義。本文系統整理五大核心聚合函數的語法、參數、返回值與完整測試案例。

一、測試環境建立

CREATE TABLE table3 (xm VARCHAR2(8), sal NUMBER(7,2));
INSERT INTO table3 VALUES ('gao', 1111.11);
INSERT INTO table3 VALUES ('gao', 1111.11);
INSERT INTO table3 VALUES ('zhu', 5555.55);
COMMIT;

-- 含空值測試
INSERT INTO table3 VALUES ('', 1111.11);
INSERT INTO table3 VALUES ('zhu', 0);
COMMIT;

二、核心聚合函數詳解

1. AVG([DISTINCT|ALL] x) — 平均值

• 參數：x 必須為數值型；ALL（預設）計算所有值，DISTINCT 去重後計算
• 返回：數值型
• 空值處理：忽略 NULL

SELECT AVG(DISTINCT sal), AVG(ALL sal), AVG(sal) FROM table3;
-- 結果：3333.33, 2592.59, 2592.59

2. SUM([DISTINCT|ALL] x) — 合計值

• 參數/返回：同 AVG

SELECT SUM(DISTINCT sal), SUM(ALL sal), SUM(sal) FROM table3;
-- 結果：6666.66, 7777.77, 7777.77

3. VARIANCE([DISTINCT|ALL] x) — 方差

• 功能：統計數值分布離散程度

SELECT VARIANCE(DISTINCT sal), VARIANCE(ALL sal), VARIANCE(sal) FROM table3;
-- 結果：9876523.4568, 6584348.9712, 6584348.9712

4. COUNT(* | [DISTINCT|ALL] x) — 計數

SELECT COUNT(*), COUNT(xm), COUNT(ALL xm), COUNT(DISTINCT sal), COUNT(ALL sal), COUNT(sal), SUM(1) FROM table3;
-- 結果：5, 4, 4, 3, 5, 5, 5

等價關係：COUNT(*) = SUM(1)

5. MIN/MAX([DISTINCT|ALL] x) — 極值

• 適用型別：數值、字元、日期
• 空值處理：忽略 NULL
• 字元比較：按字典序（字元集編碼順序）

SELECT MIN(DISTINCT sal), MIN(xm), MIN(DISTINCT xm), MIN(ALL xm) FROM table3;
-- 結果：0, 'gao', 'gao', 'gao'

三、關鍵行為總結表

四、進階應用技巧

• 條件聚合：SUM(CASE WHEN sal > 2000 THEN 1 ELSE 0 END) 實現多維統計
• 分組集合：GROUP BY ROLLUP/CUBE/GROUPING SETS 產生多層級彙總
• 窗口聚合：AVG(sal) OVER (PARTITION BY dept) 保留明細行同時顯示組統計
• 近似聚合：12c+ APPROX_COUNT_DISTINCT、APPROX_MEDIAN 針對海量資料近似計算

掌握聚合函數的細微語義差異（特別是 NULL 與 DISTINCT 的交互），是編寫正確、高效統計查詢的基礎。