SELECT – Cláusula OVER (Transact-SQL) (Português)

• 11/08/2017
• 17 minutos para ler
• • V
  • L
  • c
  • j
  • M
  • +11

Aplica-se a: SQL Server (todos versões compatíveis) Banco de dados SQL do Azure Instância gerenciada do Azure SQL Azure Synapse Analytics Data Warehouse paralelo

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 conjunto de linhas especificado pelo usuário em um conjunto de resultados de consulta. Uma função de janela então 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, agregados cumulativos, totais corridos ou N principais por resultados de grupo.

• Funções de classificação

• Funções agregadas

• Funções analíticas

• PRÓXIMO VALOR PARA a função

Convenções de sintaxe Transact-SQL

Sintaxe

Argumentos

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.
• ROWS / RANGE que limita as linhas dentro da partição especificando os pontos inicial e final dentro da partição. Ele requer ORDER BY argumento 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 da janela serão aplicadas em todo o conjunto de resultados.

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

Divide o conjunto de resultados da consulta em partições. A função de janela é aplicada a cada partição separadamente e o cálculo é reiniciado para cada partição íon.

Se PARTITION BY não for especificado, a função trata todas as linhas do conjunto de resultados da consulta como uma única partição. A função será aplicada em todas as linhas na partição se você não especificar a cláusula ORDER BY.

PARTITION BY value_expression

Especifica a coluna pela qual o conjunto de linhas é particionado. value_expression só pode se referir a colunas disponibilizadas 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.

ORDER BY

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

• Se não for especificado, a ordem padrão é ASC e a função de janela usará todas as linhas na partição.
• Se for especificado, e ROWS / RANGE não for especificado, o padrão RANGE UNBOUNDED PRECEDING AND CURRENT ROW é usado como padrão para a janela frame pelas funções que podem aceitar a especificação opcional ROWS / RANGE (por exemplo min ou max).

order_by_expression
Especifica uma coluna ou expressão a ser classificada. order_by_expression só pode se referir a colunas disponibilizadas pela cláusula FROM. Um número 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 Suporte a agrupamento e Unicode. COLLATE é aplicável apenas para colunas do tipo char, varchar, nchar e 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.Valores nulos são tratados como os menores valores possíveis.

ROWS ou RANGE

Aplica-se a: SQL Server 2012 (11.x) e posterior.

Limites adicionais as linhas dentro da partição especificando pontos de início e fim dentro da partição. Isso é feito especificando 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 em uma partição especificando um número fixo de linhas antes ou depois da 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 posteriores são definidas com base na ordenação na cláusula ORDER BY. A moldura da janela “RANGE … CURRENT ROW …” inclui todas as linhas que possuem os mesmos valores na expressão ORDER BY da 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 precedentes e incluindo a linha atual.

UNBOUNDED PRECEDING

Aplica-se a: SQL Server 2012 (11.x) e posterior.

Especifica que a janela começa na primeira linha da partição. UNBOUNDED PRECEDING só pode ser especificado como ponto de início da janela.

< especificação de valor sem sinal > ANTES
Especificado com < especificação de valor sem sinal > para indicar o número de linhas ou valores que precedem a linha atual. Esta especificação não é permitida para RANGE.

CURRENT ROW

Aplica-se a: SQL Server 2012 (11.x) e posterior.

Especifica que a janela é iniciada ou termina na linha atual quando usado com ROWS ou o valor atual quando usado com RANGE. A LINHA ATUAL pode ser especificada como ponto inicial e final.

ENTRE E

Aplica-se a: SQL Server 2012 (11.x) e posterior.

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

SEGUINDO DESBLOQUEADO

Aplica-se a: SQL Server 2012 (11.x) e posterior.

Especifica que a janela termina na última linha da partição. UNBOUNDED FOLLOWING só pode ser especificado como um ponto final da 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 sem sinal > SEGUINDO
Especificado com < especificação de valor sem sinal > para indicar o número de linhas ou valores para seguir a linha atual. Quando < especificação de valor sem sinal > FOLLOWING é especificado como o ponto inicial da janela, o ponto final deve ser < especificação de valor sem sinal > SEGUINDO. 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 sem sinal
Aplica-se a: SQL Server 2012 (11.x) e posterior.

É um literal inteiro positivo (incluindo 0 ) que especifica o número de linhas ou valores para preceder ou seguir a linha ou valor atual. Esta especificação é válida apenas para ROWS.

Observações gerais

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 no particionamento e ordenação.

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

Importante !

Se ROWS / RANGE for especificado e < o frame da janela anterior a > for usado para < extensão do quadro da janela > (sintaxe curta), então esta especificação é usada para o ponto inicial do limite do quadro e CURRENT ROW é usado para o ponto final do limite. Por exemplo, “ROWS 5 PRECEDING” é igual a “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”.

Limitações e restrições

A cláusula OVER não pode ser usada com a função de agregação CHECKSUM.

RANGE não pode ser usado com < especificação de valor sem sinal > ANTES ou < especificação de valor sem sinal > SEGUINDO.

Dependendo da classificação, agregado ou analítico função usada com a cláusula OVER, < cláusula ORDER BY > e / ou < A cláusula ROWS e RANGE > pode não ser compatível.

Exemplos

A. Usando a cláusula OVER com a função ROW_NUMBER

O exemplo a seguir mostra o uso da cláusula OVER com a função ROW_NUMBER para exibir um número de linha para cada linha em 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 em que todo o conjunto de resultados da consulta é retornado.

Aqui está o conjunto de resultados.

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

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.

Aqui está o conjunto de resultados.

O exemplo a seguir mostra o uso da cláusula OVER com uma função agregada em um valor calculado.

Aqui está o conjunto de resultados. Observe que os agregados são calculados por SalesOrderID e Percent by ProductID é calculado para cada linha de cada SalesOrderID.

C. Produzindo uma média móvel e um total cumulativo

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 no Sales.SalesPerson tabela. Os dados são particionados por TerritoryID e ordenados logicamente por SalesYTD. Isso significa que a função AVG é calculada para cada território com base no ano de vendas. Observe que para TerritoryID 1, há duas linhas para o ano de vendas de 2005, representando os dois vendedores com vendas naquele ano. A média de vendas para essas duas linhas é calculada e, em seguida, a terceira linha que representa as vendas para o ano de 2006 é incluída no cálculo.

Aqui está o conjunto de resultados.

Neste exemplo, a cláusula OVER não inclui PARTITION BY. Isso significa que a função será aplicada a todas as linhas retornadas pela consulta. A cláusula ORDER BY especificada na cláusula OVER determina a ordem lógica na 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.

Aqui está o conjunto de resultados.

D. Especificando a cláusula ROWS

Aplica-se a: SQL Server 2012 (11.x) e posterior.

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 o número N de linhas que se seguem (1 linha neste exemplo).

Aqui está o conjunto de resultados.

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.

Aqui está o conjunto de resultados.

Exemplos: Data Warehouse paralelo

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

O exemplo a seguir retorna ROW_NUMBER para representantes de vendas com base em sua cota de vendas atribuída.

Aqui está um conjunto de resultados parcial.

F. 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, usar a cláusula OVER é mais eficiente do que usar subconsultas.

Aqui está o conjunto de resultados.

O exemplo a seguir mostra o uso da cláusula OVER com uma função agregada em um valor calculado. Observe que os agregados são calculados por SalesOrderNumber e a porcentagem do pedido total de vendas é calculada para cada linha de cada SalesOrderNumber.

O primeiro início deste conjunto de resultados é:

Consulte também

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