A cláusula OVER foi adicionada ao SQL Server “há muito tempo” no SQL Server 2005 e foi expandida no SQL Server 2012. É usado predominantemente com as “Funções de janela”; a única exceção é a função de sequência NEXT VALUE FOR. A cláusula OVER é usada para determinar quais linhas da consulta são aplicadas à função, em que ordem são avaliadas por essa função e quando os cálculos da função devem reiniciar. Uma vez que é usada em conjunto com outras funções, e este artigo trata especificamente apenas da cláusula OVER, essas funções serão discutidas apenas no que se refere à cláusula OVER nos exemplos dados.

A sintaxe de a cláusula OVER é:

<function> OVER ( )

Observando a sintaxe, parece que todas as subcláusulas são opcionais. Na verdade, cada função que pode usar a cláusula OVER determina quais das subcláusulas são permitidas e quais são obrigatórias. Dependendo da função que está sendo usada, a própria cláusula OVER pode ser opcional. Há um gráfico no final deste artigo que mostra quais funções permitem / exigem quais partes da cláusula OVER.

A cláusula PARTITION BY é usada para dividir o conjunto de resultados da consulta em subconjuntos de dados, ou partições. Se a cláusula PARTITION BY não for usada, todo o conjunto de resultados da consulta será a partição que será usada. A função de janela em uso é aplicada a cada partição separadamente e o cálculo que a função executa é reiniciado para cada partição. Você define um conjunto de valores que determinam a (s) partição (ões) em que dividir a consulta. Esses valores podem ser colunas, funções escalares, subconsultas escalares ou variáveis.

Por exemplo, vamos examinar a seguinte consulta:

SELECT COUNT(*)FROM .sys.indexes;

Esta consulta retorna o seguinte conjunto de resultados:

Este é simplesmente o número de linhas retornadas pela consulta – neste caso, o número de índices no banco de dados msdb. Agora vamos adicionar a cláusula OVER a esta consulta:

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

Os resultados resumidos são:

Esta consulta retorna o object_id e index_id para cada índice, e o número total de índices no conjunto de resultados. Como uma cláusula PARTITION BY não foi usada, todo o conjunto de resultados foi tratado como uma única partição. Agora é hora de adicionar a cláusula PARTITION BY e ver como isso muda os resultados:

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

Os resultados resumidos são:

Esta consulta retorna uma linha para cada índice, mas agora a consulta especifica uma cláusula PARTITION BY da coluna object_id, então a função de contagem está retornando o número de índices nesse object_id específico. A cláusula ORDER BY controla a ordem em que as linhas são avaliadas pela função. Isso será demonstrado em breve. A cláusula ROWS ou RANGE determina o subconjunto de linhas dentro da partição que deve ser aplicado à função. Ao usar ROWS ou RANGE, você especifica o ponto inicial e final da janela. Os valores permitidos são:

Existem duas sintaxes para especificar a janela:

BETWEEN <beginning frame> AND <ending frame><beginning frame>

Se apenas o quadro inicial for especificado, o quadro final padrão é CURRENT ROW.

A palavra-chave UNBOUNDED especifica o início da partição (para PRECEDING) ou o final de a partição (para SEGUIR). CURRENT ROW especifica que a linha atual é o início da janela ou o final da janela, dependendo da posição da moldura da janela em que ela é usada. “N” especifica um número de linhas antes da linha atual (para PRECEDING ), ou após a linha atual (para FOLLOWING) para usar para a moldura da janela.

A seguir estão as especificações de janela válidas:

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

Para usar a cláusula ROWS ou RANGE, você também deve especificar a cláusula ORDER BY. Por outro lado, se você usar a cláusula ORDER BY e não especificar uma cláusula ROWS ou RANGE, o padrão RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW é usado.

Para demonstrar as cláusulas ORDER BY e ROWS ou RANGE, vamos criar alguns dados de teste: duas contas, quatro datas por conta e um valor para cada data. A consulta mostrará ambos cláusulas sendo usadas de maneiras diferentes:

Esta consulta retorna o seguinte conjunto de resultados:

