Use a cláusula OVER
Você já aprendeu que as funções de janela exigem a cláusula OVER para criar e manipular janelas. A cláusula OVER define as linhas às quais a função de janela é aplicada. Pode ser todas as linhas ou um subconjunto das linhas. Ele também pode definir a ordem das linhas para uma função de janela.
Você pode usar a cláusula OVER com funções para calcular valores agregados, como médias móveis, agregados cumulativos, totais em execução ou um N superior por resultados de grupo.
A cláusula OVER pode ter os seguintes argumentos:
- PARTITION BY – divide o conjunto de resultados da consulta em diferentes partes.
- ORDER BY - define a ordem lógica das linhas do conjunto de resultados.
- ROWS/RANGE - limita as linhas especificando os pontos de início e fim. Isso requer o argumento ORDER BY e o valor padrão é desde o início da partição até o elemento atual.
Se você não especificar um argumento para a cláusula OVER, as funções de janela serão aplicadas em todo o conjunto de resultados.
O diagrama a seguir mostra a relação entre SELECT, OVER e PARTITION BY:
PARTIÇÃO POR
A cláusula PARTITION BY divide o conjunto de resultados em partições antes de aplicar a função window. Se PARTITION BY não for especificado, a função de janela será aplicada a todas as linhas da consulta. As partições usam uma das colunas disponibilizadas na cláusula FROM.
ORDER BY
ORDER BY define a ordem lógica das linhas dentro de cada partição. Por exemplo, a função RANK requer que as linhas sejam ordenadas para que possa retornar a posição de classificação de cada linha. A ordem padrão é ASC, mas é uma prática recomendada especificar ASC ou DESC após a ordem por expressões. NULL é tratado como o menor valor possível.
Cláusulas ROWS ou RANGE
Os argumentos ROW ou RANGE definem um limite de início e fim em torno das linhas que estão sendo operadas. ROW ou RANGE requer uma subcláusula ORDER BY dentro da cláusula OVER.
A cláusula ROWS limita as linhas dentro de uma partição especificando um número fixo de linhas anteriores ou posteriores à linha atual.
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.
LINHA ATUAL
Especifica que a janela começa ou termina na linha atual quando usada com ROWS ou o valor atual quando usado com RANGE. CURRENT ROW pode ser especificado como um ponto inicial e final.
ENTRE E
Usado com ROWS ou RANGE para especificar os pontos de limite inicial e final da janela.