Cláusula SELECT - OVER (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System) Ponto de extremidade de análise do SQL Warehouse no Microsoft Fabric

A OVER cláusula determina o particionamento e a ordenação de um conjunto de linhas antes que a função de janela associada seja aplicada. Ou seja, a OVER cláusula define uma janela ou um conjunto de linhas especificado pelo usuário dentro de um conjunto de resultados de consulta. Uma função de janela computa um valor para cada linha na janela. Você pode usar a OVER cláusula com funções para calcular valores agregados, como médias móveis, agregações cumulativas, totais acumulados ou N principais resultados por grupo.

• Funções de classificação
• Funções agregadas
• Funções analíticas
• NEXT VALUE FOR

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para SQL Server, Banco de Dados SQL do Azure e 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> }

Sintaxe do Parallel Data Warehouse.

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Argumentos

As funções da janela podem ter os seguintes argumentos na cláusula OVER:

• PARTITION BY divide o conjunto de resultados da consulta em partições.

• ORDER BY define a ordem lógica das linhas dentro de cada partição do conjunto de resultados.

• ROWS ou RANGE que limita as linhas dentro da partição especificando os pontos inicial e final dentro da partição. Isso requer o argumento ORDER BY, e o valor padrão é do início da partição até o elemento atual se o argumento ORDER BY for especificado.

Se você não especificar nenhum argumento, as funções de janela serão aplicadas em todo o conjunto de resultados.

SELECT object_id,
       MIN(object_id) OVER () AS [min],
       MAX(object_id) OVER () AS [max]
FROM sys.objects;

PARTITION BY

Divide o conjunto de resultados da consulta em partições. A função de janela é aplicada separadamente a cada partição e a computação é reiniciada para cada partição.

PARTITION BY <value_expression>

Se PARTITION BY não for especificado, a função tratará todas as linhas do conjunto de resultados da consulta como uma única partição.

A função será aplicada em todas as linhas da partição se você não especificar ORDER BY a cláusula.

PARTITION BY value_expression

Especifica a coluna pela qual o conjunto de linhas é particionado. value_expression só pode se referir a colunas disponibilizadas FROM pela cláusula. value_expression não pode se referir a expressões ou aliases na lista de seleção. value_expression pode ser uma expressão de coluna, subconsulta escalar, função escalar ou variável definida pelo usuário.

SELECT object_id,
       type,
       MIN(object_id) OVER (PARTITION BY type) AS [min],
       MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;

ORDER BY

ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]

Define a ordem lógica das linhas dentro de cada partição do conjunto de resultados. Ou seja, ela especificará a ordem lógica em que o cálculo da função da janela será executado.

• Se não for especificado, a ordem padrão é ASC e a função de janela usa todas as linhas na partição.

• Se especificado e um ROWS ou RANGE não for especificado, default RANGE UNBOUNDED PRECEDING AND CURRENT ROW será usado como padrão para o quadro da janela, pelas funções que podem aceitar uma especificação ou RANGE opcional ROWS (por exemplo, min ou max).

SELECT object_id,
       type,
       MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
       MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;

order_by_expression

Especifica uma coluna ou expressão na qual ordenar. order_by_expression só pode se referir a colunas disponibilizadas FROM pela cláusula. Um inteiro não pode ser especificado para representar um nome de coluna ou alias.

COLLATE collation_name

Especifica que a operação deve ser executada ORDER BY de acordo com a ordenação especificada na collation_name. collation_name pode ser um nome de ordenação do Windows ou um nome de ordenação SQL. Para obter mais informações, consulte Suporte de agrupamento e unicode. COLLATE é aplicável somente a colunas do tipo char, varchar, nchar e nvarchar.

ASC | DESC

Define que os valores na coluna especificada devem ser classificados em ordem crescente ou decrescente. ASCé a ordem de classificação padrão. Valores nulos são tratados como os menores valores possíveis.

LINHAS OU INTERVALO

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.

