應用視窗功能進行分析

14 分鐘

小提示

有關更多詳細信息，請參閱 文本和圖像 選項卡！

分析性查詢通常需要跨越多列的計算，同時仍返回個別列細節。傳統的聚合函式會將列合併成群組，導致列層級資訊流失。視窗函數透過在與當前列相關的一組列間進行計算，且不壓縮結果集來解決此問題。

了解視窗函數語法

視窗函數根據 OVER 子句定義的「視窗」計算列中的數值。與一般聚合函數不同，視窗函式不會將列群組成單一輸出列。相反地，它們會計算相關列間的值，同時保留結果中所有原始列。

視窗函式的一般語法為：

function_name(arguments) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC | DESC]]
    [ROWS | RANGE frame_specification]
)

OVER子句組件控制視窗的定義：

PARTITION BY：將資料列劃分為群組（partitions）以便計算
ORDER BY：決定每個分割中列的邏輯順序
ROWS/RANGE：定義相對於當前列的影格邊界

以下查詢展示了一個簡單的視窗函數，計算每位顧客訂單的累計總數：

SELECT 
    CustomerID,
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

備註

當你在沒有框架規範的ORDER BY子句中指定OVER時，預設框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW用於聚合函數。這會產生累積計算。

使用排名函數

排名函數會根據列在分割中的位置，為列分配連續數字。 SQL Server 提供四種排名函式。每個函式在處理並列數值的方式不同：

ROW_NUMBER() 為每列分配唯一編號，即使數值相同，也不會重複：

SELECT 
    ProductID,
    Name,
    ListPrice,
    ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

結果集如下：

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     2
751         Road-150 Red, 48            3578.27     3
771         Mountain-100 Silver, 38     3399.99     4

此查詢會依價格從高到低排名所有產品。每個產品都會獲得一個獨特的編號，無論多個產品是否共享相同價格。

RANK() 會為並列值指派相同的排名，並為了反映並列情況而跳過後續的排名號碼：

SELECT 
    ProductID,
    Name,
    ListPrice,
    RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

結果集如下：

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     4

當兩款產品價格相同時，兩者都會獲得相同的排名。下一個產品的排名反映排名較高的產品總數，造成序列中出現空缺。

DENSE_RANK() 將相同排名分配給並列的數值，但不會跳過數字：

SELECT 
    ProductID,
    Name,
    ListPrice,
    DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

結果集如下：

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     2

例如 RANK()，具有相同數值的項目會共享相同的排名。不過，DENSE_RANK() 會從下一個連續數字接續下去，所以你可以用它來計算不同的價格層級。

NTILE(n) 將列分配到指定數量的大致相等群組：

SELECT 
    ProductID,
    Name,
    ListPrice,
    NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;

結果集如下：

ProductID   Name                          ListPrice   PriceQuartile
---------   ---------------------------   ---------   -------------
749         Road-150 Red, 62              3578.27     1
771         Mountain-100 Silver, 38       3399.99     1
722         LL Road Frame - Black, 58     337.22      2
859         Half-Finger Gloves, S         24.49       4

這個問題會根據價格將產品分為四類。價格最高的產品位於四分位數 1，價格最低的產品位於四分位數 4。用 NTILE() 來進行百分位數分析或平均分配工作。

使用 PARTITION BY 結合排名函數可以實現每組排名：

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice,
    ROW_NUMBER() OVER (
        PARTITION BY p.ProductCategoryID 
        ORDER BY p.ListPrice DESC
    ) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;

結果集如下：

Category          Product                     ListPrice   CategoryPriceRank
---------------   -------------------------   ---------   -----------------
Road Bikes        Road-150 Red, 62            3578.27     1
Road Bikes        Road-150 Red, 44            3578.27     2
Mountain Bikes    Mountain-100 Silver, 38     3399.99     1
Mountain Bikes    Mountain-100 Black, 38      3374.99     2

此查詢會分別對每個類別內的產品進行排名。每個類別的排名會從 1 重新開始，因此你可以透過篩選 CategoryPriceRank = 1 來找出每個類別中最昂貴的產品。

小提示

當您需要每個排名正好對應一筆資料 (例如在每組中找到排名前 N 的項目)，請使用 ROW_NUMBER()。使用 RANK() 或 DENSE_RANK() 在需要保留領帶資訊以供報告時使用。

應用聚合視窗函數

標準的聚合函數如 SUM、 AVG、 COUNTMIN、，MAX可透過加入OVER子句作為視窗函數使用。這讓你能在保留每個列細節的同時計算聚合。

以下查詢說明如何計算持續總額與累積總量：

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
    AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
    COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;

結果集如下：

SalesOrderID   OrderDate    TotalDue    RunningTotal   RunningAverage   OrderNumber
------------   ----------   ---------   ------------   --------------   -----------
71774          2008-06-01   972.785     972.785        972.785          1
71776          2008-06-01   87.083      1059.868       529.934          2
71780          2008-06-01   42452.65    43512.518      14504.172        3
71782          2008-06-01   43962.79    87475.308      21868.827        4

