SELECT — предложение OVER (Transact-SQL)SELECT - OVER Clause (Transact-SQL)

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel Data WarehouseПрименимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance даAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics даПараллельное хранилище данныхParallel Data WarehouseyesПараллельное хранилище данныхParallel 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-SQLTopic link icon Transact-SQL Syntax Conventions

СинтаксисSyntax

-- Syntax for SQL Server, Azure SQL Database, and Azure Synapse Analytics  
  
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 ] )  

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

АргументыArguments

Оконные функции могут иметь следующие аргументы в предложении OVER:Window functions might have the following arguments in their OVER clause:

  • PARTITION BY — разделяет результирующий набор запроса на секции.PARTITION BY that divides the query result set into partitions.
  • ORDER BY — определяет логический порядок строк в каждой секции результирующего набора.ORDER BY that defines the logical order of the rows within each partition of the result set.
  • ROWS/RANGE — ограничивает строки в пределах секции, указывая начальную и конечную точки.ROWS/RANGE that limits the rows within the partition by specifying start and end points within the partition. Он требует аргумента ORDER BY, а по умолчанию будет охватывать интервал от начала секции до текущего элемента, если указан аргумент ORDER BY.It requires ORDER BY argument and the default value is from the start of partition to the current element if the ORDER BY argument is specified.

Если аргументы не указаны, оконные функции будут применены ко всему результирующему набору.If you don't specify any argument, the window functions will be applied on the entire result set.

select 
      object_id
    , [min] = min(object_id) over()
    , [max] = max(object_id) over()
from sys.objects
object_idobject_id минmin maxmax
33 33 21391546662139154666
55 33 21391546662139154666
...... ...... ......
21231546092123154609 33 21391546662139154666
21391546662139154666 33 21391546662139154666

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.

PARTITION BY *value_expression* 

Если аргумент PARTITION BY не указан, функция обрабатывает все строки результирующего набора запроса как одну секцию.If PARTITION BY is not specified, the function treats all rows of the query result set as a single partition. Функция будет применена ко всем строкам в секции, если не указано предложение ORDER BY.Function will be applied on all rows in the partition if you don't specify ORDER BY clause.

PARTITION BY value_expressionPARTITION BY value_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.

select 
     object_id, type
   , [min] = min(object_id) over(partition by type)
   , [max] = max(object_id) over(partition by type)
from sys.objects
object_idobject_id typetype минmin maxmax
6819529368195293 PKPK 6819529368195293 711673583711673583
631673298631673298 PKPK 6819529368195293 711673583711673583
711673583711673583 PKPK 6819529368195293 711673583711673583
...... ...... ......
33 SS 33 9898
55 SS 33 9898
...... ...... ......
9898 SS 33 9898
...... ...... ......

ORDER BYORDER BY

ORDER BY *order_by_expression* [COLLATE *collation_name*] [ASC|DESC]  

Определяет логический порядок строк в каждой секции результирующего набора.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 function calculation is performed.

  • Если он не указан, то порядок по умолчанию — ASC, а оконная функция будет использовать все строки в секции.If it is not specified, the default order is ASC and window function will use all rows in partition.
  • Если он указан, а аргумент ROWS/RANGE не указан, то по умолчанию используется RANGE UNBOUNDED PRECEDING AND CURRENT ROW для фрейма окна теми функциями, которые могут принимать дополнительную спецификацию ROWS/RANGE (например, min или max).If it is specified, and in ROWS/RANGE is not specified, then default RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame by the functions that can accept optional ROWS/RANGE specification (for example min or max).
select 
      object_id, type
    , [min] = min(object_id) over(partition by type order by object_id)
    , [max] = max(object_id) over(partition by type order by object_id)
