Oracle 分析函數 (Analytic Functions) 完整指南：語法三大子句、常用函數表、LAG/LEAD 實戰與 LISTAGG 特殊用法
語法結構三大子句
function(arg1, arg2, ...) OVER (
[PARTITION BY partition_cols] -- 分區子句：分組，獨立計算
[ORDER BY order_cols [ASC|DESC] [NULLS FIRST|LAST]] -- 排序子句：決定視窗順序
[WINDOWING_CLAUSE] -- 開窗子句：限定計算範圍
)
開窗子句詳細語法
[ROWS | RANGE] BETWEEN AND
::= UNBOUNDED PRECEDING | CURRENT ROW | n PRECEDING | n FOLLOWING
::= UNBOUNDED FOLLOWING | CURRENT ROW | n PRECEDING | n FOLLOWING
-- PRECEDING = 上界 (前方), FOLLOWING = 下界 (後方)
- ROWS：物理行數邊界；RANGE：邏輯值邊界 (需 ORDER BY 單一數值/日期欄)。
- 預設開窗：
RANGE UNBOUNDED PRECEDING AND CURRENT ROW (分區起始至當前行)。
常用分析函數速查表
| 函數 | 用途 | 備註 |
|---|
| ROW_NUMBER() | 分區內唯一遞增序號 | 無重複，適合分頁、去重 |
| RANK() / DENSE_RANK() | 排序名次 (有/無間隙) | 並列同名次 |
| LAG(col, n, default) | 取前 n 行的 col 值 | 預設 n=1, default=NULL |
| LEAD(col, n, default) | 取後 n 行的 col 值 | 同上 |
| FIRST_VALUE / LAST_VALUE | 視窗首/尾值 | 搭配 IGNORE NULLS (12c+) |
| NTILE(n) | 分桶 (等高分位) | 回傳 1~n 桶號 |
| SUM/AVG/MIN/MAX/COUNT OVER | 視窗聚合 | 移動總和、移動平均 |
| RATIO_TO_REPORT | 佔比 (值/分區總和) | 百分比計算 |
| LISTAGG(col, delim) WITHIN GROUP (ORDER BY ...) | 字串彙總 | 不支援開窗子句 |
實戰：LAG 函數對比 (有/無預設值)
SELECT LISTAGG(sid, ',') WITHIN GROUP (ORDER BY sid DESC) AS sids
FROM (SELECT sid FROM user_table WHERE userid=76298 ORDER BY sid);
-- 結果: 31827,11199,9303,7901,5629,3254
- 等同
WM_CONCAT (非官方支援)；12c+ 支援 ON OVERFLOW TRUNCATE 防溢位。
避坑提醒
- 分析函數不可巢狀；需用子查詢/Inline View 包裝。
ORDER BY 缺失時，LAG/LEAD/FIRST_VALUE/LAST_VALUE 結果不確定。- 大量資料分區排序可能產生 TEMP 空間壓力；考慮索引支援
ORDER BY 避免排序。
