SELECT - OVER 子句 (Transact-SQL)SELECT - OVER Clause (Transact-SQL)

适用于: 是SQL Server(从 2008 开始)是Azure SQL 数据库是Azure SQL 数据仓库是并行数据仓库APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

在应用关联的开窗函数前确定行集的分区和排序。Determines the partitioning and ordering of a rowset before the associated window function is applied. 也就是说,OVER 子句定义查询结果集内的窗口或用户指定的行集。That is, the OVER clause defines a window or user-specified set of rows within a query result set. 然后,开窗函数将计算窗口中每一行的值。A window function then computes a value for each row in the window. 您可以将 OVER 子句与函数一起使用,以便计算各种聚合值,例如移动平均值、累积聚合、运行总计或每组结果的前 N 个结果。You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server, Azure SQL Database, and Azure SQL Data Warehouse  
  
OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  
  
<PARTITION BY clause> ::=  
PARTITION BY value_expression , ... [ n ]  
  
<ORDER BY clause> ::=  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]  
  
<ROW or RANGE clause> ::=  
{ ROWS | RANGE } <window frame extent>  
  
<window frame extent> ::=   
{   <window frame preceding>  
  | <window frame between>  
}  
<window frame between> ::=   
  BETWEEN <window frame bound> AND <window frame bound>  
  
<window frame bound> ::=   
{   <window frame preceding>  
  | <window frame following>  
}  
  
<window frame preceding> ::=   
{  
    UNBOUNDED PRECEDING  
  | <unsigned_value_specification> PRECEDING  
  | CURRENT ROW  
}  
  
<window frame following> ::=   
{  
    UNBOUNDED FOLLOWING  
  | <unsigned_value_specification> FOLLOWING  
  | CURRENT ROW  
}  
  
<unsigned value specification> ::=   
{  <unsigned integer literal> }  
  
-- Syntax for Parallel Data Warehouse  
  
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )  

参数Arguments

PARTITION BYPARTITION BY
将查询结果集分为多个分区。Divides the query result set into partitions. 开窗函数分别应用于每个分区,并为每个分区重新启动计算。The window function is applied to each partition separately and computation restarts for each partition.

value_expressionvalue_expression
指定行集按其分区的列。Specifies the column by which the rowset is partitioned. value_expression 只能引用可供 FROM 子句使用的列。value_expression can only refer to columns made available by the FROM clause. value_expression 不能引用选择列表中的表达式或别名。value_expression cannot refer to expressions or aliases in the select list. value_expression 可以是列表达式、标量子查询、标量函数或用户定义的变量。value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<ORDER BY 子句 ><ORDER BY clause>
定义结果集的每个分区中行的逻辑顺序。Defines the logical order of the rows within each partition of the result set. 也就是说,它指定按其执行开窗函数计算的逻辑顺序。That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expressionorder_by_expression
指定用于进行排序的列或表达式。Specifies a column or expression on which to sort. order_by_expression 只能引用可供 FROM 子句使用的列。order_by_expression can only refer to columns made available by the FROM clause. 不能将整数指定为表示列名或别名。An integer cannot be specified to represent a column name or alias.

COLLATE collation_nameCOLLATE collation_name
指定应该根据在 collation_name 中指定的排序规则执行 ORDER BY 操作。Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. collation_name 既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称。collation_name can be either a Windows collation name or a SQL collation name. 有关详细信息,请参阅 Collation and Unicode SupportFor more information, see Collation and Unicode Support. COLLATE 仅适用于 char、nchar、varchar 和 nvarchar 类型的列。COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESCASC | DESC
指定按升序或降序排列指定列中的值。Specifies that the values in the specified column should be sorted in ascending or descending order. ASC 是默认排序顺序。ASC is the default sort order. Null 值被视为最低的可能值。Null values are treated as the lowest possible values.

ROWS | RANGEROWS | RANGE
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

通过指定分区中的起点和终点,进一步限制分区中的行数。Further limits the rows within the partition by specifying start and end points within the partition. 这是通过按照逻辑关联或物理关联对当前行指定某一范围的行实现的。This is done by specifying a range of rows with respect to the current row either by logical association or physical association. 物理关联通过使用 ROWS 子句实现。Physical association is achieved by using the ROWS clause.

