Oracle 分析函數：ROW_NUMBER() 與 RANK() 指南

Oracle 分析函數：ROW_NUMBER() 與 RANK() 完全指南

大家好，我是Jayson。在上一篇我們聊了 PL/SQL 游標，今天我們要進入 SQL 分析函數的世界，專注於兩個超級實用的工具：ROW_NUMBER() 和 RANK()。這兩個函數搭配 PARTITION BY 和 ORDER BY，可以輕鬆完成「分組排序」、「取各組最大/最小值記錄」等複雜任務，是資料分析與報表製作的必備技能。

一、什麼是分析函數？

分析函數（Analytic Functions），也稱為視窗函數（Window Functions），允許我們在不改變查詢結果列數的情況下，對每一行計算一個基於某個視窗範圍的聚合值或排序值。簡單說，就是可以在每一行的旁邊加上排名、累計總和、移動平均等資訊。

ROW_NUMBER() 和 RANK() 是最常用的兩種排名函數。

二、ROW_NUMBER() 基本語法與用法

ROW_NUMBER() 會為查詢結果集中的每一行指派一個唯一的序號，序號從 1 開始，按照 ORDER BY 指定的順序遞增。如果搭配 PARTITION BY，則會在每個分區內獨立編號。

基本語法：

ROW_NUMBER() OVER (
    [PARTITION BY 分欄位1, 分欄位2, ...]
    ORDER BY 排序欄位1 [ASC|DESC], 排序欄位2 ...
)

範例 1：簡單的 ROW_NUMBER()

SELECT A, B, C,
       ROW_NUMBER() OVER (ORDER BY B) AS row_num
FROM Table_ABC;

這個查詢會將 Table_ABC 的資料依照 B 欄位排序，然後從 1 開始依序給每一行一個編號。注意，這裡沒有 PARTITION BY，所以整個結果集視為一個大區塊。

範例 2：使用 PARTITION BY 的分組編號

SELECT A, B, C,
       ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS row_num
FROM Table_ABC;

這是您提供的範例。它會先以 A 欄位分組（Partition），然後在每個分組內依照 B 欄位排序，再給每個分組內的每一行從 1 開始編號。也就是說，A 值相同的組別，會各自有自己的 1, 2, 3, ...。

如何用 ROW_NUMBER() 取出每組的最大/最小值記錄？

假設我們想取得每個 A 分組中，B 值最小（即排序最前面）的那一整行記錄。可以利用 ROW_NUMBER() 產生組內序號，然後在外層查詢過濾序號為 1 的行。

SELECT A, B, C
FROM (
    SELECT A, B, C,
           ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) AS rn
    FROM Table_ABC
) WHERE rn = 1;

如果想取 B 值最大的記錄，只要將 ORDER BY B 改為 ORDER BY B DESC 即可。同樣，在外層抓取 rn = 1 的行。

三、RANK() 與 DENSE_RANK() 的區別

RANK() 也是排名函數，但它處理平局（相同值）的方式與 ROW_NUMBER() 不同。

• ROW_NUMBER()：遇到相同排序值時，會隨機（或依資料庫內部順序）決定先後，每個編號都是唯一且連續的。例如：1, 2, 3, 4。
• RANK()：相同排序值會得到相同排名，且排名會跳號。例如：1, 1, 3, 4。
• DENSE_RANK()：相同排序值得到相同排名，但排名不跳號。例如：1, 1, 2, 3。

範例 3：RANK() 的基本用法

SELECT A, B, C,
       RANK() OVER (ORDER BY C) AS rank_num
FROM Table_ABC;

這是您提供的第二個範例。它會根據 C 欄位排序後給出排名，如果 C 值相同，則會得到相同排名，且下一排名會跳號。

舉個更具體的資料來看：

-- 假設 Table_ABC 資料如下：
-- A    B    C
-- X    10   100
-- X    20   100
-- Y    30   200
-- Y    40   150

SELECT A, B, C,
       ROW_NUMBER() OVER (ORDER BY C) AS row_num,
       RANK()       OVER (ORDER BY C) AS rank_num,
       DENSE_RANK() OVER (ORDER BY C) AS dense_rank_num
FROM Table_ABC;
-- 結果：
-- A   B   C   row_num  rank_num  dense_rank_num
-- X  10 100    1        1         1
-- X  20 100    2        1         1
-- Y  40 150    3        3         2
-- Y  30 200    4        4         3

可以看到，C 值同為 100 的兩筆在 rank_num 中都是第 1 名，但下一名直接跳到 3（跳號）；dense_rank 則是 1, 1, 2, 3，不跳號；row_number 則硬是給出 1, 2, 3, 4，沒有平局概念。

如何用 RANK() 取得各組中某欄位排名前 N 的記錄？

例如，我們想在每個 A 分組內，根據 C 欄位排名，取出每組排名第一的記錄（可能有多筆，因為平局）。這時用 RANK() 最合適。

SELECT A, B, C
FROM (
    SELECT A, B, C,
           RANK() OVER (PARTITION BY A ORDER BY C) AS rk
    FROM Table_ABC
) WHERE rk = 1;

這個查詢會傳回每個 A 分組中 C 值最小的所有記錄（若有多筆最小值）。

四、實際應用場景

1. 分組去重（保留每組最新/最舊的一筆）

這是 ROW_NUMBER() 最常見的用途。例如訂單表，每個客戶可能有多筆訂單，想取出每個客戶的最新一筆訂單。用 PARTITION BY 客戶ID ORDER BY 訂單日期 DESC，然後取 rn = 1。

2. 排行榜（Top N by Group）

使用 RANK() 或 DENSE_RANK() 可以列出各部門薪資最高的員工（允許並列）。

3. 分頁查詢（Pagination）

搭配 ROW_NUMBER() 可以在整個結果集上產生序號，然後在外層 WHERE row_num BETWEEN 11 AND 20 來實現分頁。

五、注意事項與效能提示

• WHERE 子句的限制：不能在同一個查詢層級中直接對分析函數的結果使用 WHERE 過濾，必須將分析函數放在子查詢或 CTE 中，然後在外層過濾。因為分析函數是在 WHERE、GROUP BY 等子句之後才計算的。
• 排序穩定性：當 ORDER BY 欄位有重複值時，ROW_NUMBER() 的指派順序是不確定的（non‑deterministic）。如果一定要指定順序，可以加入更多排序欄位以確保唯一性。
• 效能：分析函數通常能高效處理，但若 PARTITION BY 的欄位沒有索引，可能會導致大量排序操作。可視需要建立合適的索引。

六、總結

今天我們介紹了 Oracle 中兩個強大的分析函數：

• ROW_NUMBER()：產生唯一連續序號，適合用來分組取前 N 筆或分頁。
• RANK() / DENSE_RANK()：處理平局排名，RANK 跳號，DENSE_RANK 不跳號，適合需要並列排名的場景。
• 搭配 PARTITION BY 可以實現分組排序，搭配外層查詢過濾就能輕鬆取得每組最大/最小值記錄。

這些函數寫法簡潔，執行效率高，是 SQL 進階使用者的必備利器。希望這篇文章能幫助你更靈活地處理資料排序與分組需求！

---

參考資料：Oracle Database SQL Language Reference – Analytic Functions

下回預告：我們將探討 LAG() 與 LEAD() 這些「跨列參照」的分析函數，敬請期待！

---

本文由［Jayson Chan］原創，轉載請註明出處: https://freecodeinfosite.blogspot.com/