您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 四大排名函數深度解析：ROW_NUMBER、RANK、DENSE_RANK、NTILE 與實戰效能案例

編輯：SQL Server 2005

一、四大排名函數速查表

函數	用途	平局處理	編號連續性	典型場景
ROW_NUMBER()	唯一連續序號	強制依序編號（不確定順序）	是	分頁、刪除重複、唯一識別碼
RANK()	競賽式排名	同分同名，下一名跳過間隙	否	銷售排名、考試名次
DENSE_RANK()	密集排名	同分同名，下一名緊接	是	分級統計、薪資等級
NTILE(n)	等寬分桶	依序平均分配	桶編號連續	直方圖、客戶分級、百分位數

二、範例資料準備

CREATE TABLE t_table (field1 INT, field2 VARCHAR(10));
INSERT INTO t_table VALUES
(1,'A'), (2,'B'), (3,'C'), (3,'D'), (3,'E'), (4,'F');

三、逐函數實戰示範

1. ROW_NUMBER：分頁查詢黃金搭檔

-- 取第 2~3 筆（依 field1 排序）
WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY field1) AS rn, *
    FROM t_table
)
SELECT * FROM cte WHERE rn BETWEEN 2 AND 3 ORDER BY field1;

關鍵技巧：OVER 子句的 ORDER BY 與外層 ORDER BY **必須一致**，否則序號可能不連續導致分頁錯漏。

2. RANK：Olympic 名次邏輯

SELECT RANK() OVER (ORDER BY field1) AS rk, * FROM t_table ORDER BY field1;

結果：field1=3 的三列同為名次 3，下一列 field1=4 名次跳為 6（間隙 = 並列筆數 - 1）。

3. DENSE_RANK：無間隙密集排名

SELECT DENSE_RANK() OVER (ORDER BY field1) AS drk, * FROM t_table ORDER BY field1;

同分同名次 3，下一列名次為 4（連續）。

4. NTILE(4)：四分位數分桶

SELECT NTILE(4) OVER (ORDER BY field1) AS bucket, * FROM t_table;

6 列分 4 桶 → 前 2 桶各 2 列，後 2 桶各 1 列（桶大小差異 ≤ 1）。

四、進階應用：Speaker Statistics 完整案例

資料：11 位講者，三大軌道，分數 1~9。

4.1 ROW_NUMBER：分頁與確定性排序

-- 不確定性：分數相同時順序隨機
SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, speaker, track, score
FROM SpeakerStats ORDER BY score DESC;

-- 確定性：多層排序鍵
SELECT ROW_NUMBER() OVER (ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker) AS rn,
       speaker, track, score, pctfilledevals, numsessions
FROM SpeakerStats ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker;

4.2 分頁最佳化：物化 + 叢集索引

-- 一次物化
SELECT ROW_NUMBER() OVER (ORDER BY score DESC, speaker) AS rn, *
INTO #SpeakerStatsRN FROM SpeakerStats;
CREATE UNIQUE CLUSTERED INDEX idx_rn ON #SpeakerStatsRN(rn);

-- 後續每頁僅掃描目標範圍
DECLARE @pagenum INT = 2, @pagesize INT = 3;
SELECT rn, speaker, track, score
FROM #SpeakerStatsRN
WHERE rn BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY score DESC, speaker;

4.3 PARTITION BY：各軌道內部排名

SELECT track,
       ROW_NUMBER() OVER (PARTITION BY track ORDER BY score DESC, speaker) AS pos,
       speaker, score
FROM SpeakerStats
ORDER BY track, pos;

4.4 RANK vs DENSE_RANK 並列比較

SELECT speaker, track, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK()       OVER (ORDER BY score DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM SpeakerStats ORDER BY score DESC;

觀察 score=9 三人：rn=1,2,3、rk=1,1,1、drk=1,1,1；score=8 三人：rn=4,5,6、rk=4,4,4、drk=2,2,2。

4.5 NTILE(3)：三級評等

SELECT speaker, track, score,
       CASE NTILE(3) OVER (ORDER BY score DESC)
            WHEN 1 THEN 'High' WHEN 2 THEN 'Medium' WHEN 3 THEN 'Low'
       END AS score_category
FROM SpeakerStats ORDER BY track, speaker;

11 列分 3 桶 → 4, 4, 3 分配。