ROWS 子句通过指定当前行之前或之后的固定数目的行,限制分区中的行数。The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. 此外,RANGE 子句通过指定针对当前行中的值的某一范围的值,从逻辑上限制分区中的行数。Alternatively, the RANGE clause logically limits the rows within a partition by specifying a range of values with respect to the value in the current row. 基于 ORDER BY 子句中的顺序对之前和之后的行进行定义。Preceding and following rows are defined based on the ordering in the ORDER BY clause. 窗口框架“RANGE …CURRENT ROW ...”包括在 ORDER BY 表达式中与当前行具有相同值的所有行。The window frame "RANGE ... CURRENT ROW ..." includes all rows that have the same values in the ORDER BY expression as the current row. 例如,ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 意味着该函数对其操作的行的窗口在大小上是 3 行,开头为之前的 2 行,再包括当前行。For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means that the window of rows that the function operates on is three rows in size, starting with 2 rows preceding until and including the current row.

备注

ROWS 或 RANGE 要求指定 ORDER BY 子句。ROWS or RANGE requires that the ORDER BY clause be specified. 如果 ORDER BY 包含多个顺序表达式,则 CURRENT ROW FOR RANGE 在确定当前行时将考虑 ORDER BY 列表中的所有列。If ORDER BY contains multiple order expressions, CURRENT ROW FOR RANGE considers all columns in the ORDER BY list when determining the current row.

UNBOUNDED PRECEDINGUNBOUNDED PRECEDING
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指定窗口在分区中的第一行开始。Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING 只能指定为窗口起点。UNBOUNDED PRECEDING can only be specified as window starting point.

<无符号值指定> PRECEDING<unsigned value specification> PRECEDING
使用 <无符号值指定> 指示要置于当前行之前的行或值的数目。Specified with <unsigned value specification>to indicate the number of rows or values to precede the current row. 对于 RANGE 则不允许这样指定。This specification is not allowed for RANGE.

CURRENT ROWCURRENT ROW
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

在与 ROWS 一起使用时指定窗口在当前行开始或结束,或者在与 RANGE 一起使用时指定当前值。Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE. CURRENT ROW 可指定为既是起点,又是终点。CURRENT ROW can be specified as both a starting and ending point.

BETWEEN <窗口框架限定< AND <窗口框架限定>BETWEEN <window frame bound > AND <window frame bound >
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

与 ROWS 或 RANGE 一起使用,以便指定窗口的下(开始)边界和上(结束)边界点。Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. <窗口框架限定> 定义边界起点,<窗口框架限定> 定义边界结点。<window frame bound> defines the boundary starting point and <window frame bound> defines the boundary end point. 上限不能小于下限。The upper bound cannot be smaller than the lower bound.

UNBOUNDED FOLLOWINGUNBOUNDED FOLLOWING
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指定窗口在分区的最后一行结束。Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING 只能指定为窗口终点。UNBOUNDED FOLLOWING can only be specified as a window end point. 例如,RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 定义以当前行开始、以分区的最后一行结束的窗口。For example RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING defines a window that starts with the current row and ends with the last row of the partition.

<无符号值指定> FOLLOWING<unsigned value specification> FOLLOWING
使用 <无符号值指定> 指示要置于当前行之后的行或值的数目。Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. 在 <无符号值指定> FOLLOWING 指定为窗口起点时,终点必须是 <无符号值指定>FOLLOWING。When <unsigned value specification> FOLLOWING is specified as the window starting point, the ending point must be <unsigned value specification>FOLLOWING. 例如,ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING 定义一个窗口,该窗口以跟随在当前行之后的第二行开头、以跟随在当前行之后的第十行结尾。For example, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING defines a window that starts with the second row that follows the current row and ends with the tenth row that follows the current row. 对于 RANGE 则不允许这样指定。This specification is not allowed for RANGE.

无符号整数文字unsigned integer literal
适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

一个正整数文字(包括 0),它指定要置于当前行或值之前或之后的行或值的数目。Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. 这一指定仅对于 ROWS 有效。This specification is valid only for ROWS.

