Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
SQL ServerBase de Dados SQL do AzureInstância Gerida do Azure SQLAzure Synapse AnalyticsSistema de Plataforma de Análise (PDW)Ponto de Extremidade de Análise SQL no Microsoft FabricArmazém no Microsoft FabricBase de Dados SQL no Microsoft Fabric
A cláusula OVER 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 cláusula OVER define uma janela ou um conjunto de linhas especificado pelo usuário dentro de um conjunto de resultados de consulta. Em seguida, uma função de janela calcula um valor para cada linha na janela. Você pode usar a cláusula OVER com funções para calcular valores agregados, como médias móveis, agregações cumulativas, totais em execução ou N por resultados de grupo.
Transact-SQL convenções de sintaxe
Syntax
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 para Parallel Data Warehouse.
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
Arguments
As funções de janela podem ter os seguintes argumentos em sua cláusula OVER:
PARTITION BY que divide o conjunto de resultados da consulta em partições.
ORDER BY que define a ordem lógica das linhas dentro de cada partição do conjunto de resultados.
LINHAS ou INTERVALO que limita as linhas dentro da partição, especificando pontos de início e fim dentro da partição. Ele requer
ORDER BYargumento e o valor padrão é do início da partição para o elemento atual se o argumentoORDER BYfor 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;
| object_id | min | max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTIÇÃO POR
Divide o conjunto de resultados da consulta em partições. A função de janela é aplicada a cada partição separadamente 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 é aplicada em todas as linhas da partição se você não especificar ORDER BY cláusula.
PARTIÇÃO POR value_expression
Especifica a coluna pela qual o conjunto de linhas é particionado.
value_expression só pode referir-se a colunas disponibilizadas pela cláusula FROM.
value_expression não pode fazer referência 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;
| object_id | tipo | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
ORDENAR POR
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, especifica a ordem lógica na qual o cálculo da função de janela é executado.
Se não for especificado, a ordem padrão será
ASCe a função de janela usará todas as linhas na partição.Se especificado e um
ROWSouRANGEnão for especificado, oRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWpadrão será usado como padrão para o quadro da janela, pelas funções que podem aceitar uma especificação opcionalROWSouRANGE(por exemplo,minoumax).
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;
| object_id | tipo | min | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
Especifica uma coluna ou expressão na qual classificar.
order_by_expression só pode referir-se a colunas disponibilizadas pela cláusula FROM. Um inteiro não pode ser especificado para representar um nome de coluna ou alias.
COLLATE collation_name
Especifica que a operação ORDER BY deve ser executada de acordo com o agrupamento especificado em collation_name.
collation_name pode ser um nome de agrupamento do Windows ou um nome de agrupamento SQL. Para obter mais informações, consulte de suporte a agrupamento e Unicode .
COLLATE é aplicável apenas para colunas do tipo char, varchar, nchare nvarchar.
ASC | DESC
Especifica que os valores na coluna especificada devem ser classificados em ordem crescente ou decrescente.
ASC é a ordem de classificação padrão. Os valores nulos são tratados como os valores mais baixos possíveis.
LINHAS ou INTERVALO
Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.
Limita ainda mais as linhas dentro da partição, especificando os pontos de início e fim dentro da 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 cláusula ROWS.
A cláusula ROWS limita as linhas dentro de uma partição especificando um número fixo de linhas anteriores ou posteriores à linha atual. Como alternativa, a cláusula RANGE 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 cláusula ORDER BY. O quadro da janela RANGE ... CURRENT ROW ... inclui todas as linhas que têm os mesmos valores na expressão ORDER BY 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 que precedem 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;
| object_id | preceding | central | following |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
ROWS ou RANGE requer que você especifique a cláusula ORDER BY. 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.
PRECEDÊNCIA ILIMITADA
Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.
Especifica que a janela começa na primeira linha da partição.
UNBOUNDED PRECEDING só pode ser especificado como ponto de partida da janela.
<especificação de valor não assinada> ANTERIOR
Especificado com <unsigned value specification> para indicar o número de linhas ou valores a preceder a linha atual. Esta especificação não é permitida para RANGE.
LINHA ATUAL
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.
ENTRE E
Aplica-se a: SQL Server 2012 (11.x) e versões posteriores.
BETWEEN <window frame bound> AND <window frame bound>
Usado com ROWS ou 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 de extremidade do limite. O limite superior não pode ser menor do que o limite inferior.
SEGUIMENTO ILIMITADO
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.
<especificação de valor não assinada> SEGUINTE
Especificado com <unsigned value specification> para indicar o número de linhas ou valores a seguir à linha atual. Quando <unsigned value specification> FOLLOWING é especificado como o ponto de partida 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. Esta especificação não é permitida para RANGE.
< > literal inteiro não assinado
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 preceder ou seguir a linha ou o valor atual. Esta especificação é válida apenas para ROWS.
Remarks
Mais de uma função de janela pode ser usada em uma única consulta com uma única cláusula FROM. A cláusula OVER para cada função pode diferir em particionamento e 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.
Important
Se ROWS ou RANGE for especificado e <window frame preceding> for usado para <window frame extent> (sintaxe curta), essa especificação será usada para o ponto inicial do limite do quadro da janela e CURRENT ROW será usado para o ponto final do limite. Por exemplo, ROWS 5 PRECEDING é igual a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.
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 será usado como padrão para o quadro da janela. Isto aplica-se apenas a funções que podem aceitar ROWS opcionais ou especificações RANGE. Por exemplo, as funções de classificação não podem aceitar ROWS ou RANGE, portanto, essa moldura de janela não é aplicada mesmo que ORDER BY esteja presente e ROWS ou RANGE não esteja.
Limitations
A cláusula OVER não pode ser usada com as agregações DISTINCT.
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 cláusula OVER, <ORDER BY clause> e/ou o <ROWS and RANGE clause> podem não ser suportados.
Examples
Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que você pode baixar da home page Microsoft SQL Server Samples and Community Projects.
A. Use a cláusula OVER com a função ROW_NUMBER
O exemplo a seguir mostra o uso da cláusula OVER com ROW_NUMBER 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 cláusula ORDER BY na instrução SELECT 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
Aqui está 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. Use a cláusula OVER com funções agregadas
O exemplo a seguir usa a cláusula OVER com funções de agregação em todas as linhas retornadas pela consulta. Neste exemplo, usar a cláusula OVER é mais eficiente do que usar 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
Aqui está 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
Aqui está 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. Produzir uma média móvel e um total acumulado
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 acumulado de vendas anuais para cada território na tabela Sales.SalesPerson. Os dados são particionados por TerritoryID e ordenados logicamente por SalesYTD. Isto significa que a função AVG é calculada para cada território com base no ano de vendas. Para TerritoryID de 1, há duas linhas para o ano de vendas 2005 representando os dois vendedores com vendas naquele ano. As vendas médias para essas duas linhas são calculadas e, em seguida, a terceira linha que representa as vendas para o 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;
Aqui está 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 cláusula OVER não inclui PARTITION BY. Isso significa que a função é aplicada a todas as linhas retornadas pela consulta. A cláusula ORDER BY especificada na cláusula OVER determina a ordem lógica à qual a função AVG é aplicada. A consulta retorna uma média móvel de vendas por ano para todos os territórios de vendas especificados na cláusula WHERE. A cláusula ORDER BY especificada na instrução SELECT determina a ordem em que 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;
Aqui está 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 cláusula ROWS para definir uma janela sobre a qual as linhas são calculadas como a linha atual e a N número 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;
Aqui está 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 cláusula ROWS é especificada com UNBOUNDED PRECEDING. O resultado é que a janela começa 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;
Aqui está 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: Analytics Platform System (PDW)
E. Use a cláusula OVER com a função ROW_NUMBER
O exemplo a seguir retorna o ROW_NUMBER para representantes de vendas com base em sua cota de vendas atribuída.
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;
Aqui está um conjunto de resultados parciais.
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. Use a cláusula OVER com funções agregadas
Os exemplos a seguir mostram o uso da cláusula OVER com funções agregadas. Neste exemplo, usar a cláusula OVER é 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;
Aqui está 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 da ordem 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 deste 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