分析函數核心語法
| 子句 | 功能 | 關鍵點 |
|---|
| PARTITION BY | 分組，函數在各分區獨立計算 | 省略 = 整張表為單一分區 |
| ORDER BY | 分區內排序，決定窗口順序 | 排名函數必須；聚合函數影響窗口範圍 |
| WINDOWING | 定義窗口範圍 (RANGE/ROWS) | 預設: RANGE UNBOUNDED PRECEDING TO CURRENT ROW |
窗口子句詳細解析
RANGE — 邏輯偏移 (依 ORDER BY 值)
-- 當前行及前 2 行
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING)
-- 整個分區所有行
SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
關鍵差異：RANGE vs ROWS
| 特性 | RANGE | ROWS |
|---|
| 基準 | ORDER BY 欄位值 | 物理行位置 |
| 相同值處理 | 同值視為同一群組 | 各行獨立計算 |
| 效能 | 較慢 (需排序分組) | 較快 |
| 適用 | 數值/日期累加、移動平均 | Top-N、固定行數窗口 |
核心分析函數分類與範例
1. 聚合類
SELECT
empno, ename, sal, deptno,
COUNT(*) OVER (PARTITION BY deptno) dept_cnt,
ROUND(AVG(sal) OVER (PARTITION BY deptno), 2) dept_avg,
MAX(sal) OVER (PARTITION BY deptno) dept_max,
MIN(sal) OVER (PARTITION BY deptno) dept_min,
SUM(sal) OVER (PARTITION BY deptno) dept_sum
FROM emp;
2. 排名類
| 函數 | 行為 | 重複值處理 |
|---|
| RANK() | 標準排名 | 跳號 (1,2,2,4) |
| DENSE_RANK() | 密集排名 | 不跳號 (1,2,2,3) |
| ROW_NUMBER() | 唯一行號 | 強制唯一 (1,2,3,4) |
-- 首/尾值 (需指定完整窗口)
FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_sal,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_sal
-- 相鄰行存取
LAG(sal, 2, 0) OVER (PARTITION BY deptno ORDER BY sal) lag_2, -- 前 2 行
LEAD(sal, 2, 0) OVER (PARTITION BY deptno ORDER BY sal) lead_2 -- 後 2 行
4. KEEP 子句 (首/尾值聚合)
| 函數 | 功能 | 範例 |
|---|
| CUME_DIST() | 累積分布 (0~1) | 排名/總行數 |
| NTILE(n) | 分桶 (1~n) | NTILE(4) 四分位數 |
| RATIO_TO_REPORT() | 值佔總和比例 | 薪資佔部門總薪資% |
| PERCENT_RANK() | 百分位排名 | (RANK-1)/(總數-1) |
SELECT * FROM (
SELECT deptno, job, sal FROM emp
)
PIVOT (
SUM(sal) FOR job IN (
'CLERK' AS clerk_sal,
'MANAGER' AS mgr_sal,
'ANALYST' AS analyst_sal,
'SALESMAN' AS sales_sal,
'PRESIDENT' AS pres_sal
)
)
ORDER BY deptno;
多重彙總 PIVOT
SELECT * FROM (
SELECT deptno, job, sal FROM emp
)
PIVOT XML (
SUM(sal) FOR job IN (ANY)
)
ORDER BY deptno;
UNPIVOT — 列轉行 (逆向)
SELECT deptno,
SUM(DECODE(job, 'CLERK', sal)) clerk_sal,
SUM(DECODE(job, 'MANAGER', sal)) mgr_sal,
SUM(DECODE(job, 'ANALYST', sal)) analyst_sal,
SUM(DECODE(job, 'SALESMAN', sal)) sales_sal,
SUM(DECODE(job, 'PRESIDENT', sal)) pres_sal
FROM emp
GROUP BY deptno;
效能與最佳實踐
- 單次掃描多重統計：分析函數避免多次自我聯結/子查詢。
- 索引支援：
PARTITION BY + ORDER BY 欄位建立複合索引可避免排序。
- 窗口預設值陷阱：有
ORDER BY 時預設窗口為 RANGE UNBOUNDED PRECEDING TO CURRENT ROW (累加)，非整個分區。
- NULLS FIRST/LAST：明確指定 NULL 排序位置，避免跨版本差異。
- 大表分頁：
ROW_NUMBER() OVER (ORDER BY ...) RN + WHERE RN BETWEEN :start AND :end。
總結
分析函數是 SQL 進階分析的「瑞士刀」：單次掃描、多維度計算、靈活窗口控制。掌握 PARTITION BY、ORDER BY、RANGE/ROWS 三大子句互動，熟練運用排名、聚合、取值、報表四大類函數，並善用 PIVOT/UNPIVOT 完成行列轉換，能以極簡潔 SQL 解決複雜報表與分析需求。