Limita mais as linhas dentro da partição com a especificação de pontos iniciais e finais na partição. Ele especifica um intervalo de linhas em relação à linha atual por associação lógica ou associação física. A associação física é obtida usando a ROWS cláusula.

A ROWS cláusula limita as linhas dentro de uma partição especificando um número fixo de linhas que precedem ou seguem a linha atual. Como alternativa, a RANGE cláusula limita logicamente as linhas dentro de uma partição especificando um intervalo de valores em relação ao valor na linha atual. As linhas anteriores e seguintes são definidas com base na ordenação na ORDER BY cláusula. O quadro RANGE ... CURRENT ROW ... da janela inclui todas as linhas que têm os mesmos valores na ORDER BY expressão que a linha atual. Por exemplo, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW significa que a janela de linhas em que a função opera tem três linhas de tamanho, começando com 2 linhas anteriores até e incluindo a linha atual.

SELECT object_id,
       COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
       COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
       COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;

ROWS ou RANGE requer que você especifique a ORDER BY cláusula. Se ORDER BY contiver várias expressões de pedido, CURRENT ROW FOR RANGE considerará todas as colunas da ORDER BY lista ao determinar a linha atual.

UNBOUNDED PRECEDING

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.

Especifica que a janela inicia na primeira linha da partição. UNBOUNDED PRECEDING só pode ser especificado como ponto de partida da janela.

<unsigned value specification> PRECEDING

Especificado com <unsigned value specification> para indicar o número de linhas ou valores a serem precedidos pela linha atual. Essa especificação não é permitida para RANGE.

CURRENT ROW

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.

Especifica que a janela começa ou termina na linha atual quando usada com ROWS ou o valor atual quando usada com RANGE. CURRENT ROW pode ser especificado como um ponto inicial e final.

BETWEEN AND

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.

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

Usado com or ROWS RANGE para especificar os pontos de limite inferior (inicial) e superior (final) da janela. <window frame bound> define o ponto de partida do limite e <window frame bound> define o ponto final do limite. O limite superior não pode ser menor que o limite inferior.

UNBOUNDED FOLLOWING

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.

Especifica que a janela termina na última linha da partição. UNBOUNDED FOLLOWING só pode ser especificado como um ponto de extremidade de janela. Por exemplo, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING define uma janela que começa com a linha atual e termina com a última linha da partição.

<unsigned value specification> FOLLOWING

Especificado com <unsigned value specification> para indicar o número de linhas ou valores que sucede a linha atual. Quando <unsigned value specification> FOLLOWING é especificado como o ponto inicial da janela, o ponto final deve ser <unsigned value specification> FOLLOWING. Por exemplo, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING define uma janela que começa com a segunda linha que segue a linha atual e termina com a décima linha que segue a linha atual. Essa especificação não é permitida para RANGE.

<literal inteiro sem sinal>

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.

Um literal inteiro positivo (incluindo 0) que especifica o número de linhas ou valores a serem precedidos ou seguidos à linha ou ao valor atual. Esta especificação é válida apenas para ROWS.

Comentários

Mais de uma função de janela pode ser usada em uma única consulta com uma única FROM cláusula. A OVER cláusula para cada função pode diferir no particionamento e na ordenação.

Se PARTITION BY não for especificado, a função tratará todas as linhas do conjunto de resultados da consulta como um único grupo.

Se ORDER BY não for especificado, toda a partição será usada para um quadro de janela. Isso se aplica apenas a funções que não exigem ORDER BY cláusula. Se ROWS ou RANGE não for especificado, mas ORDER BY for especificado, RANGE UNBOUNDED PRECEDING AND CURRENT ROW é usado como padrão para o quadro da janela. Isso se aplica somente a funções que podem aceitar opcional ROWS ou RANGE especificação. Por exemplo, as funções de classificação não podem aceitar ROWS ou RANGE, portanto, esse quadro de janela não é aplicado, mesmo que ORDER BY esteja presente e ROWS ou RANGE não.

Limitações

A OVER cláusula não pode ser usada com as DISTINCT agregações.

RANGE não pode ser usado com <unsigned value specification> PRECEDING ou <unsigned value specification> FOLLOWING.

