Oracle OVER() 分析函數完全指南：PARTITION BY、ORDER BY、窗口框架與實戰模式

核心認知：OVER() 定義分析函數的「計算窗口」

OVER() 子句為分析函數指定：分區 (PARTITION BY)、排序 (ORDER BY)、窗口框架 (ROWS/RANGE BETWEEN)。它不改變結果集行數，而是為每行附加基於窗口的計算結果。

語法結構

analytic_function([arguments]) OVER (
  [PARTITION BY partition_expr [, ...]]
  [ORDER BY order_expr [ASC|DESC] [NULLS FIRST|LAST] [, ...]]
  [windowing_clause]
)

實戰資料集：SCOTT.EMP (14 筆)

EMPNO ENAME      JOB         MGR  HIREDATE  SAL   COMM DEPTNO
----- ---------- --------- ----- -------- ----- ----- ------
7369  SMITH      CLERK      7902 17-DEC-80  800        20
7499  ALLEN      SALESMAN   7698 20-FEB-81 1600  300   30
7521  WARD       SALESMAN   7698 22-FEB-81 1250  500   30
7566  JONES      MANAGER    7839 02-APR-81 2975        20
7654  MARTIN     SALESMAN   7698 28-SEP-81 1250 1400   30
7698  BLAKE      MANAGER    7839 01-MAY-81 2850        30
7782  CLARK      MANAGER    7839 09-JUN-81 2450        10
7788  SCOTT      ANALYST    7566 19-APR-87 3000        20
7839  KING       PRESIDENT       17-NOV-81 5000        10
7844  TURNER     SALESMAN   7698 08-SEP-81 1500    0   30
7876  ADAMS      CLERK      7788 23-MAY-87 1100        20
7900  JAMES      CLERK      7698 03-DEC-81  950        30
7902  FORD       ANALYST    7566 03-DEC-81 3000        20
7934  MILLER     CLERK      7782 23-JAN-82 1300        10

模式 1：SUM() OVER(PARTITION BY) — 分組彙總不分組

SELECT empno, ename, deptno, sal,
       SUM(sal) OVER (PARTITION BY deptno) AS dept_sal_total
FROM scott.emp;

結果：每行顯示所屬部門薪資總和 (10:8750, 20:10875, 30:9400)，不減少行數，對比 GROUP BY 僅輸出 3 行。

模式 2：SUM() OVER(PARTITION BY ORDER BY) — 累加/移動彙總

SELECT empno, ename, deptno, sal,
       SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS dept_running_total
FROM scott.emp;

結果：部門內按薪資遞增累加 (10: 1300→3750→8750)。ORDER BY 隱含窗口框架：RANGE UNBOUNDED PRECEDING AND CURRENT ROW (從分區起始到當前行)。

模式 3：RANK() OVER() — 分組排序名次

SELECT empno, sal, job,
       RANK() OVER (PARTITION BY job ORDER BY sal DESC) AS job_sal_rank
FROM scott.emp;

結果：同職位內薪資降冪排名。並列同名次，跳過後續名次 (如兩筆 3000 同第 1，下一筆第 3)。

對比函數：

• DENSE_RANK()：並列同名次，不跳過 (1,1,2,3...)
• ROW_NUMBER()：嚴格唯一遞增 (1,2,3,4...)

進階窗口框架控制

移動平均實戰

SELECT empno, ename, hiredate, sal,
       AVG(sal) OVER (
         ORDER BY hiredate
         RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW
       ) AS mov_avg_30d
FROM scott.emp;

常見分析函數分類

實戰模式庫

1. Top-N 每組前 N 名

SELECT * FROM (
  SELECT empno, ename, deptno, sal,
         ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn
  FROM emp
) WHERE rn <= 3;

2. 去重保留最新/最舊

SELECT * FROM (
  SELECT empno, ename, deptno, sal,
         ROW_NUMBER() OVER (PARTITION BY empno ORDER BY hiredate DESC) AS rn
  FROM emp
) WHERE rn = 1;

3. 環比/同比計算

SELECT deptno, hiredate, sal,
       LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS prev_sal,
       sal - LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS diff
FROM emp;

4. 百分位數統計

SELECT deptno,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal) OVER (PARTITION BY deptno) AS median_sal
FROM emp;

效能與避坑指南

• ✅ 分析函數單掃描完成，效能遠優於自聯結/子查詢/標量子查詢
• ✅ 善用 PARTITION BY 縮小排序範圍，減少記憶體/臨時表空間壓力
• ❌ 避免在 ORDER BY 使用非決定性函數 (如 DBMS_RANDOM)
• ❌ 外層查詢再加 ORDER BY 可能與窗口排序衝突，導致結果難以預期
• 🔍 EXPLAIN PLAN 觀察 WINDOW SORT / WINDOW NOSORT 操作

OVER() 將「群組計算」從「減少行數的 GROUP BY」解放為「保留行數的逐行附加」，是 SQL 從集合思維進階到窗口思維的關鍵橋樑。掌握 PARTITION BY、ORDER BY、窗口框架三要素，即可解決 90% 的報表統計、排名、環比、去重、分頁等進階需求。