Compartilhar via


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 de janela de agregação. Para obter mais informações, consulte Funções agregadas (Transact-SQL).

Ícone de vínculo de tópicoConvenções de sintaxe Transact-SQL

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. Para obter mais informações, consulte Cláusula ORDER BY [Transact-SQL].

    Observação importanteImportante

    Quando 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 ORDENADO BY> não pode ser usada com funções de janela de agregação.

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 agregação. 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 de agregação 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 de agregação 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 AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;
GO

B. Usando a cláusula OVER com funções de agregação

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 AdventureWorks;
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

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 AdventureWorks;
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

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

Consulte também

Referência