SQL Server 開窗函數 OVER() 實戰：分組統計、排名分頁與聚合計算完整指南

`OVER()` 子句讓聚合函數 (`SUM`、`AVG`、`MAX`、`MIN`) 與排名函數 (`ROW_NUMBER`、`RANK`、`DENSE_RANK`、`NTILE`) 能在不分組 (無 GROUP BY) 的情況下，針對每一列輸出分組統計值，極大簡化「各組最大值對應明細」等查詢。本文系統講解語法、兩大函數族群、分組統計實戰與分頁經典模式。

語法結構

函數(...) OVER ( [ PARTITION BY 分組欄位 ] [ ORDER BY 排序欄位 ] )

• PARTITION BY：定義視窗分組 (如依產品類別)。
• ORDER BY：定義視窗內排序 (排名函數必填，聚合函數不可使用)。

一、排名開窗函數 (需 ORDER BY)

經典分頁模式 (CTE + ROW_NUMBER)

WITH OrderInfo AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate) AS RN,
        OrderID, CustomerID, EmployeeID, OrderDate
    FROM Orders
)
SELECT * FROM OrderInfo WHERE RN BETWEEN 1 AND 10;  -- 第 1 頁，每頁 10 筆

二、聚合開窗函數 (不可 ORDER BY)

支援 `SUM`、`AVG`、`MAX`、`MIN`、`COUNT` 等。只能用 PARTITION BY 或完全不分區。

實戰：各產品類別最高價格產品明細 (免 JOIN)

DECLARE @t TABLE (
    ProductID INT, ProductName VARCHAR(20), ProductType VARCHAR(20), Price INT
);
INSERT @t VALUES 
(1,'name1','P1',3),(2,'name2','P1',5),
(3,'name3','P2',4),(4,'name4','P2',4);

-- 做法：OVER() 直接輸出各組最大價，再篩選
WITH cte AS (
    SELECT 
        *,
        MAX(Price) OVER (PARTITION BY ProductType) AS MaxPrice
    FROM @t
)
SELECT ProductID, ProductName, ProductType, Price
FROM cte
WHERE Price = MaxPrice
ORDER BY ProductType;

優勢： 免 `JOIN` 衍生表、免 `GROUP BY` 再關聯、單掃描完成，可讀性高。

對比：傳統 JOIN 寫法

SELECT t1.* 
FROM @t t1
JOIN (SELECT ProductType, MAX(Price) AS MaxPrice FROM @t GROUP BY ProductType) t2
  ON t1.ProductType = t2.ProductType AND t1.Price = t2.MaxPrice;

三、進階視窗框架 (Frame Clause) - SQL Server 2012+

SUM(Sales) OVER (
    PARTITION BY Year 
    ORDER BY Month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 滾動 3 月加總
)

• `ROWS` / `RANGE` 定義實體/邏輯視窗邊界。
• `UNBOUNDED PRECEDING`、`CURRENT ROW`、`N FOLLOWING` 組合。
• 應用：累積總額、移動平均、同比環比。

四、效能與最佳實務

1. 索引支援： `PARTITION BY` + `ORDER BY` 欄位建立複合索引可避免 Sort 運算子。
2. 避免過大視窗： `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` 等同無框架，但明確語意較佳。
3. 排名函數分頁 在大資料量下優於 `OFFSET/FETCH` (舊版) 或雙重 `TOP`，但新版建議直接 `OFFSET/FETCH`。
4. 除錯技巧： 先 `SELECT *, 函數 OVER(...) FROM table` 檢視視窗值，再包裝 CTE 篩選。

善用 `OVER()` 將分組統計從「多查詢 JOIN」簡化為「單查詢視窗」，是寫出高效、易維護 T-SQL 的關鍵技能。