一般备注General Remarks

可以在单个查询中将多个开窗函数与单个 FROM 子句一起使用。More than one window function can be used in a single query with a single FROM clause. 每个函数的 OVER 子句在分区和排序上可能不同。The OVER clause for each function can differ in partitioning and ordering.

如果未指定 PARTITION BY,则此函数将查询结果集的所有行视为单个组。If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

重要说明!Important!

如果指定 ROWS/RANGE 并且 <窗口框架前置> 用于 <窗口框架区>(简短语法),则这一指定用于窗口框架边界起点并且 CURRENT ROW 用于边界终点。If ROWS/RANGE is specified and <window frame preceding> is used for <window frame extent> (short syntax) then this specification is used for the window frame boundary starting point and CURRENT ROW is used for the boundary ending point. 例如,“ROWS 5 PRECEDING”等于“ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”。For example "ROWS 5 PRECEDING" is equal to "ROWS BETWEEN 5 PRECEDING AND CURRENT ROW".

备注

如果未指定 ORDER BY,则整个分区将用于窗口框架。If ORDER BY is not specified entire partition is used for a window frame. 这仅适用于不要求 ORDER BY 子句的函数。This applies only to functions that do not require ORDER BY clause. 如果未指定 ROWS/RANGE,但指定了 ORDER BY,则将 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 用作窗口框架的默认值。If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. 这仅适用于可接受可选 ROWS/RANGE 指定的函数。This applies only to functions that have can accept optional ROWS/RANGE specification. 例如,排名函数无法接受 ROWS/RANGE,因此,此窗口框架不适用,甚至在存在 ORDER BY 而不存在 ROWS/RANGE 时也是如此。For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.

限制和局限Limitations and Restrictions

OVER 子句不能与 CHECKSUM 聚合函数结合使用。The OVER clause cannot be used with the CHECKSUM aggregate function.

RANGE 不能用于 <无符号值指定> PRECEDING 或 <无符号值指定> FOLLOWING。RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.

根据用于 OVER 子句的排名、聚合或分析函数,可能不支持 <ORDER BY 子句> 和/或 <ROWS 和 RANGE 子句>。Depending on the ranking, aggregate, or analytic function used with the OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not be supported.

示例Examples

A.A. 将 OVER 子句与 ROW_NUMBER 函数结合使用Using the OVER clause with the ROW_NUMBER function

下面的示例说明如何将 OVER 子句与 ROW_NUMBER 函数一起使用来显示分区内各行的行号。The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. 在 OVER 子句中指定的 ORDER BY 子句按 SalesYTD 列对每个分区中的行进行排序。The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. SELECT 语句中的 ORDER BY 子句确定按其返回整个查询结果集的顺序。The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned.

USE AdventureWorks2012;  
GO  
SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",   
    p.LastName, s.SalesYTD, a.PostalCode  
FROM Sales.SalesPerson AS s   
    INNER JOIN Person.Person AS p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address AS a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0  
ORDER BY PostalCode;  
GO  

下面是结果集:Here is the result set.

Row Number      LastName                SalesYTD              PostalCode 
--------------- ----------------------- --------------------- ---------- 
1               Mitchell                4251368.5497          98027 
2               Blythe                  3763178.1787          98027 
3               Carson                  3189418.3662          98027 
4               Reiter                  2315185.611           98027 
5               Vargas                  1453719.4653          98027  
6               Ansman-Wolfe            1352577.1325          98027  
1               Pak                     4116871.2277          98055  
2               Varkey Chudukatil       3121616.3202          98055  
3               Saraiva                 2604540.7172          98055  
4               Ito                     2458535.6169          98055  
5               Valdez                  1827066.7118          98055  
6               Mensa-Annan             1576562.1966          98055  
7               Campbell                1573012.9383          98055  
8               Tsoflias                1421810.9242          98055

B.B. 将 OVER 子句与聚合函数结合使用Using the OVER clause with aggregate functions

下面的示例对于查询返回的所有行将 OVER 子句与聚合函数一起使用。The following example uses the OVER clause with aggregate functions over all rows returned by the query. 在这个示例中,使用 OVER 子句与使用子查询相比,可以更高效地派生聚合值。In this example, using the OVER clause is more efficient than using subqueries to derive the aggregate values.

