您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 視窗函數 OVER() 子句完整教學：聚合與排名運算實戰

編輯：SQL Server 2005

SQL Server 2005 引入 OVER() 子句，讓聚合函數與排名函數能在「視窗」 (一組相關資料列) 上運算，無需 GROUP BY 即可同時取得明細與彙總，大幅簡化複雜統計查詢。本文系統介紹語法結構、適用函數類別、實戰範例 (總計、佔比、分組彙總、分組排名、整體排名) 與 SQL 2000 對比寫法。

核心概念

視窗：使用者定義的一組資料列 (PARTITION BY 分區 + ORDER BY 排序 + ROWS/RANGE 框架)
視窗函數：在視窗上運算，對每一列回傳結果 (而非像 GROUP BY 縮減列數)
兩大類別：
- 聚合視窗函數：SUM、AVG、COUNT、MIN、MAX 等
- 排名視窗函數：ROW_NUMBER、RANK、DENSE_RANK、NTILE

語法結構

聚合/排名函數 OVER (
    [PARTITION BY partition_expression, ... ]
    [ORDER BY order_expression [ASC|DESC], ... ]
    [ROWS|RANGE BETWEEN frame_start AND frame_end]
)

實戰範例：銷售訂單統計

建立測試資料

CREATE TABLE SalesOrder (
    OrderID INT,
    OrderQty DECIMAL(18,2)
);

INSERT INTO SalesOrder VALUES
(1, 2.0), (1, 1.0), (1, 3.0),
(2, 6.0), (2, 1.1),
(3, 8.0), (3, 1.1), (3, 7.0);

需求：單一查詢同時取得

整體總量
每筆訂單佔整體比例
各群組 (OrderID) 總量
每筆訂單在群組內佔比

視窗函數寫法 (一條查詢完成)

SELECT 
    OrderID,
    OrderQty,
    SUM(OrderQty) OVER() AS [彙總],
    CONVERT(DECIMAL(18,4), OrderQty / SUM(OrderQty) OVER()) AS [每單佔比],
    SUM(OrderQty) OVER(PARTITION BY OrderID) AS [分組彙總],
    CONVERT(DECIMAL(18,4), OrderQty / SUM(OrderQty) OVER(PARTITION BY OrderID)) AS [每單在組佔比]
FROM SalesOrder
ORDER BY OrderID;

結果

OrderID OrderQty 彙總  每單佔比 分組彙總 每單在組佔比
1       2.00     29.20 0.0685   6.00     0.3333
1       1.00     29.20 0.0342   6.00     0.1667
1       3.00     29.20 0.1027   6.00     0.5000
2       6.00     29.20 0.2055   7.10     0.8451
2       1.10     29.20 0.0377   7.10     0.1549
3       8.00     29.20 0.2740   16.10    0.4969
3       1.10     29.20 0.0377   16.10    0.0683
3       7.00     29.20 0.2397   16.10    0.4348

SQL 2000 寫法對比 (需暫存表、多次掃描、JOIN)

DECLARE @sum DECIMAL(18,2);
SELECT @sum = SUM(OrderQty) FROM SalesOrder;

SELECT OrderID, SUM(OrderQty) AS su INTO #t
FROM SalesOrder GROUP BY OrderID;

SELECT s.OrderID, s.OrderQty,
    @sum AS [彙總],
    CONVERT(DECIMAL(18,4), s.OrderQty/@sum) AS [每單佔比],
    t.su AS [分組彙總],
    CONVERT(DECIMAL(18,4), s.OrderQty/t.su) AS [每單在組佔比]
FROM SalesOrder s JOIN #t t ON t.OrderID = s.OrderID
ORDER BY s.OrderID;

DROP TABLE #t;

排名視窗函數範例

SELECT 
    OrderID, OrderQty,
    RANK() OVER(PARTITION BY OrderID ORDER BY OrderQty) AS [分組排名],
    RANK() OVER(ORDER BY OrderQty) AS [整體排名]
FROM SalesOrder
ORDER BY OrderID;

結果

OrderID OrderQty 分組排名 整體排名
1       2.00     2        4
1       3.00     3        5
1       1.00     1        1
2       1.10     1        2
2       6.00     2        6
3       7.00     2        7
3       8.00     3        8
3       1.10     1        2  -- 注意：RANK 相同值同名次，跳過下一名次

進階框架子句 (ROWS/RANGE)

-- 移動平均：前 1 列到當前列
AVG(OrderQty) OVER(PARTITION BY OrderID ORDER BY OrderQty 
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)

-- 累計總計：分區起始到當前列
SUM(OrderQty) OVER(PARTITION BY OrderID ORDER BY OrderQty 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- 區間總計：當前列前後各 1 列
SUM(OrderQty) OVER(ORDER BY OrderDate 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

效能與最佳實務

PARTITION BY + ORDER BY 欄位建立索引可避免 Sort 操作子
大量資料視窗聚合可能消耗 TempDB (Sort/Hash Spill)，監控 spill_to_tempdb 警告
ROWS (實體列數) 效能優於 RANGE (邏輯值範圍)，優先使用 ROWS
避免在視窗函數中使用非確定性函數 (NEWID(), RAND()) 導致結果不穩定
SQL 2012+ 支援 FIRST_VALUE, LAST_VALUE, LEAD, LAG, PERCENT_RANK, CUME_DIST