Dependendo da classificação, agregação ou função analítica usada com a OVER cláusula e/ <ORDER BY clause> ou a <ROWS and RANGE clause> pode não ser suportada.

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

R. Use a cláusula OVER com a função ROW_NUMBER

O exemplo a seguir mostra o uso da OVER cláusula com ROW_NUMBER a função para exibir um número de linha para cada linha dentro de uma partição. A cláusula ORDER BY especificada na cláusula OVER ordena as linhas em cada partição pela coluna SalesYTD. A ORDER BY cláusula na SELECT instrução determina a ordem na qual todo o conjunto de resultados da consulta é retornado.

USE AdventureWorks2022;
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

Veja a seguir o conjunto de resultados.

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. Usar a cláusula OVER com funções agregadas

O exemplo a seguir usa a cláusula OVER com funções de agregação sobre todas as linhas retornadas pela consulta. Neste exemplo, o uso da cláusula OVER é mais eficiente que o uso de subconsultas para derivar os valores agregados.

USE AdventureWorks2022;
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

Veja a seguir o conjunto de resultados.

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.

USE AdventureWorks2022;
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

Veja a seguir o conjunto de resultados. Os agregados são calculados por SalesOrderID e o Percent by ProductID é calculado para cada linha de cada 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

C. Produza uma média móvel e um total acumulado

O exemplo a seguir usa as AVG funções and SUM com a OVER cláusula para fornecer uma média móvel e um total acumulado de vendas anuais para cada território na Sales.SalesPerson tabela. Os dados são particionados por TerritoryID e ordenados logicamente por SalesYTD. Isso significa que a AVG função é calculada para cada território com base no ano de vendas. Para TerritoryID de 1, há duas linhas para o ano 2005 de vendas representando os dois vendedores com vendas naquele ano. A média de vendas dessas duas linhas é calculada e, em seguida, a terceira linha que representa as vendas do ano 2006 é incluída no cálculo.

USE AdventureWorks2022;
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;

Veja a seguir o conjunto de resultados.

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

Neste exemplo, a OVER cláusula não inclui PARTITION BY. Isso significa que a função é aplicada a todas as linhas retornadas pela consulta. A ORDER BY cláusula especificada na OVER cláusula determina a ordem lógica à qual a AVG função é aplicada. A consulta retorna uma média móvel de vendas por ano para todos os territórios de vendas especificados na WHERE cláusula. A ORDER BY cláusula especificada na SELECT instrução determina a ordem na qual as linhas da consulta são exibidas.

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;

Veja a seguir o conjunto de resultados.

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

D. Especificar a cláusula ROWS

Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.

O exemplo a seguir usa a ROWS cláusula para definir uma janela na qual as linhas são calculadas como a linha atual e o número N de linhas que se seguem (uma linha neste exemplo).

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;

Veja a seguir o conjunto de resultados.

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 ROWS cláusula é especificada com UNBOUNDED PRECEDING. O resultado é que a janela inicia na primeira linha da partição.

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;

Veja a seguir o conjunto de resultados.

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: PDW (Analytics Platform System)

E. Use a cláusula OVER com a função ROW_NUMBER

O exemplo a seguir retorna o ROW_NUMBER de representantes de vendas com base em suas cotas de vendas atribuídas.

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.

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. Usar a cláusula OVER com funções agregadas

Os exemplos a seguir mostram o uso da OVER cláusula com funções de agregação. Neste exemplo, usar a OVER cláusula é mais eficiente do que usar subconsultas.

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;

Veja a seguir o conjunto de resultados.

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. Os agregados são calculados por SalesOrderNumber e a porcentagem do pedido de venda total é calculada para cada linha de cada SalesOrderNumber.

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;

O primeiro início desse conjunto de resultados é o seguinte:

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

Conteúdo relacionado

• Funções de agregação (Transact-SQL)
• Funções analíticas (Transact-SQL)
• Excelente postagem no blog sobre funções de janela e OVER, em sqlmag.com, por Itzik Ben-Gan