SQL Server 2005 排名函數詳解：ROW_NUMBER、RANK、DENSE_RANK、NTILE 語法與執行原理

一、 排名函數語法通則

ranking_function OVER ([PARTITION BY partition_column_list] ORDER BY order_column_list)

• 僅能出現在 SELECT 與 ORDER BY 子句中。
• PARTITION BY 將結果集切分為獨立分區，各分區內獨立計算排名。
• ORDER BY 決定排名依據，支援多欄位排序。

二、 ROW_NUMBER：連續唯一序號

為每列分配 1, 2, 3… 不重複編號。執行邏輯：先依 PARTITION BY 分區 → 再依 ORDER BY 排序 → 最後依序編號。

SELECT empid, qty,
       ROW_NUMBER() OVER (ORDER BY qty, empid) AS rn
FROM dbo.Sales;

三、 SQL 2005 前的替代方案（效能對比）

1. 集合式子查詢（SET-BASED）

SELECT empid, qty,
       (SELECT COUNT(*) FROM dbo.Sales S2
        WHERE S2.qty < S1.qty
           OR (S2.qty = S1.qty AND S2.empid <= S1.empid)) AS rn
FROM dbo.Sales S1
ORDER BY qty, empid;

相關子查詢導致 O(N²) 掃描，大資料量極慢。

2. IDENTITY 插入暫存表

SELECT empid, qty, IDENTITY(INT, 1, 1) AS rn
INTO #SalesRN FROM dbo.Sales ORDER BY qty, empid;

需額外 I/O 與暫存表管理，不如內建函數優雅高效。

四、 RANK 與 DENSE_RANK：平局處理的兩種哲學

假設 qty 有重複值：

• RANK：名次 = 「比自己高的人數」+ 1，產生間隙（Olympic 排名）。
• DENSE_RANK：名次 = 「比自己高的不同分數數」+ 1，無間隙（密集排名）。

五、 NTILE：等寬分桶演算法

NTILE(buckets) OVER (ORDER BY ...)

將排序後資料平均分配至 buckets 個桶，桶編號 1..N。分配規則：

1. 基礎大小 = CEILING(總列數 / 桶數)。
2. 前 餘數 個桶獲得基礎大小，其餘桶獲得 基礎大小 - 1。

例：11 列分 3 桶 → 4, 4, 3。

六、 PARTITION BY 進階應用：分組內排名

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

各 track (DB/Dev/Sys) 獨立編號，適合「各組前 N 名」查詢。

七、 效能優勢總結

• 單次掃描 + 排序即可完成所有排名計算，線性擴展。
• 優化器可利用索引有序掃描避免額外排序。
• 語法簡潔、可讀性高，取代複雜的相關子查詢或暫存表方案。