Oracle 分析函數實戰筆記 (一)：ROW_NUMBER() 精準取得各群組 Top-N

實驗環境

• OS: Windows 2000 Server
• DB: Oracle 8.1.7
• Schema: SCOTT (EMP 表)

基礎資料：依部門、薪資遞減排序

SQL> BREAK ON DEPTNO SKIP 1
SQL> SELECT deptno, ename, sal
  2  FROM emp
  3  ORDER BY deptno, sal DESC;

DEPTNO ENAME             SAL
------ ---------- ----------
    10 KING             5000
       CLARK            2450
       MILLER           1300
    20 SCOTT            3000
       FORD             3000
       JONES            2975
       ADAMS            1100
       SMITH             800
    30 BLAKE            2850
       ALLEN            1600
       TURNER           1500
       WARD             1250
       MARTIN           1250
       JAMES             950

需求：取得各部門薪資最高的前 3 名員工

ROW_NUMBER() 寫法

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

結果

DEPTNO RN ENAME      SAL
------ -- ------ ------
    10  1 KING      5000
        2 CLARK     2450
        3 MILLER    1300
    20  1 SCOTT     3000
        2 FORD      3000
        3 JONES     2975
    30  1 BLAKE     2850
        2 ALLEN     1600
        3 TURNER    1500

核心觀念釐清

ROW_NUMBER() 產生的是「行號」，而非「排名」。

觀察部門 20：SCOTT 與 FORD 薪資同為 3000，但 RN 分別為 1、2。這代表：

• **嚴格遞增**，不會因同值而跳號。
• **結果具確定性**，但同薪資誰排前依 ORDER BY 次序 (若無次序則不穩定)。
• 若需「同分同名次」，請改用 RANK() 或 DENSE_RANK() (見下一篇)。

擴展應用：動態 Top-N 參數化

-- 將 3 參數化，搭配 Bind Variable
VARIABLE v_top NUMBER
EXEC :v_top := 3;

SELECT deptno, ename, sal
FROM (
    SELECT deptno, ename, sal,
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) rn
    FROM emp
)
WHERE rn <= :v_top;

效能提示

• PARTITION BY 欄位若有索引，Oracle 可避免全表排序 (WINDOW SORT PUSHED RANK)。
• 大數據量時，確保 PGA_AGGREGATE_TARGET 足夠，避免排序溢寫 Temp 表空間。
• 12c+ 可用 FETCH FIRST N ROWS WITH TIES 簡化語法，但 ROW_NUMBER 相容性最佳。

結語

ROW_NUMBER() 是 **精準取得各群組 Top-N** 的利器，語意清晰：分區 → 排序 → 編號 → 過濾。掌握其「行號非排名」特性，避免業務誤判。下一篇將深入 RANK() / DENSE_RANK() 的同分處理差異。