深度探討 SQL Server 2005 視窗函數：OVER 子句實戰範例

SQL Server 2005 引入 視窗函數，以 OVER(...) 子句定義資料分割視窗，支援彙總、排序、分布等運算，單一查詢即可同時產生明細與多層級彙總，極大簡化報表邏輯。

核心語法

聚合函數 OVER ( [PARTITION BY 運算式] [ORDER BY 運算式] [ROWS/RANGE 視窗框架] )

• PARTITION BY：將資料分割為獨立視窗 (如依訂單編號)。
• ORDER BY：視窗內排序，配合框架子句實現累加/移動平均。
• ROWS BETWEEN ...：精確控制視窗範圍 (如前 3 列至當前列)。

實戰範例：單一訂單明細同時產出 9 種彙總

USE AdventureWorks2;
SELECT
    SalesOrderID, ProductID,
    OrderQty AS [Item Qty],
    SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Total Qty],
    AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Average Qty],
    COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Total Count],
    MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Minimum Qty],
    MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS [Maximum Qty],
    LineTotal,
    AVG(LineTotal) OVER (PARTITION BY SalesOrderID) AS [Average Amount],
    SUM(LineTotal) OVER (PARTITION BY SalesOrderID) AS [Total Amount],
    (LineTotal * 100.0 / SUM(LineTotal) OVER (PARTITION BY SalesOrderID)) AS [Percent %]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 43664
ORDER BY ProductID;

進階應用：累加總額與移動平均

SELECT
    OrderDate,
    DailySales,
    SUM(DailySales) OVER (ORDER BY OrderDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
    AVG(DailySales) OVER (ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MovingAvg7Day
FROM DailySalesFact
ORDER BY OrderDate;

支援的函數類別

• 彙總：SUM, AVG, COUNT, MIN, MAX, CHECKSUM_AGG 等。
• 排序：ROW_NUMBER, RANK, DENSE_RANK, NTILE。
• 分布：PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DISC (2012+)。
• 位移：LAG, LEAD, FIRST_VALUE, LAST_VALUE (2012+)。

結語

視窗函數將「分組彙總後再 JOIN 回明細」的傳統模式簡化為單一掃描，不僅程式碼簡潔，執行計畫亦更高效。掌握 PARTITION BY、ORDER BY、框架子句三大控制桿，即可輕鬆實現累加、移動平均、排名、分位數等進階分析。