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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是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 clause><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 只適用於下列類型的資料行:charvarcharncharnvarcharCOLLATE 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 表示此函數操作所在的資料列視窗大小為三個資料列,從之前的 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. 搭配 ROW_NUMBER 函數來使用 OVER 子句Using the OVER clause with the ROW_NUMBER function

下列範例示範如何搭配 ROW_NUMBER 函數使用 OVER 子句,以針對資料分割內的每一個資料列顯示資料列號碼。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 的每一行都會計算出 SalesOrderIDNotice 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

下列範例搭配 OVER 子句來使用 AVG 與 SUM 函數,為 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. 搭配 ROW_NUMBER 函數來使用 OVER 子句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