SQL Server 2005 排序視窗函數全解析：ROW_NUMBER、RANK、DENSE_RANK、NTILE 差異與實戰

前言：排序函數家族登場

SQL Server 2005 引入四大排序視窗函數，皆需搭配 OVER (ORDER BY ...) 子句使用，支援 PARTITION BY 分組。本文以範例資料逐一剖析差異、語法、執行邏輯與實戰應用。

範例資料準備

CREATE TABLE t_table (field1 INT, field2 VARCHAR(20));
INSERT INTO t_table VALUES 
(10, 'A'), (20, 'B'), (30, 'C'), (30, 'D'), (30, 'E'), (40, 'F');

資料內容：

圖 1：原始資料

一、ROW_NUMBER()：嚴格遞增序號

SELECT ROW_NUMBER() OVER (ORDER BY field1) AS rn, * FROM t_table;

圖 2：ROW_NUMBER 結果 (1,2,3,4,5,6)

關鍵特性

• **無視相同值**，嚴格依排序順序產生 1,2,3... 不重複、不跳號。
• OVER (ORDER BY ...) 中的排序**獨立於外層查詢 ORDER BY**，可不同：

SELECT ROW_NUMBER() OVER (ORDER BY field2 DESC) AS rn, * 
FROM t_table ORDER BY field1 DESC;

圖 3：OVER 排序與輸出排序分離

實戰：分頁查詢 (配合 CTE)

WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY field1) AS rn, *
    FROM t_table
)
SELECT * FROM cte WHERE rn > 1 AND rn < 4 ORDER BY field1; -- 第 2-3 筆

圖 4：分頁結果

⚠ 分頁時 **OVER 排序必須與外層 ORDER BY 一致**，否則序號可能不連續導致資料遺漏/重複。

對比：傳統 TOP 雙重反轉法 (SQL 2000 相容)

SELECT * FROM (
    SELECT TOP 2 * FROM (
        SELECT TOP 3 * FROM t_table ORDER BY field1
    ) a ORDER BY field1 DESC
) b ORDER BY field1;

圖 5：傳統法結果 (無序號欄位)

二、RANK()：相同值同名次，後續跳號

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

圖 6：RANK 結果 (1,2,3,3,3,6) —— 第 4 筆跳為 6

邏輯

• 相同 ORDER BY 值 → 同名次 (並列)。
• 下一名次 = **目前已處理列數 + 1** (即跳過並列數量)。
• 適用：競賽排名、成績排名 (並列第 3 名，下一名為第 6 名)。

三、DENSE_RANK()：相同值同名次，後續不跳號

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

圖 7：DENSE_RANK 結果 (1,2,3,3,3,4) —— 第 4 筆為 4

差異對比

四、NTILE(n)：將結果集等分為 n 個桶

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

圖 8：NTILE(4) 結果 (6 筆資料分 4 桶 → 2,2,1,1)

分配演算法 (兩大原則)

1. **桶號越小，分配筆數 ≥ 桶號越大** (遞減不增)。
2. **筆數要么全同，要么從某桶開始後續全同** (階梯狀)。

-- 演算法邏輯 (Pseudocode)
IF (TotalRows % Buckets == 0)
    EachBucket = TotalRows / Buckets;
ELSE
    Base = TotalRows / Buckets + 1;           -- 前桶筆數 (較大)
    n = 1;
    WHILE ((TotalRows - Base*n) % (Buckets - n) != 0) n++;
    Remainder = (TotalRows - Base*n) / (Buckets - n); -- 後桶筆數 (較小)
    前 n 桶各 Base 筆，後續各 Remainder 筆;

驗證範例

• 59 筆 / 5 桶 → Base=12, n=4 → 前 4 桶 12 筆，第 5 桶 11 筆。
• 53 筆 / 5 桶 → Base=11, n=3 → 前 3 桶 11 筆，後 2 桶 10 筆。
• 本例 6 筆 / 4 桶 → Base=2, n=2 → 前 2 桶 2 筆，後 2 桶 1 筆。

實戰：百分位分群、資料取樣

-- 將客戶依消費金額分為 10 等分 (十分位數)
SELECT CustomerID, TotalAmount,
       NTILE(10) OVER (ORDER BY TotalAmount) AS Decile
FROM CustomerSales;

-- 取前 10% 高價值客戶 (Decile = 10)
WITH cte AS (
    SELECT *, NTILE(10) OVER (ORDER BY TotalAmount) AS Decile
    FROM CustomerSales
)
SELECT * FROM cte WHERE Decile = 10;

綜合比較與選型指南

效能提示

• 排序視窗函數需要 **Sort 運算子**，大量資料需確保 ORDER BY 欄位有索引支援 (覆蓋索引最佳)。
• PARTITION BY 會增加 Spools/Hash Match 成本，分組鍵選擇性高時效能較好。
• 避免在視窗函數外層再包裹子查詢導致重複排序，善用 CTE/衍生表結構。

結語

四大排序函數各有其語義精確對應的業務場景：**ROW_NUMBER 做分頁、RANK 做競賽排名、DENSE_RANK 做等級標籤、NTILE 做分位分群**。掌握「並列是否跳號」、「序號是否連續」、「桶分配演算法」三大核心差異，即可精準選型、撰寫高可讀、高效能的 T-SQL。配合 PARTITION BY 分組與 OVER 獨立排序特性，更能處理複雜的多維度排名分析需求。