SQL Server 窗口函數 ROW_NUMBER() 與 PARTITION BY 實戰詳解：分組排序、去重、分頁、Top-N 問題一次搞定

核心概念

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 是解決 **「分組內排序編號」** 最強利器。與 GROUP BY 只能輸出彙總值不同，它**保留明細行**，並為每個分區內的行分配唯一遞增序號 (1, 2, 3...)。

• PARTITION BY：定義分區邊界 (如部門、客戶、訂單類別)。省略時整個結果集為單一分區。
• ORDER BY：分區內排序規則，決定序號分配順序。
• 返回值：BIGINT，從 1 開始，無間隙。

經典場景 1：查詢各分類最新一筆記錄 (Top-N Per Group)

需求：從訂單表中查詢每個客戶 最新 的一筆訂單。

錯誤嘗試：GROUP BY CustomerID + MAX(OrderDate) 只能取到日期，無法同時取得該筆訂單的其他欄位 (如 Amount, Status)。

正確解法：

WITH RankedOrders AS (
    SELECT 
        OrderID, CustomerID, OrderDate, Amount, Status,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM dbo.Orders
)
SELECT OrderID, CustomerID, OrderDate, Amount, Status
FROM RankedOrders
WHERE rn = 1;  -- 只取每組第一名 (最新)

經典場景 2：資料去重 (保留最早/最晚/自定義規則)

需求：表中有重複訂單 (同 CustomerID, OrderDate, Amount)，保留每組 最早建立 (ID 最小) 的一筆。

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID, OrderDate, Amount ORDER BY ID) AS rn
    FROM dbo.Orders
)
DELETE FROM CTE WHERE rn > 1;  -- 直接刪除 CTE 中多餘行 (SQL Server 支援)

經典場景 3：分頁查詢 (高效、穩定)

SQL Server 2012+ 推薦 OFFSET/FETCH，但窗口函數分頁相容性更好 (2005+)，且易於加入動態排序：

DECLARE @PageSize INT = 20, @PageNum INT = 3;
WITH Paged AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS rn
    FROM dbo.Orders
    WHERE Status = 'Active'
)
SELECT * FROM Paged
WHERE rn BETWEEN (@PageNum-1)*@PageSize + 1 AND @PageNum*@PageSize
ORDER BY rn;

經典場景 4：統計每個客戶的訂單數、第 N 次訂單詳情

-- 1. 每個客戶總訂單數
WITH CTE AS (
    SELECT CustomerID, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS rn
    FROM dbo.Orders
)
SELECT CustomerID, MAX(rn) AS TotalOrders
FROM CTE GROUP BY CustomerID;

-- 2. 每個客戶第 3 筆訂單
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS rn
    FROM dbo.Orders
) t WHERE rn = 3;

-- 3. 每個客戶最小金額訂單 及 該筆是第幾單
WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS rn
    FROM dbo.Orders
)
SELECT * FROM CTE
WHERE Amount = (SELECT MIN(Amount) FROM CTE c2 WHERE c2.CustomerID = CTE.CustomerID);

進階技巧與避坑指南

1. PARTITION BY vs GROUP BY 核心差異

2. 執行順序陷阱

SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS rn, *
FROM Orders
WHERE OrderDate > '2023-01-01';

執行順序：FROM → WHERE → GROUP BY → HAVING → SELECT (窗口函數) → ORDER BY。
**即：WHERE 先過濾，再對剩餘行分區編號。** 若需對全表編號再過濾，請用 CTE/子查詢。

3. 多重排序與確定性

ORDER BY OrderDate DESC, OrderID DESC 加入主鍵作為 Tie-Breaker，**保證結果確定性** (相同日期時按 ID 排序)，避免並行計劃導致序號抖動。

4. 效能優化：索引支援

理想索引：CREATE INDEX IX_Orders_Cust_Date ON Orders(CustomerID, OrderDate DESC) INCLUDE (Amount, Status);
優化器可直接**有序掃描索引葉層** → **流式窗口聚合** → **零排序、零 TempDB、零記憶體授予**，極致高效。

5. 其它兄弟函數對比

總結

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 是 T-SQL 中**表達力最強、應用面最廣**的分析函數。掌握其「分區 → 排序 → 編號 → 篩選」四步法，配合 CTE/子查詢，即可優雅解決 **Top-N、去重、分頁、分組統計、確定性排序** 等核心業務問題。**記住：窗口函數執行於 WHERE 之後，善用索引覆蓋 (Partition 欄位 + Order 欄位) 實現零排序流式執行。**