Oracle 分析函數實戰筆記 (二)：RANK() vs DENSE_RANK() 同分處理差異深度解析

同場景、不同語意

沿用 SCOTT.EMP 資料，需求調整為：**取得各部門薪資前 3 名 (含同分)**。這時「排名」語意超越「行號」。

RANK() — 競爭排名 (Olympic Ranking)

• 同分 **同名次**。
• **下一名次跳過同分人數** (1, 1, 3, 4...)。
• 適用：獎項發放、排名顯示「並列第幾」。

查詢與結果

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

DEPTNO RK ENAME      SAL
------ -- ------ ------
    10  1 KING      5000
        2 CLARK     2450
        3 MILLER    1300
    20  1 SCOTT     3000
        1 FORD      3000   -- 並列第 1
        3 JONES     2975   -- 直接跳到第 3
    30  1 BLAKE     2850
        2 ALLEN     1600
        3 TURNER    1500

DENSE_RANK() — 緊湊排名 (Dense Ranking)

• 同分 **同名次**。
• **下一名次不跳號** (1, 1, 2, 3...)。
• 適用：分級統計、分頁顯示「前 N 個等級」。

查詢與結果

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

DEPTNO DRK ENAME      SAL
------ --- ------ ------
    10  1 KING      5000
        2 CLARK     2450
        3 MILLER    1300
    20  1 SCOTT     3000
        1 FORD      3000
        2 JONES     2975   -- 緊接第 2
        3 ADAMS     1100   -- 第 3 等級
    30  1 BLAKE     2850
        2 ALLEN     1600
        3 TURNER    1500

三大排名函數對比總結表

進階陷阱：WHERE 子句無法直接使用分析函數

-- 錯誤：ORA-00934: group function is not allowed here
SELECT deptno, ename, sal
FROM emp
WHERE RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) <= 3;

-- 必須用內層查詢 (Inline View) 或 WITH 子句
WITH ranked AS (
    SELECT deptno, ename, sal,
           RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rk
    FROM emp
)
SELECT * FROM ranked WHERE rk <= 3;

效能與最佳化

• 三者執行計畫通常相同：WINDOW SORT + FILTER。
• 大數據量建議：PARTITION BY 欄位建立索引、調大 PGA、考慮 PARALLEL。
• 12c+ FETCH FIRST 3 ROWS WITH TIES 等價於 RANK() <= 3，語法更簡潔。

結語

**「行號」用 ROW_NUMBER，「排名」分 RANK/DENSE_RANK**。業務若要求「前 3 名含同分」，RANK 會漏資料 (跳號導致第 4 名擠進前 3)，**DENSE_RANK 才是正確選擇**。下一篇將探討 LEAD/LAG、FIRST_VALUE/LAST_VALUE 等相對定位函數。