通用語法架構
FUNCTION_NAME ( [arguments] )
OVER (
[ PARTITION BY expr [, ...] ]
[ ORDER BY expr [ASC|DESC] [NULLS FIRST|LAST] ]
[ WINDOWING_CLAUSE ]
)
三大子句詳細說明
1. PARTITION BY — 分區 (分群)
- 將結果集切分為獨立群組，函數在各群組**獨立計算**，群組間不干擾。
- 類比
GROUP BY，但**不縮減行數**，每行保留原始粒度。
- 可省略 → 整個結果集視為單一分區。
2. ORDER BY — 排序 (決定計算順序)
- 分區內排序，影響
ROW_NUMBER, RANK, LEAD/LAG, FIRST/LAST_VALUE 等**順序敏感**函數。
NULLS FIRST|LAST 控制空值排序位置 (預設 ASC→LAST, DESC→FIRST)。- 若無
ORDER BY，順序敏感函數結果**不確定**。
3. WINDOWING CLAUSE — 窗口範圍 (動態滑動框架)
{ RANGE | ROWS } BETWEEN
{ UNBOUNDED PRECEDING | CURRENT ROW | n PRECEDING | n FOLLOWING }
AND
{ UNBOUNDED FOLLOWING | CURRENT ROW | n PRECEDING | n FOLLOWING }
| 類型 | 語意 | 適用資料型別 | 備註 |
|---|
RANGE | 基於 **ORDER BY 欄位值域** 的邏輯範圍 | 數值、日期 | 同值視為同一邊界 |
ROWS | 基於 **物理行數** 的相對位移 | 任意 | 精確控制行數 |
**預設窗口**：RANGE UNBOUNDED PRECEDING (分區起始到當前行) — 這也是大多數聚合函數的預設行為。
常用分析函數速查表
| 函數 | 用途 | 關鍵參數 | 範例 |
|---|
ROW_NUMBER() | 唯一行號 | — | 去重保留第一筆、精準 Top-N |
RANK() | 競爭排名 (跳號) | — | 並列獎項 |
DENSE_RANK() | 緊湊排名 (不跳號) | — | 薪資等級、分級統計 |
LAG(expr, offset, default) | 向前取值 | offset(預設1), default | 環比計算：sal - LAG(sal) OVER (...) |
LEAD(expr, offset, default) | 向後取值 | offset(預設1), default | 預測下期、差異比較 |
FIRST_VALUE(expr) | 分區第一值 | — | 基準值比較 |
LAST_VALUE(expr) | 分區最後值 | 需窗口 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | 結尾基準 |
NTILE(n) | 等分桶 (分位數) | 桶數 n | 四分位數 NTILE(4) |
CUME_DIST() | 累積分布 (0~1) | — | 百分位計算 |
AVG/SUM/MIN/MAX/COUNT | 窗口聚合 | 窗口子句 | 移動平均 AVG(sal) OVER (ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) |
CORR/COVAR_POP/COVAR_SAMP | 統計相關/協方差 | 兩表達式 | 相關性分析 |
實戰範例：移動平均與環比增長
SELECT deptno, ename, hiredate, sal,
-- 3 個月移動平均 (含當月)
ROUND(AVG(sal) OVER (PARTITION BY deptno ORDER BY hiredate
RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND CURRENT ROW), 2) AS mov_avg_3m,
-- 環比增長率
ROUND((sal - LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate))
/ NULLIF(LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate), 0) * 100, 2) AS mom_pct
FROM emp
ORDER BY deptno, hiredate;
效能與避坑指南
- 排序成本：
ORDER BY 觸發 WINDOW SORT，大數據量確保 PGA 足夠、考慮並行。
- 窗口預設值陷阱：
LAST_VALUE 預設窗口只到當前行，**必須顯式擴展至 UNBOUNDED FOLLOWING**。
- RANGE 僅支援數值/日期：字串欄位請改用
ROWS。
- 12c+
FETCH FIRST ... WITH TIES 可替代 RANK() <= N，執行計畫相同但可讀性更高。
結語
分析函數的核心在於 **OVER 子句三部曲：分區 → 排序 → 窗口**。熟練掌握 RANGE/ROWS、PRECEDING/FOLLOWING、NULLS FIRST/LAST 組合，能解決 90% 複雜報表需求 (移動統計、同比環比、分位數、去重、差異偵測)。建議收藏速查表，實戰中隨查隨用。
