SQL Server 2005 排名函數完整解析：ROW_NUMBER, RANK, DENSE_RANK, NTILE

前言

SQL Server 2005 引入四個排名視窗函數，廣泛應用於分頁、排行榜、分組排序、資料分桶等場景。本文透過實例詳細解析其差異與適用場景。

範例資料準備

CREATE TABLE Sales (
    empid VARCHAR(10) PRIMARY KEY,
    mgrid VARCHAR(10),
    qty INT
)
INSERT Sales VALUES
('A','Z',300), ('B','X',100), ('C','X',200), ('D','Y',200),
('E','Z',250), ('F','Z',300), ('G','X',100), ('H','Y',150),
('I','X',250), ('J','Z',100), ('K','Y',200)

CREATE INDEX idx_qty_empid ON Sales(qty, empid)
CREATE INDEX idx_mgrid_qty_empid ON Sales(mgrid, qty, empid)

四大排名函數詳細比較

1. ROW_NUMBER() - 連續唯一序號

SELECT empid, qty,
       ROW_NUMBER() OVER (ORDER BY qty) AS RowNum
FROM Sales ORDER BY qty

• 無論 ORDER BY 欄位是否重複，皆產生 1,2,3... 連續不重複序號。
• 若需確定性排序，ORDER BY 應包含唯一鍵（如 qty, empid）。
• 支援 PARTITION BY 分組計算。

2. RANK() - 競爭式排名（有間隔）

SELECT empid, qty,
       RANK() OVER (ORDER BY qty) AS rnk
FROM Sales ORDER BY qty

• 相同值給相同排名，下一名次跳過已佔用的數字（如 1,1,3,4...）。
• 適用於「名次」語意：並列第一，第三名才是第三名。

3. DENSE_RANK() - 緊湊式排名（無間隔）

SELECT empid, qty,
       DENSE_RANK() OVER (ORDER BY qty) AS drnk
FROM Sales ORDER BY qty

• 相同值給相同排名，下一名次緊接著遞增（如 1,1,2,3...）。
• 適用於「等級/分級」語意：分數相同同級，下一級為第二級。

4. NTILE(n) - 分桶/分組

SELECT empid, qty,
       NTILE(3) OVER (ORDER BY qty, empid) AS bucket
FROM Sales ORDER BY qty, empid

• 將結果集盡量平均分成 n 個桶，桶編號從 1 開始。
• 前面的桶筆數 ≥ 後面的桶，差異至多 1 筆。
• 常用於分位數分析、直方圖、負載均衡分批。

綜合對比範例

SELECT empid, qty,
       ROW_NUMBER() OVER (ORDER BY qty) AS rn,
       RANK() OVER (ORDER BY qty) AS rk,
       DENSE_RANK() OVER (ORDER BY qty) AS drk,
       NTILE(4) OVER (ORDER BY qty) AS nt
FROM Sales ORDER BY qty

輸出片段（qty 由小到大）：

PARTITION BY 分組排名

SELECT mgrid, empid, qty,
       ROW_NUMBER() OVER (PARTITION BY mgrid ORDER BY qty, empid) AS RowNum
FROM Sales ORDER BY mgrid, qty

每個 mgrid 群組內獨立計算序號，適用於「各部門前 N 名」等需求。

效能與索引建議

• 最佳索引：PARTITION BY 欄位, ORDER BY 欄位（含覆蓋欄位）。
• 排名函數僅掃描資料一次，效能遠優於 SQL 2000 時代的相關子查詢或遊標法。
• 避免在大型結果集上不必要的排序，必要時使用 TOP 限制範圍。

SQL 2000 時代的替代方案（僅供參考）

-- 相關子查詢法（效能差，O(N^2)）
SELECT empid, (SELECT COUNT(*) FROM Sales s2 WHERE s2.empid <= s1.empid) AS rn
FROM Sales s1 ORDER BY empid

-- IDENTITY 插入暫存表法
SELECT empid, qty, IDENTITY(int,1,1) AS rn INTO #tmp FROM Sales ORDER BY qty