USE AdventureWorks2012;  
GO  
SELECT SalesOrderID, ProductID, OrderQty  
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total  
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"  
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"  
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"  
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"  
FROM Sales.SalesOrderDetail   
WHERE SalesOrderID IN(43659,43664);  
GO  

下面是结果集:Here is the result set.

SalesOrderID ProductID   OrderQty Total       Avg         Count       Min    Max  
------------ ----------- -------- ----------- ----------- ----------- ------ ------  
43659        776         1        26          2           12          1      6  
43659        777         3        26          2           12          1      6  
43659        778         1        26          2           12          1      6  
43659        771         1        26          2           12          1      6  
43659        772         1        26          2           12          1      6  
43659        773         2        26          2           12          1      6  
43659        774         1        26          2           12          1      6  
43659        714         3        26          2           12          1      6  
43659        716         1        26          2           12          1      6  
43659        709         6        26          2           12          1      6  
43659        712         2        26          2           12          1      6  
43659        711         4        26          2           12          1      6  
43664        772         1        14          1           8           1      4  
43664        775         4        14          1           8           1      4  
43664        714         1        14          1           8           1      4  
43664        716         1        14          1           8           1      4  
43664        777         2        14          1           8           1      4  
43664        771         3        14          1           8           1      4  
43664        773         1        14          1           8           1      4  
43664        778         1        14          1           8           1      4  

以下示例显示在计算所得值中将 OVER 子句与聚合函数结合使用。The following example shows using the OVER clause with an aggregate function in a calculated value.

USE AdventureWorks2012;  
GO  
SELECT SalesOrderID, ProductID, OrderQty  
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total  
    ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)   
        *100 AS DECIMAL(5,2))AS "Percent by ProductID"  
FROM Sales.SalesOrderDetail   
WHERE SalesOrderID IN(43659,43664);  
GO  

下面是结果集:Here is the result set.注意,聚合由 SalesOrderID 计算,并会为每个 Percent by ProductID 的每一行计算 SalesOrderID(ProductID 的百分比)。Notice that the aggregates are calculated by SalesOrderID and the Percent by ProductID is calculated for each line of each SalesOrderID.

SalesOrderID ProductID   OrderQty Total       Percent by ProductID  
------------ ----------- -------- ----------- ---------------------------------------  
43659        776         1        26          3.85  
43659        777         3        26          11.54  
43659        778         1        26          3.85  
43659        771         1        26          3.85  
43659        772         1        26          3.85  
43659        773         2        26          7.69  
43659        774         1        26          3.85  
43659        714         3        26          11.54  
43659        716         1        26          3.85  
43659        709         6        26          23.08  
43659        712         2        26          7.69  
43659        711         4        26          15.38  
43664        772         1        14          7.14  
43664        775         4        14          28.57  
43664        714         1        14          7.14  
43664        716         1        14          7.14  
43664        777         2        14          14.29  
43664        771         3        14          21.4  
43664        773         1        14          7.14  
43664        778         1        14          7.14  
  
 (20 row(s) affected)  

C.C. 生成移动平均值和累计合计Producing a moving average and cumulative total

下面的示例将 AVG 和 SUM 函数与 OVER 子句结合使用,以便为 Sales.SalesPerson 表中的每个地区提供年度销售额的累计合计。The following example uses the AVG and SUM functions with the OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the Sales.SalesPerson table. 数据按 TerritoryID 分区并在逻辑上按 SalesYTD 排序。The data is partitioned by TerritoryID and logically ordered by SalesYTD. 这意味着,将基于年度销售额为各区域计算 AVG 函数。This means that the AVG function is computed for each territory based on the sales year. 请注意,对于 TerritoryID 1,为 2005 销售年度存在两行,分别表示在该年度有销售业绩的两个销售人员。Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. 将计算这两行的平均销售额,然后在计算中包括表示 2006 年销售额的第三行。The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.