這很重要

當在 ORDER BY 子句中使用不含 OVER 的聚合視窗函數時，該函數會計算整個分區。添加 ORDER BY 會從分割區的起始點到當前列進行累積計算。

使用`ROWS`和`RANGE` 定義窗框

視窗框架可以精確指定相對於當前列應該包含哪些列在計算中。該 ROWS 子句計算實體列數，而 RANGE 分組數值相等的列。

幀邊界可透過以下方式指定：

UNBOUNDED PRECEDING：從分割區開始
n PRECEDING：n 當前行之前的行
CURRENT ROW：當前行
n FOLLOWING：n 當前列之後的數行
UNBOUNDED FOLLOWING：到分割區的末端

以下查詢計算過去三個訂單的移動平均：

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    AVG(TotalDue) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

結果集如下：

SalesOrderID   OrderDate    TotalDue    MovingAvg3Orders
------------   ----------   ---------   ----------------
71774          2008-06-01   972.785     972.785
71776          2008-06-01   87.083      529.934
71780          2008-06-01   42452.65    14504.172
71782          2008-06-01   43962.79    28834.174

此查詢計算三階移動平均，包含當前列及其前兩列。對於第一列，只有一個值可用，因此平均值等 TotalDue於。到第三排時，視窗包含所有三排。

使用解析函數

分析功能讓你能在不使用自我加入或子查詢的情況下，存取其他資料列的資料。這些功能對於時間序列分析、趨勢偵測，以及將當前值與歷史或未來值進行比較非常有用。與計算摘要的彙總視窗函數不同，分析函數會從視窗中特定列中檢索特定值。

LAG() 以及 LEAD() 獲取自上一列或下一列的值，例如如下：

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
    LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
    TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

結果集如下：

SalesOrderID   OrderDate    TotalDue    PreviousOrderTotal   NextOrderTotal   ChangeFromPrevious
------------   ----------   ---------   ------------------   --------------   ------------------
71774          2008-06-01   972.785     0                    87.083           972.785
71776          2008-06-01   87.083      972.785              42452.65         -885.702
71780          2008-06-01   42452.65    87.083               43962.79         42365.567
71782          2008-06-01   43962.79    42452.65             0                1510.14

LAG() 從前一列取得值，而 LEAD() 從下一列取得。第二個參數指定要回溯或向前查看多少列（預設為 1），第三個參數則在沒有列存在時提供預設值（例如第一列）。LAG() 利用這些函數計算跨期變化、識別趨勢或偵測連續資料中的異常。

FIRST_VALUE() 以及 LAST_VALUE() 從畫面中第一或最後一列回傳的值：

SELECT 
    ProductID,
    Name,
    ListPrice,
    ProductCategoryID,
    FIRST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
    ) AS MostExpensiveInCategory,
    LAST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;

結果集如下：

ProductID   Name                        ListPrice   ProductCategoryID   MostExpensiveInCategory    LeastExpensiveInCategory
---------   -------------------------   ---------   -----------------   ------------------------   ------------------------
749         Road-150 Red, 62            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
750         Road-150 Red, 44            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
722         LL Road Frame - Red, 58     337.22      5                   Road-150 Red, 62           LL Road Frame - Red, 58
771         Mountain-100 Silver, 38     3399.99     6                   Mountain-100 Silver, 38    Mountain-500 Black, 52

FIRST_VALUE() 回傳排序視窗第一列的值，此處為每個類別中最昂貴的產品。 LAST_VALUE() 會回傳最低價格的值，但需要明確指定視窗框架才能包含所有資料列。這些功能幫助你將每一列與基準值（如群組中最高、最低或基準值）進行比較。

備註

LAST_VALUE() 需要明確的框架規範，必須包含當前列之後的列。若無此功能，預設幀只包含至當前列的列數，因此 LAST_VALUE() 回傳該列的值。

PERCENT_RANK（） 和 CUME_DIST（） 計算分割區內的相對位置：

SELECT 
    Name,
    ListPrice,
    PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
    CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;

結果集如下：

Name                        ListPrice   PercentRank   CumulativeDistribution
-------------------------   ---------   -----------   ----------------------
Patch Kit/8 Patches         2.29        0.0           0.0081
Road Tire Tube              3.99        0.0081        0.0162
Touring Tire Tube           4.99        0.0162        0.0243
Road-150 Red, 62            3578.27     0.9919        1.0

PERCENT_RANK() 回傳介於 0 到 1 之間的值，表示有多少比例的列值較低（0 代表最低，1 代表最高）。 CUME_DIST() 顯示累積分布，顯示有多少比例的列值小於或等於當前列。利用這些函數進行百分位分析、識別異常值或建立分布報告。

欲了解更多視窗函數的資訊，請參閱視窗函數（Transact-SQL）及排名函數。

意見反應

此頁面對您有幫助嗎？

應用視窗功能進行分析

了解視窗函數語法

使用排名函數

應用聚合視窗函數

使用ROWS和RANGE 定義窗框

使用解析函數

意見反應

使用`ROWS`和`RANGE` 定義窗框