O ” A coluna RowNbr ”está usando g a função COUNT para retornar quantas linhas há na partição. A partição é ordenada por TranDate, e estamos especificando um frame de janela de todas as linhas do início da partição até a linha atual. Para a primeira linha, há apenas uma linha na moldura da janela, então o valor “1” é retornado. Para a segunda linha, agora há duas linhas na moldura da janela, então o valor “2” é retornado. E assim por diante até o restante das linhas nesta conta.

Visto que a cláusula PARTITION BY especifica a conta, quando a conta muda, os cálculos da função são redefinidos, o que pode ser visto examinando as linhas da segunda conta no conjunto de resultados. Este é um exemplo de agregação “em execução”, em que a agregação se baseia em cálculos anteriores. Um exemplo de quando você usaria isso seria ao calcular o saldo de sua conta bancária após cada transação (também conhecido como total em execução).

A coluna “DateCount” está realizando uma contagem de quantas linhas, particionadas pela data. Neste exemplo, cada uma das contas tem uma transação em cada uma das mesmas quatro datas, portanto, cada data tem duas transações (uma para cada conta). Isso resulta no valor “2” sendo retornado para cada linha. Isso é semelhante a realizar uma contagem que usa GROUP BY para a data; a diferença é que o total é retornado para cada linha em vez de apenas uma vez para cada data. Um exemplo de quando você usaria esse método seria para exibir uma “Linha X de Y” ou calcular uma porcentagem da linha atual para o total.

A coluna “Last2Count” realiza uma contagem das linhas dentro da partição, para a linha atual e a linha imediatamente anterior. Para a primeira linha em cada conta, uma vez que não há linhas anteriores, um valor de “1” é retornado. Para as linhas restantes em cada conta, um valor de “2” é retornado. Este é um exemplo de uma agregação “móvel” ou “móvel”. Um exemplo de quando você usaria esse método seria calcular um bônus com base no vendas dos últimos dois meses.

Neste ponto, mostrei apenas a cláusula ROWS. A cláusula RANGE funciona de maneira semelhante, mas em vez de lidar com as linhas de forma posicional, ela lida com o valores retornados por essa linha. É porque não é posicional que as cláusulas N PRECEDING / FOLLOWING não podem ser usadas. Vamos dar uma olhada rápida na diferença entre ROWS e RANGE usando ambos na mesma consulta. Aqui temos um lista de pessoas (vamos chamá-los de DBAs) e suas taxas por hora. Observe que as linhas com RowIDs 4 & 5 e 12 & 13 têm a mesma taxa. A consulta irá somar as taxas duas vezes, uma usando ROWS e a outra usando RANGE:

Esta consulta produz o seguinte conjunto de resultados:

Em ambas as colunas SumByRows e SumByRange, a cláusula OVER é idêntica, com exceção da cláusula ROWS / RANGE. Observe também que, como o intervalo final não foi especificado, o padrão é usar CURRENT ROW. Uma vez que estamos somando o salário do início do conjunto de resultados até a linha atual, o que estamos realmente calculando é um total parcial da coluna Salário. Na coluna SumByRows, o valor é calculado usando a cláusula ROWS, e podemos ver que a soma da linha atual é o Salário da linha atual mais o total da linha anterior. No entanto, a cláusula RANGE funciona com base no valor da coluna Salário, portanto, ela soma todas as linhas com o mesmo salário ou menor. Isso resulta no valor SumByRange sendo o mesmo valor para todas as linhas com o mesmo Salário.

Uma observação importante: a cláusula ORDER BY na cláusula OVER apenas controla a ordem em que as linhas na partição serão utilizadas pela função de janela. Ele não controla a ordem do conjunto de resultados final. Sem uma cláusula ORDER BY na própria consulta, a ordem das linhas não é garantida. Você pode notar que sua consulta pode estar retornando na ordem da última cláusula OVER especificada – isso se deve à maneira como isso está implementado atualmente no SQL Server. Se a equipe do SQL Server na Microsoft mudar a maneira como funciona, ela não poderá mais ordenar seus resultados da maneira que você está observando atualmente. Se precisar de uma ordem específica para o conjunto de resultados, você deve fornecer uma cláusula ORDER BY contra a própria consulta.

Finalmente, aqui está um gráfico das várias funções que podem usar a cláusula OVER, bem como quais partes da cláusula são permitidas / obrigatórias / opcionais.

R-Obrigatório, O-Opcional, X-Não permitido