USE AdventureWorks2012;  
GO  
SELECT BusinessEntityID, TerritoryID   
   ,DATEPART(yy,ModifiedDate) AS SalesYear  
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD  
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID   
                                            ORDER BY DATEPART(yy,ModifiedDate)   
                                           ),1) AS MovingAvg  
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID   
                                            ORDER BY DATEPART(yy,ModifiedDate)   
                                            ),1) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5  
ORDER BY TerritoryID,SalesYear;  

下面是结果集:Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal  
---------------- ----------- ----------- -------------------- -------------------- --------------------  
274              NULL        2005        559,697.56           559,697.56           559,697.56  
287              NULL        2006        519,905.93           539,801.75           1,079,603.50  
285              NULL        2007        172,524.45           417,375.98           1,252,127.95  
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07  
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07  
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27  
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18  
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37  
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17  
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17  
  
(10 row(s) affected)  
  

在这个例子中,OVER 子句未包含 PARTITION BY。In this example, the OVER clause does not include PARTITION BY. 这意味着该函数将应用于查询所返回的所有行。This means that the function will be applied to all rows returned by the query. 在 OVER 子句中指定的 ORDER BY 子句将确定应用 AVG 函数的逻辑顺序。The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. 该查询将按年为在 WHERE 子句中指定的所有销售区域返回销售额的移动平均值。The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. 在 SELECT 语句中指定的 ORDER BY 子句将确定显示查询行的顺序。The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.

SELECT BusinessEntityID, TerritoryID   
   ,DATEPART(yy,ModifiedDate) AS SalesYear  
   ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD  
   ,CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)   
                                            ),1) AS MovingAvg  
   ,CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)   
                                            ),1) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5  
ORDER BY SalesYear;  

下面是结果集:Here is the result set.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal  
---------------- ----------- ----------- -------------------- -------------------- --------------------  
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35  
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35  
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35  
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35  
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35  
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35  
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35  
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47  
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47  
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93  
(10 row(s) affected)  

D.D. 指定 ROWS 子句Specifying the ROWS clause

适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

以下示例使用 ROWS 子句定义其行将作为当前行以及后随的 N 行(在此示例中为 1 行)计算的窗口。The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).

SELECT BusinessEntityID, TerritoryID   
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD  
    ,DATEPART(yy,ModifiedDate) AS SalesYear  
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID   
                                             ORDER BY DATEPART(yy,ModifiedDate)   
                                             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5;  

下面是结果集:Here is the result set.

BusinessEntityID TerritoryID SalesYTD             SalesYear   CumulativeTotal  
---------------- ----------- -------------------- ----------- --------------------  
274              NULL        559,697.56           2005        1,079,603.50  
287              NULL        519,905.93           2006        692,430.38  
285              NULL        172,524.45           2007        172,524.45  
283              1           1,573,012.94         2005        2,925,590.07  
280              1           1,352,577.13         2005        2,929,139.33  
284              1           1,576,562.20         2006        1,576,562.20  
275              2           3,763,178.18         2005        3,763,178.18  
277              3           3,189,418.37         2005        3,189,418.37  
276              4           4,251,368.55         2005        6,709,904.17  
281              4           2,458,535.62         2005        2,458,535.62  

在下面的示例中,使用 UNBOUNDED PRECEDING 指定 ROWS 子句。In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. 结果为窗口在分区中的第一行开始。The result is that the window starts at the first row of the partition.

SELECT BusinessEntityID, TerritoryID   
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD  
    ,DATEPART(yy,ModifiedDate) AS SalesYear  
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID   
                                             ORDER BY DATEPART(yy,ModifiedDate)   
                                             ROWS UNBOUNDED PRECEDING),1) AS CumulativeTotal  
FROM Sales.SalesPerson  
WHERE TerritoryID IS NULL OR TerritoryID < 5;  

下面是结果集:Here is the result set.

BusinessEntityID TerritoryID SalesYTD             SalesYear   CumulativeTotal  
---------------- ----------- -------------------- ----------- --------------------  
274              NULL        559,697.56           2005        559,697.56  
287              NULL        519,905.93           2006        1,079,603.50  
285              NULL        172,524.45           2007        1,252,127.95  
283              1           1,573,012.94         2005        1,573,012.94  
280              1           1,352,577.13         2005        2,925,590.07  
284              1           1,576,562.20         2006        4,502,152.27  
275              2           3,763,178.18         2005        3,763,178.18  
277              3           3,189,418.37         2005        3,189,418.37  
276              4           4,251,368.55         2005        4,251,368.55  
281              4           2,458,535.62         2005        6,709,904.17  
  

