Cláusula Over - Parte 2/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.
No primeiro documento sobre OVER, detalhamos a função ROW_NUMBER() e a aplicação de diversas funções já conhecidas do SQL Server, como SUM, AVG e COUNT. Neste documento, vamos analisar outras funções muito interessantes de trabalhar em conjunto com OVER:
A cláusula OVER normalmente é utilizada com dois argumentos:
PARTITION BY: que divide o resultado da query em partes.
ORDER BY: define a ordenação das linhas para aplicar a função associada à OVER. Este ORDER BY não afeta a ordem de exibição dos resultados da query.
Para esses exemplos, vamos utilizar as tabelas do banco de dados AdventureWorks2014
LEAD e LAG
As funções LEAD e LAG trazem o valor de outros registros para a linha do registro atual, e podem ser usados com qualquer tipo de coluna.
LEAD: traz o valor do próximo registro da coluna.
LAG: traz o valor do registro anterior da coluna.
A sintaxe dos comandos é bem simples:
LEAD(Coluna, qtde registros a pular, valor retornado quando não encontra o registro anterior)
LAG(Coluna, qtde registros a pular, valor retornado quando não encontra o próximo registro)
A coluna “Previous Quota” traz o conteúdo da coluna Current Quota do registro anterior ao atual. Para o primeiro registro, a função retorna ZERO, porque não há registro antes do primeiro.
A coluna “Next Quota” traz o conteúdo da coluna Current Quota do registro posterior ao atual. Para o último registro, a função retorna ZERO, porque não há registro depois do último.
Veja como fica o resultado, quando definimos que 3 registros devem ser pulados:
Utilizando PARTITION BY, a busca pelo conteúdo do registro anterior ou posterior da coluna é dentro do ano – YEAR(DtExemplo).
Vale ressaltar que LEAD e LAG não precisam ser utilizados juntos na query, e que o número de linhas puladas também não precisa ser igual nas duas funções.
NTILE
A função NTILE() agrupa o resultado da query em um número específico de partes ordenadas. Para cada linha no resultado, NTILE vai retornar um número, agrupando a linha. Esta função é muito útil na distribuição de resultados em diversos grupos.
Neste exemplo, a query retorna 10 linhas, e NTILE foi definido para criar 3 grupos, organizando o resultado pela coluna Product. Para facilitar o entendimento da função, o resultado também está organizado pela coluna Product, por isso NTILE está em ordem ascendente. Mas, e se trocarmos o ORDER BY?
Agora, o conteúdo de NTILE não está mais ordenado. Mas, se compararmos o resultado das duas queries, veremos que o valor de NTILE continua equivalente ao conteúdo de Product, independente da ordem de apresentação do resultado. Ou seja, a cláusula ORDER BY de OVER não é afetada pela cláusula ORDER BY da query.
Para deixar o agrupamento mais específico, vamos acrescentar a cláusula PARTITION BY. Nesta query, o resultado é agrupado por LOCATION e, dentro de LOCATION, NTILE é distribuído ordenado por PRODUCT. O resultado da query está ordenado por Location e Product, para facilitar a visualização da distribuição de NTILE. Se trocarmos o valor da cláusula ORDER BY do SELECT, a distribuição de NTILE acompanha os registros.
RANK e DENSE_RANK
A função RANK classifica cada linha de um resultado. Ela também pode ser usada para particionar os dados na classificação.
Esta query classifica a quantidade de produtos. Para facilitar, o resultado está ordenado pela coluna Quantity. Veja que a coluna RANK_ASC classificou as quantidades em ordem ascendente. E os registros 9 e 10 possuem a mesma classificação, porque tem o mesmo valor.
Já a coluna Rank_DESC classificou a quantidade em ordem decrescente. Observe que não temos um “segundo lugar”, já que o valor 49 se repete em primeiro.
Ao acrescentar PARTITION BY, a classificação ocorre por localidade, e dentro dela por quantidade. Cada vez que muda a localidade, a classificação recomeça. Lembre-se que os registros estão apresentados nessa ordem para facilitar o entendimento do comando.
Caso você precise de valores consecutivos de classificação, sem “buracos”, use a função DENSE_RANK. Para facilitar a visualização, acrescentamos outra localidade na query:
Os valores nas colunas RANK_ASC e DENSE_RANK_ASC são iguais até a 9ª posição, onde os dois produtos possuem a mesma quantidade: 49. No 11ª registro encontramos a diferença entre elas. A função RANK classificou o registro como 11, já que temos 2 registros como 9. A função DENSE_RANK classificou como 10, mantendo a sequência da classificação.
FIRST_VALUE e LAST_VALUE
As funções analíticas FIRST_VALUE e LAST_VALUE retornam o mesmo valor para a primeira e a última linha de um conjunto de registros.
FIRST_VALUE retorna a primeira linha em um conjunto ordenado de registros.
LAST_VALUE retorna a última linha em um conjunto ordenado de registros.
Vamos começar com a sintaxe mais simples:
A query retorna a lista de Pedidos de Vendas, seus itens e o valor total do item. A coluna Primeiro Item retorna o primeiro valor dos itens dentro do resultado: 5.70. E todas as linhas retornam o mesmo valor para o primeiro item. Agora, o Último item é diferente. A coluna retorna o valor do item de cada linha, ou seja, o valor até aquele registro do resultado.
Agora, vamos acrescentar PARTITION BY, e selecionar os valores dentro do pedido:
Ainda podemos melhorar um pouco mais, e trazer o primeiro e o último valor em todos os registros:
Mas, seria interessante mesmo trazer o primeiro e o último valor por pedido de venda. Assim:
Analisando o resultado das funções FIRST_VALUE e LAST_VALUE, você pode pensar “mas eu posso usar MIN e MAX e conseguir o mesmo resultado”. Será?
As funções MIN e MAX retornam o menor e o maior valor, de acordo com a cláusula GROUP BY. E se eu preciso exibir mais detalhes, como o número do item do pedido e o valor total do item, essas colunas devem ser incluídas no GROUP BY, e o resultado de MIN e MAX não são o que precisamos.
A cláusula OVER, combinada com essas séries de funções e partições, é uma ferramenta poderosa para a geração de relatórios de forma mais simples e clara.