Oracle OVER() 分析函數實戰：累加、分組統計與佔比計算

核心概念

OVER() 子句將彙總函數（SUM、AVG、COUNT 等）轉為分析函數，不再縮減行數，而是為每一行計算基於「視窗」的彙總值。

語法結構

聚合函數(...) OVER (
  [PARTITION BY 分組欄位]
  [ORDER BY 排序欄位]
  [ROWS/RANGE BETWEEN ...]  -- 視窗框架，進階用法
)

範例資料：scott.emp 表

14 筆員工資料，含 deptno、ename、sal。

案例一：全表累加與佔比

SELECT deptno, ename, sal,
       SUM(sal) OVER (ORDER BY ename)        AS running_total,
       SUM(sal) OVER ()                      AS grand_total,
       ROUND(100 * sal / SUM(sal) OVER (), 2) AS pct_of_total
FROM emp;

關鍵差異：

• OVER (ORDER BY ename)：依名稱排序後逐行累加（Running Total）。
• OVER ()：無排序、無分組，等同全表總和（每行相同值）。

案例二：分組累加、分組總與分組佔比

SELECT deptno, ename, sal,
       SUM(sal) OVER (PARTITION BY deptno ORDER BY ename) AS dept_running,
       SUM(sal) OVER (PARTITION BY deptno)                AS dept_total,
       ROUND(100 * sal / SUM(sal) OVER (PARTITION BY deptno), 2) AS dept_pct,
       SUM(sal) OVER (ORDER BY deptno, ename)             AS global_running,
       SUM(sal) OVER ()                                   AS grand_total,
       ROUND(100 * sal / SUM(sal) OVER (), 2)             AS grand_pct
FROM emp;

結果解讀

常見應用場景

• 累計銷售額：SUM(amt) OVER (PARTITION BY shop_id ORDER BY sale_date)
• Top-N 分析：搭配 ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sal DESC) 取各部門前 3 高薪。
• 同比/環比：LAG(sal) OVER (PARTITION BY empno ORDER BY month) 取上月薪資。
• 移動平均：AVG(sal) OVER (ORDER BY ename ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

效能提示

• 分析函數通常比自連接或子查詢效能優，因只需單次掃描+排序。
• 大數據量時，PARTITION BY 欄位若有索引可避免排序開銷。
• 避免在視窗框架使用 RANGE 搭配數值/日期型別，可能觸發昂貴的排序操作；優先用 ROWS。