Cláusula OVER - Parte 1/2
A cláusula OVER determina o particionamento e a ordenação de um conjunto de linhas antes que a função associada seja aplicada. Ou seja, a cláusula OVER define um conjunto de linhas especificadas pelo usuário dentro do conjunto de resultados da consulta. Uma função de conjunto, em seguida, 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 em execução ou um N superior por resultados de grupo.
Os scripts de exemplos utilizam o BD AdventureWorks2014.
Observe este script de numeração de linhas:
As colunas FullName e LastName são simples, apenas retornam o conteúdo das colunas da tabela.
Na coluna Row Number 1, a função ROW_NUMBER() vai numerar todas as linhas da tabela, sobre (OVER) a ordenação da coluna LastName, em ordem ascendente.
Na coluna Row Number 2, a função ROW_NUMBER() vai numerar todas as linhas da tabela, sobre (OVER) a ordenação da coluna LastName, em ordem ascendente, agrupando (PARTITION BY) pelo valor de LastName.
Seguindo o resultado na coluna, temos 1 linha com LastName Abbas, 1 linha com LastName Abel... E 1,2,3 linhas com LastName Abercrombie. Ou seja, sempre que o sobrenome muda, a coluna recomeça a numeração.
Agora, vamos fazer uma pequena alteração na query...
Agora, a coluna Row Number 1 está numerada em ordem decrescente... Ou quase! Para os sobrenomes Abbas e Abel, que são os dois primeiros da lista, a numeração do registro equivale aos dois últimos valores da contagem. Mas, para o sobrenome Abercrombie, a numeração está correta, mas é exibida em ordem ascendente.
E se trocarmos a coluna do ORDER BY?
A ordenação definida pela cláusula ORDER BY não afeta a cláusula OVER, elas são independentes. A ordenação dentro da cláusula OVER afeta, exclusivamente, a função utilizada.
Nesta query, vamos combinar algumas funções de agregação com OVER:
SalesOrderId: número do pedido
ProductId: código do pedido
OrderQty: quantidade de itens do produto no pedido.
Total: é a soma das quantidades de itens do produto para o pedido completo.
AVG: é a média de itens do produto para o pedido completo (2/2, 7/6 e 6/4).
COUNT: quantidade de produtos no pedido.
MIN: quantidade mínima de itens de produtos no pedido.
MAX: quantidade máxima de itens de produtos no pedido.
Veja o uso da cláusula OVER com uma função de agregação em um valor calculado:
SalesOrderId: número do pedido
ProductId: código do pedido
OrderQty: quantidade de itens do produto no pedido.
Total: é a soma das quantidades de itens do produto para o pedido completo.
Percent by ProductId: percentual que a quantidade de OrderQty representa do total dentro do pedido.
Podemos usar 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 na tabela Sales.SalesPerson. Os dados são particionados por TerritoryID e ordenados logicamente por SalesYTD. Isso significa que a função AVG é computada para cada território com base no ano de vendas.
Observe que para TerritoryID 1 e 4, há duas linhas para o ano de vendas 2011, que representam os dois vendedores com vendas nesse ano. As vendas médias para essas duas linhas são computadas e a terceira linha que representa vendas do ano 2012 é incluída na totalização.
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 na qual as linhas da consulta são exibidas.
Rows e Range, Preceding e Following
Estas cláusulas, em conjunto com OVER, permitem uma variedade de consultas e cálculos utilizando conjuntos de linhas.
ROWS PRECEDING, FOLLOWING, UNBOUNDED: se refere à linha corrente e todas aquelas anteriores ou posteriores, de acordo com PRECEDING ou FOLLOWING.
RANGE PRECEDING, FOLLOWING, UNBOUNDED: significa que todos os valores no range corrente e aqueles antes ou depois.
Um exemplo de uma necessidade de linhas precedentes seria calcular uma média de três anos, que precisa olhar para o ano em curso e os três anos anteriores no cálculo.
Esta é a terminologia destas cláusulas:
ROWS: comando afetará linhas.
RANGE: comando afetará conjunto de linhas.
PRECEDING: comando afetará as linhas anteriores à linha atual.
FOLLOWING: comando afetará as linhas posteriores à linha atual.
UNBOUNDED: quando usado com PRECEDING ou FOLLOWING, afetará tudo antes ou depois da linha atual.
CURRENT ROW: comando afetará apenas a linha atual.
Para entender melhor estas cláusulas, vamos criar uma nova tabela:
Este script criou a tabela Receita e incluiu 45 linhas.
E relembrando a sintaxe básica de OVER:
Vamos acrescentar ROWS PRECEDING, que especifica que a função de agregação na partição corrente na cláusula OVER deve considerar a linha corrente e uma quantidade específica de linhas antes da linha corrente. Observe que PRECEDING vem antes de CURRENT ROW. Quando o código do departamento mudar, a coluna calculada recomeça a somatória.
Agora a mesma query, só que usando ROWS FOLLOWING, que especifica que a função de agregação na partição corrente na cláusula OVER deve considerar a linha corrente e uma quantidade específica de linhas após a linha corrente. Observe que, agora, a cláusula FOLLOWING vem depois de CURRENT ROW. Quando o código do departamento mudar, a coluna calculada recomeça a somatória.
Ok. Desta vez vamos utilizar ambos – PRECEDING e FOLLOWING. Esta combinação nos permite fazer coisas como calcular uma média incluindo o ano corrente e os anos anteriores e posteriores ao ano corrente. Quando o código do departamento mudar, a coluna calculada recomeça a somatória.
Vale destacar que a quantidade de linhas para PRECEDING e FOLLOWING não precisam ser iguais.
A cláusula UNBOUNDED PRECEDING diz à função de agregação para usar o valor corrente, e todos os valores na partição antes do valor corrente. Quando o código do departamento mudar, a coluna calculada recomeça a somatória.
A cláusula UNBOUNDED FOLLOWING diz à função de agregação para usar o valor corrente, e todos os valores na partição posteriores ao valor corrente. Quando o código do departamento mudar, a coluna calculada recomeça a somatória.
Rows vs Range
ROWS e RANGE costumam causar alguma confusão. ROWS significa a linha ou linhas especificadas, e RANGE se refere àquelas linhas mais qualquer outra que tenha os mesmos valores das colunas declaradas no OVER. Com a massa de dados atual, ROWS e RANGE retornam o mesmo resultado.
Para diferenciar ROWS e RANGE, vamos incluir um conjunto duplicado de valores para 2005. Os resultados abaixo mostram que para o ano de 2005 existem duas linhas no conjunto de resultados e a coluna RowsCumulative é diferente do RangeCumulative. Os valores da coluna RowsCumulative são totalizados linha a linha. E os valores de RangeCumulative são totalizados para todas as linhas com o mesmo CodDepto.
As diferentes combinações de OVER PARTITION podem ajudar muito no desenvolvimento de relatórios. Tente novas combinações e outras funções cumulativas. E depois me conte como foi...