示例:并行数据仓库Parallel Data WarehouseExamples: 并行数据仓库Parallel Data Warehouse

E.E. 将 OVER 子句与 ROW_NUMBER 函数结合使用Using the OVER clause with the ROW_NUMBER function

以下示例根据销售代表所分配的销售配额返回各自的 ROW_NUMBER。The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

-- Uses AdventureWorks  
  
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,  
    FirstName, LastName,   
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq  
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE e.SalesPersonFlag = 1  
GROUP BY LastName, FirstName;  

以下为部分结果集。Here is a partial result set.

RowNumber  FirstName  LastName            SalesQuota  
---------  ---------  ------------------  -------------  
1          Jillian    Carson              12,198,000.00  
2          Linda      Mitchell            11,786,000.00  
3          Michael    Blythe              11,162,000.00  
4          Jae        Pak                 10,514,000.00  

F.F. 将 OVER 子句与聚合函数结合使用Using the OVER clause with aggregate functions

以下示例显示了将 OVER 子句与聚合函数结合使用。The following examples show using the OVER clause with aggregate functions. 在此示例中,使用 OVER 子句比使用子查询的效率高。In this example, using the OVER clause is more efficient than using subqueries.

-- Uses AdventureWorks  
  
SELECT SalesOrderNumber AS OrderNumber, ProductKey,   
       OrderQuantity AS Qty,   
       SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total,  
       AVG(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Avg,  
       COUNT(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Count,  
       MIN(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Min,  
       MAX(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Max  
FROM dbo.FactResellerSales   
WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND  
      ProductKey LIKE '2%'  
ORDER BY SalesOrderNumber,ProductKey;  

下面是结果集:Here is the result set.

OrderNumber  Product  Qty  Total  Avg  Count  Min  Max  
-----------  -------  ---  -----  ---  -----  ---  ---  
SO43659      218      6    16     3    5      1    6  
SO43659      220      4    16     3    5      1    6  
SO43659      223      2    16     3    5      1    6  
SO43659      229      3    16     3    5      1    6  
SO43659      235      1    16     3    5      1    6  
SO43664      229      1     2     1    2      1    1  
SO43664      235      1     2     1    2      1    1  

以下示例显示在计算所得值中将 OVER 子句与聚合函数结合使用。The following example shows using the OVER clause with an aggregate function in a calculated value. 注意,聚合由 SalesOrderNumber 计算,并会为每个 SalesOrderNumber 的每一行计算总销售订单的百分比。Notice that the aggregates are calculated by SalesOrderNumber and the percentage of the total sales order is calculated for each line of each SalesOrderNumber.

-- Uses AdventureWorks  
  
SELECT SalesOrderNumber AS OrderNumber, ProductKey AS Product,   
       OrderQuantity AS Qty,   
       SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total,  
       CAST(1. * OrderQuantity / SUM(OrderQuantity)   
        OVER(PARTITION BY SalesOrderNumber)   
            *100 AS DECIMAL(5,2)) AS PctByProduct  
FROM dbo.FactResellerSales   
WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND  
      ProductKey LIKE '2%'  
ORDER BY SalesOrderNumber,ProductKey;  

此结果集的开始部分为:The first start of this result set is:

OrderNumber  Product  Qty  Total  PctByProduct  
-----------  -------  ---  -----  ------------  
SO43659      218      6    16     37.50  
SO43659      220      4    16     25.00  
SO43659      223      2    16     12.50  
SO43659      229      2    16     18.75  

另请参阅See Also

聚合函数 (Transact-SQL) Aggregate Functions (Transact-SQL)
分析函数 (Transact-SQL) Analytic Functions (Transact-SQL)
Itzik Ben-Gan 在 sqlmag.com 上撰写的关于窗口函数和 OVER 的优秀博客文章Excellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan