SELECT – Cláusula OVER (Transact-SQL)SELECT - OVER Clause (Transact-SQL)

ESTE TÓPICO APLICA-SE A: simSQL Server (a partir de 2008)simBanco de Dados SQL do Microsoft AzuresimAzure SQL Data Warehouse simParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Determina o particionamento e a ordenação de um conjunto de linhas antes da aplicação da função de janela associada.Determines the partitioning and ordering of a rowset before the associated window function is applied. Isto é, a cláusula OVER defines uma janela ou conjunto de linhas especificado pelo usuário em um conjunto de resultados de consulta.That is, the OVER clause defines a window or user-specified set of rows within a query result set. Uma função de janela computa um valor para cada linha na janela.A window function then computes a value for each row in the window. Você pode usar a cláusula OVER com funções para computar valores agregados como médias móveis, agregações cumulativas, somas acumuladas ou os primeiros N resultados por grupo.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.

SintaxeSyntax

-- 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 ] )  

ArgumentosArguments

PARTITION BYPARTITION BY
Divide o conjunto de resultados da consulta em partições.Divides the query result set into partitions. A função de janela é aplicada separadamente a cada partição e a computação é reiniciada para cada partição.The window function is applied to each partition separately and computation restarts for each partition.

value_expressionvalue_expression
Especifica a coluna pela qual o conjunto de linhas é particionado.Specifies the column by which the rowset is partitioned. value_expression apenas pode se referir a colunas disponibilizadas pela cláusula FROM.value_expression can only refer to columns made available by the FROM clause. value_expression não pode se referir a expressões ou aliases na lista de seleção.value_expression cannot refer to expressions or aliases in the select list. value_expression pode ser uma expressão de coluna, subconsulta escalar, função escalar ou variável definida pelo usuário.value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<ORDER BY clause><ORDER BY clause>
Define a ordem lógica das linhas dentro de cada partição do conjunto de resultados.Defines the logical order of the rows within each partition of the result set. Ou seja, especifica a ordem lógica na qual functioncalculation da janela é executado.That is, it specifies the logical order in which the window functioncalculation is performed.

order_by_expressionorder_by_expression
Especifica uma coluna ou expressão na qual ordenar.Specifies a column or expression on which to sort. order_by_expression apenas pode se referir a colunas disponibilizadas pela cláusula FROM.order_by_expression can only refer to columns made available by the FROM clause. Um número inteiro não pode ser especificado para representar um nome de coluna ou alias.An integer cannot be specified to represent a column name or alias.

COLLATE collation_nameCOLLATE collation_name
Especifica que a operação ORDER BY deve ser executada de acordo com o agrupamento especificado em collation_name.Specifies that the ORDER BY operation should be performed according to the collation specified in collation_name. collation_name pode ser um nome de agrupamento do Windows ou um nome de agrupamento SQL.collation_name can be either a Windows collation name or a SQL collation name. Para obter mais informações, consulte Suporte a agrupamentos e Unicode.For more information, see Collation and Unicode Support. COLLATE é aplicável somente a colunas do tipo char, varchar, nchar e nvarchar.COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESCASC | DESC
Define que os valores na coluna especificada devem ser classificados em ordem crescente ou decrescente.Specifies that the values in the specified column should be sorted in ascending or descending order. ASC é a ordem de classificação padrão.ASC is the default sort order. Valores nulos são tratados como os menores valores possíveis.Null values are treated as the lowest possible values.

ROWS | RANGEROWS | RANGE
Aplica-se a: do SQL Server 2012 (11.x)SQL Server 2012 (11.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Limita mais as linhas dentro da partição com a especificação de pontos iniciais e finais na partição.Further limits the rows within the partition by specifying start and end points within the partition. Isso é feito pela especificação de um intervalo de linhas em relação à linha atual por associação lógica ou associação física.This is done by specifying a range of rows with respect to the current row either by logical association or physical association. A associação física é obtida com o uso de uma cláusula ROWS.Physical association is achieved by using the ROWS clause.

A cláusula ROWS limita as linhas dentro de uma partição especificando um número fixo de linhas antes ou depois da linha atual.The ROWS clause limits the rows within a partition by specifying a fixed number of rows preceding or following the current row. Alternativamente, a cláusula RANGE limita as linhas logicamente dentro de uma partição especificando um intervalo de valores em relação ao valor na linha atual.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. As linhas precedentes e seguintes são definidas com base na classificação na cláusula ORDER BY.Preceding and following rows are defined based on the ordering in the ORDER BY clause. O quadro de janela "RANGE …The window frame “RANGE … CURRENT ROW …"CURRENT ROW …” inclui todas as linhas que têm os mesmos valores na expressão ORDER BY da linha atual.includes all rows that have the same values in the ORDER BY expression as the current row. Por exemplo, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa que a janela de linhas em que a função funciona tem três linhas de tamanho, iniciando com 2 linhas antes e incluindo a linha atual.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.

Observação

ROWS ou RANGE requer que a cláusula ORDER BY seja especificada.ROWS or RANGE requires that the ORDER BY clause be specified. Se ORDER BY contiver várias expressões de ordem, CURRENT ROW FOR RANGE considerará todas as colunas na lista ORDER BY ao determinar a linha atual.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
Aplica-se a: do SQL Server 2012 (11.x)SQL Server 2012 (11.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Especifica que a janela inicia na primeira linha da partição.Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING só pode ser especificado como ponto de partida da janela.UNBOUNDED PRECEDING can only be specified as window starting point.

<unsigned value specification> PRECEDING<unsigned value specification> PRECEDING
Especificado com <unsigned value specification> para indicar o número de linhas ou valores que precederão a linha atual.Specified with <unsigned value specification>to indicate the number of rows or values to precede the current row. Essa especificação não é permitida para RANGE.This specification is not allowed for RANGE.

CURRENT ROWCURRENT ROW
Aplica-se a: do SQL Server 2012 (11.x)SQL Server 2012 (11.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Especifica que a janela inicia ou termina na linha atual quando usada com ROWS ou o valor atual quando usado com 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 pode ser especificado como um ponto de partida e ponto final.CURRENT ROW can be specified as both a starting and ending point.

BETWEEN <window frame bound > AND <window frame bound >BETWEEN <window frame bound > AND <window frame bound >
Aplica-se a: do SQL Server 2012 (11.x)SQL Server 2012 (11.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Usado com ROWS ou RANGE para especificar os pontos de limite inferiores (inicial) e superiores (finais) da janela.Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. <window frame bound> define o ponto de partida do limite e <window frame bound> define o ponto de término do limite.<window frame bound> defines the boundary starting point and <window frame bound> defines the boundary end point. O limite superior não pode ser menor que o limite inferior.The upper bound cannot be smaller than the lower bound.

UNBOUNDED FOLLOWINGUNBOUNDED FOLLOWING
Aplica-se a: do SQL Server 2012 (11.x)SQL Server 2012 (11.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

Especifica que a janela termina na última linha da partição.Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING só pode ser especificado como ponto de extremidade da janela.UNBOUNDED FOLLOWING can only be specified as a window end point. Por exemplo RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING define uma janela que inicia com a linha atual e termina com a última linha da partição.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
Especificado com <unsigned value specification> para indicar o número de linhas ou valores após a linha atual.Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. Quando <unsigned value specification> FOLLOWING é especificado como o ponto de partida da janela, o ponto de extremidade deve ser <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. Por exemplo, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING define uma janela que começa com a segunda linha após a linha atual e termina com a décima linha após a linha atual.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. Essa especificação não é permitida para RANGE.This specification is not allowed for RANGE.

Um literal de inteiro sem sinalunsigned integer literal
Aplica-se a: do SQL Server 2012 (11.x)SQL Server 2012 (11.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

É um literal de inteiro positivo (inclusive 0) que especifica o número de linhas ou valores para inserir antes ou depois da linha ou valor atual.Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. Essa especificação é válida somente para ROWS.This specification is valid only for ROWS.

Comentários geraisGeneral Remarks

Mais de uma função de janela pode ser usado em uma única consulta com uma única cláusula FROM.More than one window function can be used in a single query with a single FROM clause. A cláusula OVER para cada função pode ser diferente no particionamento e na ordenação.The OVER clause for each function can differ in partitioning and ordering.

Se PARTITION BY não for especificado, a função tratará todas as linhas do conjunto de resultados da consulta como um único grupo.If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.

Importante:Important!

Se ROWS/RANGE for especificado e <window frame preceding> for usado para <window frame extent> (sintaxe curta), essa especificação será usada para o ponto de partida de limite de quadro de janela e CURRENT ROW será usado para o ponto de extremidade do limite.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. Por exemplo "ROWS 5 PRECEDING" é igual a "ROWS BETWEEN 5 PRECEDING AND CURRENT ROW."For example “ROWS 5 PRECEDING” is equal to “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.

Observação

Se ORDER BY não for especificado, toda a partição será usada para um quadro de janela.If ORDER BY is not specified entire partition is used for a window frame. Isso só se aplica a funções que não requerem a cláusula ORDER BY.This applies only to functions that do not require ORDER BY clause. Se ROWS/RANGE não for especificado, mas ORDER BY for especificado, RANGE UNBOUNDED PRECEDING AND CURRENT ROW é usado como padrão para quadro de janela.If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. Isso só se aplica a funções que podem aceitar a especificação de ROWS/RANGE opcional.This applies only to functions that have can accept optional ROWS/RANGE specification. Por exemplo, as funções de classificação não podem aceitar ROWS/RANGE, portanto, esse quadro de janela não é aplicado, mesmo que ORDER BY esteja presente e ROWS/RANGE não.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.

Limitações e restriçõesLimitations and Restrictions

A cláusula OVER não pode ser usada com a função de agregação CHECKSUM.The OVER clause cannot be used with the CHECKSUM aggregate function.

RANGE não pode ser usado com <unsigned value specification> PRECEDING ou <unsigned value specification> FOLLOWING.RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.

Dependendo da função de classificação, agregação ou analítica usada com a cláusula OVER, talvez não haja suporte para <ORDER BY clause> e/ou <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.

ExemplosExamples

A.A. Usando a cláusula OVER com a função ROW_NUMBERUsing the OVER clause with the ROW_NUMBER function

O exemplo a seguir mostra como usar a cláusula OVER com a função ROW_NUMBER para exibir um número de linha para cada linha em uma partição.The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. A cláusula ORDER BY especificada na cláusula OVER ordena as linhas em cada partição pela coluna SalesYTD.The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. A cláusula ORDER BY na instrução SELECT determina a ordem na qual todo o conjunto de resultados da consulta é retornado.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  

Aqui está o conjunto de resultados.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. Usando a cláusula OVER com funções de agregaçãoUsing the OVER clause with aggregate functions

O exemplo a seguir usa a cláusula OVER com funções de agregação sobre todas as linhas retornadas pela consulta.The following example uses the OVER clause with aggregate functions over all rows returned by the query. Neste exemplo, o uso da cláusula OVER é mais eficiente que o uso de subconsultas para derivar os valores agregados.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  

Aqui está o conjunto de resultados.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  

O exemplo a seguir mostra o uso da cláusula OVER com uma função de agregação em um valor calculado.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  

Aqui está o conjunto de resultados.Here is the result set. Observe que as agregações são calculadas por SalesOrderID e o Percent by ProductID é calculado para cada linha de cada 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.C. Gerando uma média móvel e o total cumulativoProducing a moving average and cumulative total

O exemplo a seguir usa as funções AVG e SUM com a cláusula OVER para fornecer uma média móvel e um total cumulativo de vendas anuais para cada território na tabela 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. Os dados são particionados por TerritoryID e ordenados logicamente por SalesYTD.The data is partitioned by TerritoryID and logically ordered by SalesYTD. Isso significa que a função AVG é computada para cada território com base no ano de vendas.This means that the AVG function is computed for each territory based on the sales year. Observe que para TerritoryID 1, há duas linhas para o ano de vendas 2005 que representam os dois vendedores com vendas nesse ano.Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. As vendas médias para essas duas linhas são computadas e a terceira linha que representa vendas do ano 2006 é incluída na computação.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;  

Aqui está o conjunto de resultados.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)  

Neste exemplo, a cláusula OVER não inclui PARTITION BY.In this example, the OVER clause does not include PARTITION BY. Isso significa que a função será aplicada a todas as linhas retornadas pela consulta.This means that the function will be applied to all rows returned by the query. A cláusula ORDER BY especificada na cláusula OVER determina a ordem lógica na qual a função AVG é aplicada.The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. A consulta retorna uma média móvel de vendas por ano para todos os territórios de vendas especificados na cláusula WHERE.The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. A cláusula ORDER BY especificada na instrução SELECT determina a ordem na qual as linhas da consulta são exibidas.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;  

Aqui está o conjunto de resultados.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. Especificando a cláusula ROWSSpecifying the ROWS clause

Aplica-se a: do SQL Server 2012 (11.x)SQL Server 2012 (11.x) ao SQL Server 2017SQL Server 2017.Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

O exemplo a seguir usa a cláusula ROWS para definir uma janela na qual as linhas são computadas como a linha atual e o número N de linhas que seguem (linha 1 neste exemplo).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;  

Aqui está o conjunto de resultados.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  

No exemplo a seguir, a cláusula ROWS é especificada com UNBOUNDED PRECEDING.In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. O resultado é que a janela inicia na primeira linha da partição.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;  

Aqui está o conjunto de resultados.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  

Exemplos: Parallel Data WarehouseParallel Data WarehouseExamples: Parallel Data WarehouseParallel Data Warehouse

E.E. Usando a cláusula OVER com a função ROW_NUMBERUsing the OVER clause with the ROW_NUMBER function

O exemplo a seguir retorna o ROW_NUMBER para representantes de vendas com base em suas cotas de vendas atribuídas.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;  

Este é um conjunto de resultados parcial.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. Usando a cláusula OVER com funções de agregaçãoUsing the OVER clause with aggregate functions

Os exemplos a seguir mostram como usar a cláusula OVER com funções de agregação.The following examples show using the OVER clause with aggregate functions. Neste exemplo, o uso da cláusula OVER é mais eficiente do que o uso de subconsultas.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;  

Aqui está o conjunto de resultados.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  

O exemplo a seguir mostra o uso da cláusula OVER com uma função de agregação em um valor calculado.The following example shows using the OVER clause with an aggregate function in a calculated value. Observe que as agregações são calculadas por SalesOrderNumber e o percentual da ordem de venda total é calculada para cada linha de cada 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;  

A primeira inicialização desse conjunto de resultados é: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  

Consulte TambémSee Also

Funções de agregação (Transact-SQL) Aggregate Functions (Transact-SQL)
Funções analíticas (Transact-SQL) Analytic Functions (Transact-SQL)
Excelente postagem no blog sobre funções de janela e OVER, em sqlmag.com, por Itzik Ben-GanExcellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan