Cláusula OVER (Transact-SQL)
Determina o particionamento e a ordenação do conjunto de linhas antes da aplicação da função de janela associada.
Aplica-se a:
Classificando funções de janela
Funções janela agregada. Para obter mais informações, consulte Funções agregadas (Transact-SQL).
.gif "Ícone de vínculo de tópico")
Sintaxe
Ranking Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ]
<ORDER BY_Clause> )
Aggregate Window Functions
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY value_expression , ... [ n ] ] )
Argumentos
PARTITION BY
Divide o conjunto de resultados em partições. A função de janela é aplicada separadamente a cada partição e a computação é reiniciada para cada partição.value_expression
Especifica a coluna pela qual o conjunto de linhas produzido pela cláusula FROM correspondente é particionado. value_expression pode se referir somente a colunas que se tornam disponíveis pela cláusula FROM. 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.<Cláusula ORDER BY>
Especifica a ordem a ser aplicada à função de janela de classificação. For more information, see Cláusula ORDER BY [Transact-SQL]..gif "Observação importante")
ImportanteQuando usada no contexto de uma função de janela de classificação, a <Cláusula ORDER BY> só pode se referir a colunas que se tornam disponíveis pela cláusula FROM. Um inteiro não pode ser especificado para representar a posição do nome ou alias de uma coluna na lista de seleção. A <Cláusula ORDER BY> não pode ser usada com funções de janela agregada.
Comentários
As funções de janela estão definidas no ISO SQL padrão. O SQL Server fornece funções de janela de classificação e agregada. Uma janela é um conjunto de linhas especificado pelo usuário. Uma função de janela computa um valor para cada linha em um conjunto de resultados derivado da janela.
Mais de uma função de janela agregada ou de classificação pode ser usado em uma única consulta com uma única cláusula FROM. Entretanto, a cláusula OVER para cada função pode ser diferente no particionamento e na ordenação. A cláusula OVER não pode ser usada com a função agregada de CHECKSUM.
Exemplos
A. Usando a cláusula OVER com a função ROW_NUMBER
Cada função de classificação, ROW_NUMBER, DENSE_RANK, Grau, NTILE usa a cláusula OVER. O exemplo a seguir mostra o uso da cláusula OVER com ROW_NUMBER.
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
B. Usando a cláusula OVER com funções agregadas
Os exemplos a seguir mostram o uso da cláusula OVER com funções de agregação. Neste exemplo, o uso da cláusula OVER é mais eficiente que o uso de subconsultas.
USE AdventureWorks2008R2;
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 agregada em um valor calculado.
USE AdventureWorks2008R2;
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. Observe que os agregados são calculados por SalesOrderID e o Percent by ProductID é calculado para cada linha de cada SalesOrderID.
SalesOrderID |
ProductID |
OrderQty |
Total |
Porcentagem por 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.43 |
43664 |
773 |
1 |
14 |
7.14 |
43664 |
778 |
1 |
14 |
7.14 |