Oracle 分析函數深度解析：RANK、DENSE_RANK、ROW_NUMBER 差異與 NULL 排序處理

測試資料準備

CREATE TABLE EMPLOYEEINFO (
  CODE NUMBER(3) PRIMARY KEY,
  EMPLOYEENAME VARCHAR2(15),
  DEPARTMENTID NUMBER(3),
  SALARY NUMBER(7,2)
);

-- 插入測試資料 (含相同薪資、NULL 值)

三大排序函數對比查詢

SELECT EMPLOYEENAME, SALARY,
  RANK() OVER (ORDER BY SALARY DESC) "RANK",
  DENSE_RANK() OVER (ORDER BY SALARY DESC) "DENSE_RANK",
  ROW_NUMBER() OVER (ORDER BY SALARY DESC) "ROW_NUMBER"
FROM EMPLOYEEINFO;

核心差異總結

分組排序 (PARTITION BY)

SELECT DEPARTMENTID, EMPLOYEENAME, SALARY,
  RANK() OVER (PARTITION BY DEPARTMENTID ORDER BY SALARY DESC) "RANK",
  DENSE_RANK() OVER (PARTITION BY DEPARTMENTID ORDER BY SALARY DESC) "DENSE_RANK",
  ROW_NUMBER() OVER (PARTITION BY DEPARTMENTID ORDER BY SALARY DESC) "ROW_NUMBER"
FROM EMPLOYEEINFO;

各部門內獨立計算排名，互不干擾。

NULL 值排序陷阱與解決

預設行為：ORDER BY SALARY DESC 會將 NULL 排在最前面 (視為最大值)，這通常不符合業務預期。

解決方案：明確指定 NULLS LAST 或 NULLS FIRST。

SELECT EMPLOYEENAME, SALARY,
  RANK() OVER (ORDER BY SALARY DESC NULLS LAST) "RANK",
  DENSE_RANK() OVER (ORDER BY SALARY DESC NULLS LAST) "DENSE_RANK",
  ROW_NUMBER() OVER (ORDER BY SALARY DESC NULLS LAST) "ROW_NUMBER"
FROM EMPLOYEEINFO;

加上 NULLS LAST 後，NULL 薪資員工正確排在最後。

實戰建議

• 分頁查詢**務必使用 ROW_NUMBER** 確保結果確定性。
• 報表排名視業務決定 RANK (跳號) 或 DENSE_RANK (不跳號)。
• 所有 ORDER BY 子句**建議顯式寫出 NULLS FIRST/LAST**，避免跨版本/參數差異導致結果不一致。