您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 分析函數 RANK 與 DENSE_RANK 實戰詳解：Top-N 查詢與並列排名處理

編輯：Oracle 資料庫基礎

前言

RANK() 與 DENSE_RANK() 是 Oracle 最常用的分析函數，解決「各群組前 N 筆」、「並列排名不跳號/跳號」等經典需求。本文以實戰案例深度解析語法、差異與效能。

一、語法結構


RANK() OVER ([PARTITION BY partition_expr] ORDER BY order_expr [ASC|DESC] [NULLS FIRST|LAST])
DENSE_RANK() OVER ([PARTITION BY partition_expr] ORDER BY order_expr [ASC|DESC] [NULLS FIRST|LAST])

關鍵字句

PARTITION BY：分群 (如依科目、部門)，各群獨立排名
ORDER BY：排序依據，決定名次高低
NULLS FIRST/LAST：NULL 值排序位置 (預設 ASC 時 NULLS LAST，DESC 時 NULLS FIRST)

二、核心差異：跳號 vs 不跳號

場景	分數	RANK	DENSE_RANK
第 1 名	100	1	1
第 2 名 (並列)	90	2	2
第 2 名 (並列)	90	2	2
第 4 名	80	4 (跳過 3)	3 (不跳號)
第 5 名	70	5	4

記憶口訣

RANK = 競賽排名：並列金牌 → 下一名是銅牌 (跳過銀牌)
DENSE_RANK = 密集排名：並列第 2 → 下一名緊接第 3

三、實戰案例 1：各科目前 3 高分

測試資料


CREATE TABLE scores (subject VARCHAR2(10), mark NUMBER);
INSERT INTO scores VALUES ('數學',80);
INSERT INTO scores VALUES ('語文',70);
INSERT INTO scores VALUES ('數學',90);
INSERT INTO scores VALUES ('數學',60);
INSERT INTO scores VALUES ('數學',100);
INSERT INTO scores VALUES ('語文',88);
INSERT INTO scores VALUES ('語文',65);
INSERT INTO scores VALUES ('語文',77);

查詢：各科目前 3 名


SELECT subject, mark, rnk
FROM (
  SELECT subject, mark,
         RANK() OVER (PARTITION BY subject ORDER BY mark DESC) AS rnk
  FROM scores
)
WHERE rnk <= 3
ORDER BY subject, rnk;

結果

SUBJECT  MARK  RNK
數學     100   1
數學     90    2
數學     80    3
語文     88    1
語文     77    2
語文     70    3

四、實戰案例 2：並列排名行為對比

測試資料


CREATE TABLE test3 (a VARCHAR2(1), b VARCHAR2(10), c VARCHAR2(10));
INSERT INTO test3 VALUES ('a','liu','wang');
INSERT INTO test3 VALUES ('a','jin','shu');
INSERT INTO test3 VALUES ('a','cai','kai');
INSERT INTO test3 VALUES ('b','yang','du');
INSERT INTO test3 VALUES ('b','lin','ying');
INSERT INTO test3 VALUES ('b','yao','cai');
INSERT INTO test3 VALUES ('b','yang','99');  -- yang 並列

RANK 結果


SELECT a, b, c, RANK() OVER (PARTITION BY a ORDER BY b) AS rnk
FROM test3;

A  B       C       RNK
a  cai     kai     1
a  jin     shu     2
a  liu     wang    3
b  lin     ying    1
b  yang    du      2
b  yang    99      2  -- 並列
b  yao     cai     4  -- 跳過 3

DENSE_RANK 結果


SELECT a, b, c, DENSE_RANK() OVER (PARTITION BY a ORDER BY b) AS drnk
FROM test3;

A  B       C       DRNK
a  cai     kai     1
a  jin     shu     2
a  liu     wang    3
b  lin     ying    1
b  yang    du      2
b  yang    99      2  -- 並列
b  yao     cai     3  -- 不跳號

五、進階應用：ROW_NUMBER 精確去重

若需**嚴格不並列、單一行**，使用 ROW_NUMBER()：


SELECT * FROM (
  SELECT subject, mark,
         ROW_NUMBER() OVER (PARTITION BY subject ORDER BY mark DESC, student_id) AS rn
  FROM scores
)
WHERE rn = 1;  -- 每科目最高分唯一一筆 (含 Tie-breaker)

六、效能考量與優化

因素	影響	建議
PARTITION BY 欄位	決定排序範圍，分區越多排序越小	確保分區欄位有索引或為小基數
ORDER BY 欄位	排序鍵，需排序運算	建立複合索引 (partition_col, order_col) 避免排序
資料量	大量資料排序消耗 PGA/Temp	適當增加 PGA_AGGREGATE_TARGET，或分批處理
窗口框架	預設 RANGE UNBOUNDED PRECEDING	RANK/DENSE_RANK 不支援自訂窗口，ROW_NUMBER 可用 ROWS

索引消除排序範例


-- 建立索引支援 PARTITION BY + ORDER BY
CREATE INDEX idx_scores_subj_mark ON scores(subject, mark DESC);

-- 查詢計畫將顯示 INDEX FULL SCAN 而非 SORT ORDER BY
EXPLAIN PLAN FOR
SELECT * FROM (
  SELECT subject, mark, RANK() OVER (PARTITION BY subject ORDER BY mark DESC) rnk
  FROM scores
) WHERE rnk <= 3;