from sys.objects
object_idobject_id typetype минmin maxmax
6819529368195293 PKPK 6819529368195293 6819529368195293
631673298631673298 PKPK 6819529368195293 631673298631673298
711673583711673583 PKPK 6819529368195293 711673583711673583
...... ...... ......
33 SS 33 33
55 SS 33 55
66 SS 33 66
...... ...... ......
9797 SS 33 9797
9898 SS 33 9898
...... ...... ......

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
Указывает, что операция ORDER BY должна выполняться в соответствии с параметрами сортировки, указанными в аргументе collation_name.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 Support.For more information, see Collation and Unicode Support. Аргумент COLLATE применяется только к столбцам типа char, varchar, nchar и 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 or RANGE

Область применения: SQL Server 2012 (11.x)SQL Server 2012 (11.x) и более поздних версий.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

Еще больше ограничивает строки в пределах секции, указывая начальную и конечную точки.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.

select
      object_id
    , [preceding]   = count(*) over(order by object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
    , [central] = count(*) over(order by object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING )
    , [following]   = count(*) over(order by object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
from sys.objects
order by object_id asc
object_idobject_id precedingpreceding centralcentral followingfollowing
33 11 33 156156
55 22 44 155155
66 33 55 154154
77 44 55 153153
88 55 55 152152
...... ...... ...... ......
21127265792112726579 153153 55 44
21196785992119678599 154154 55 33
21231546092123154609 155155 44 22
21391546662139154666 156156 33 11

Примечание

Предложения 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) и более поздних версий.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

Указывает, что окно начинается с первой строки секции.Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING может быть указано только как начальная точка окна.UNBOUNDED PRECEDING can only be specified as window starting point.

<unsigned value specification> PRECEDING<unsigned value specification> PRECEDING
Указывается с <unsigned value specification> для обозначения числа строк или значений перед текущей строкой.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) и более поздних версий.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

Указывает, что окно начинается или заканчивается на текущей строке при использовании совместно с предложением 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 ANDBETWEEN AND

Область применения: SQL Server 2012 (11.x)SQL Server 2012 (11.x) и более поздних версий.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

BETWEEN <window frame bound > AND <window frame bound >  

Используется совместно с предложением ROWS или RANGE для указания нижней (начальной) или верхней (конечной) граничной точки окна.Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. <window frame bound> определяет граничную начальную точку, а <window frame bound> определяет граничную конечную точку.<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) и более поздних версий.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

Указывает, что окно заканчивается на последней строке секции.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.

<unsigned value specification> FOLLOWING<unsigned value specification> FOLLOWING
Указывается с <unsigned value specification> для обозначения числа строк или значений после текущей строки.Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. Если в качестве начальной точки окна указан FOLLOWING <unsigned value specification>, конечная точка должна быть <unsigned value specification>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) и более поздних версий.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

Положительный целочисленный литерал (включая 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 и используется <window frame preceding> для <window frame extent> (короткий синтаксис), то данная спецификация используется в качестве начальной точки границы рамки окна, а 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 <unsigned value specification> или FOLLOWING <unsigned value specification>.RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.

В зависимости от функции (ранжирующая, агрегатная или аналитическая), используемой с предложением OVER, <ORDER BY clause> и/или <ROWS and RANGE clause> могут не поддерживаться.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_NUMBERUsing 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. Предложение ORDER BY, указанное в предложении OVER упорядочивает строки каждой секции по столбцу SalesYTD.The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. Предложение ORDER BY в инструкции SELECT определяет порядок, в котором возвращается весь результирующий набор запроса.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. Использование предложения 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.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. Нахождение скользящей средней и кумулятивной суммы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. Предложение ORDER BY, указанное в предложении OVER, определяет логический порядок применения функции 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. Предложение ORDER BY, указанное в инструкции SELECT, определяет порядок отображения строк запроса.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. Указание предложения ROWSSpecifying the ROWS clause

Область применения: SQL Server 2012 (11.x)SQL Server 2012 (11.x) и более поздних версий.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later.

В следующем примере с помощью предложения 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  

В следующем примере предложение ROWS указывается с UNBOUNDED PRECEDING.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. Использование предложения OVER с функцией ROW_NUMBERUsing 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. Использование предложения 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)
Полезная запись блога о функциях окна и предложении OVER на сайте sqlmag.com. Автор: Ицик Бег-Ган (Itzik Ben-Gan)Excellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan