应用用于分析的窗口函数

14 分钟

分析查询通常需要计算跨多行，同时仍返回单个行详细信息。传统的聚合函数将行折叠成组，丢失行级别信息。窗口函数通过跨与当前行相关的一组行执行计算来解决此难题，而不会折叠结果集。

了解窗口函数语法

窗口函数在 OVER 子句定义的行的窗口中计算值。与常规聚合函数不同，窗口函数不会将行分组到单个输出行中。相反，它们会跨相关行计算值，同时保留结果中的所有原始行。

窗口函数的一般语法为：

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

窗口的定义方式由 OVER 子句部分控制：

PARTITION BY：将行划分为计算的组（分区）
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;

注释

当在 OVER 子句中指定 ORDER BY 而不指定框架时，聚合函数的默认框架为 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、 COUNT和 MIN） 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

重要

当在 OVER 子句中使用聚合窗口函数而不指定 ORDER BY 时，该函数会在整个分区中计算。添加 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` 定义窗口框架