Recurso para performance de análise de dados.

Quando utilizamos uma window function (w.f) definimos uma coluna  dentro da cláusula OVER e esta coluna juntamente com a cláusula partition by cria um grupo que retorna para cada linha um resultado agrupado para aquele registro.

COMO FUNCIONA A WINDOW FUNCTION?

Suponhamos que uma consulta simples é realizada no banco AdventureWorks para retornar o ranking com as vendas do último ano corrente de acordo com cada região. Temos o seguinte resultado:

ENTENDENDO A CLÁUSULA OVER

Junto da cláusula OVER podemos usar funções de agregação como SUM, AVG, COUNT e etc tendo também três argumentos que podem participar desta cláusula:

  • Partition By
  • Order by
  • ROW

Como a W.F atua linha por linha daquele subconjunto que foi gerado pela query que possui a cláusula over, o resultado é mais detalhado.

Em alguns casos, quando utilizamos função de agregação ou analíticas, não é possível ordenar dentro do over.

WINDOW FUNCTIONS COM FUNÇÕES DE AGREGAÇÃO

Utilizando as funções de agregação, conseguimos o maior nível de detalhamento e flexibilidade, podendo usar um grupo de linhas para que a função de agregação atue. 

Temos a facilidade por exemplo de criar uma query que retorne uma coluna com o total absoluto, total por cliente, total por ano e etc. A gama de recursos que podemos utilizar com as W.F é extensa e tudo depende basicamente da cláusula OVER.

Veja este exemplo e como podemos criar um simples relatório.

A grande vantagem de utilizar W.F é para solucionar problemas que tínhamos com subqueries, quando havia necessidade de realizar queries mais analíticas.

Outro ponto interessante ao utilizar window functions é no que tange a sua ocorrência. Por ser processado após o from, where, group by ou having, ele só pode aparecer no select ou no order by. Eu prefiro utilizar no select.

Veja mais um exemplo da versatilidade e usabilidade das window function gerando uma análise de vendas gerais de  todos os vendedores. Note que como são vendas totais, não há a cláusula partition by. 

Uma dica: se utilizar uma CTE, a quantidade de análises possíveis se expande ainda mais.

Quando adicionamos o order by dentro de uma window function não estamos definindo apenas a ordem de exibição, lembrando que para garantir o ordenamento desejado, deve-se explicitar o order by ao final da query.

Mas utilizando o order by neste ponto, conseguimos trabalhar com uma opção chamada ‘frame’ que permite uma análise mais apurada, embora o escopo de ação passe a ser mais limitado.

Temos duas cláusulas quando especificamos o ‘frame’ de ação: rows e range.

T-SQL não suporta range.

Veja a query abaixo utilizando um exemplo simples de frame dentro da função de janela. Perceba que ela aplicada após o order by, como dito acima.

Quando utilizamos nesta query aplicando o frame por productid com a função de agregação de SUM, o que estamos dizendo para essa query é pra ela somar a linha atual com a próxima obtendo um ‘valor até o momento’. No exemplo abaixo, vamos ver o uso do frame rows.

Análise de running total value para produtos pedidos sendo o produto com id 787:

No próximo exemplo, como nosso order by é baseado na data todo o cálculo é feito sobre a perspectiva temporal e não simplesmente pelo id do vendedor. Este só está ali para critério de desempate.

Eu coloco a coluna subtotal para ser exibida no resultado apenas com o intuito de facilitar o entendimento da operação da query e da sua soma entre os valores.

Outra forma de determinar como o frame da w.f irá funcionar é utilizando o following na query com a seguinte sintaxe: ‘rows between current row and unbounded following‘.

Utilizando este modo de operação, nossa query passa a agir de forma inversa ao que vimos quando utilizamos ‘rows between unbounded preceding‘.

Veja esta query abaixo, utilizando ‘unbounded following‘ comparando com o preceding:

Veja nos dois destaques o seguinte:

Na coluna cust_preceding a query da linha 2 irá somar com a linha 1 e o resultado será exibido na coluna cust_preceding e este resultado da linha dois, irá somar com a linha três finalizando a soma dos pedidos para o cliente 11000.

já na coluna custfollowing o que temos é uma inversão e soma dos valores. Se perceberem o que ocorre, o resultado final da coluna cust_preceding 9115.13 virou o primeiro valor da coluna custfollowing. Isso ocorre porque quando utilizamos o frame declarando ‘unbounded following‘ ele pega o último valor da partição, joga para a primeira linha e vem somando os registros de baixo para cima.

Por ser um pouco confuso, o unbounded following não é tão utilizado. Tente dar preferência ao ‘rows unbounded preceding‘ é mais trivial.

OFFSET COMO OPÇÃO DE FRAME.

Dentro destas funções de janela e na utilização do frame, podemos especificar uma certa quantidade de linhas ao qual a função irá agir.

Dependendo do tipo de frame que utilizarmos definimos quantas linhas anteriores a atual e quantas linhas posteriores a atual uma determinada ação será executada.

Neste simples exemplo limitamos a operação apenas para uma linha anterior a atual e como na opção de framing estipulamos apenas ‘rows between 1 preceding and 1 preceding’, não houve soma, já que a linha atual está unicamente limitada a sua predecessora.

Agora, para realizar uma operação de soma limitando apenas a linha anterior, devemos declarar o framing da seguinte forma.

Veja que a linha dois somou com a linha um e a linha três somou com a dois, mas perceba que a soma não foi realizada com o resultado e sim, com o valor fixo da linha anterior.

Assim conseguimos limitar a ação de soma entre os resultados.

Outro exemplo de limitação de framing dentro das W.F se aplica somando a linha atual e a anterior, obtendo um resultado único utilizando ‘rows between 1 preceding and 1 following’.

Nesta query, a linha um como não possui uma anterior, soma apenas com a sua próxima linha tendo o resultado de 6344.86. A mesma lógica se aplica para a última linha

Já a linha dois faz exatamente o processo completo, somando com a sua anterior e seguinte, tendo o maior resultado.

Um fator importante a se destacar é que esta ação sofre uma limitação maior devido a query ser particionada pela coluna customerid o que limita a soma à apenas os clientes com id exatamente iguais. 

Para efeito de demonstração, veja esta query com a mesma propriedade da anterior excluindo apenas o particionamento:

Agora podemos afirmar que as linhas estão somando com a anterior e próxima sem restrição de particionamento.

Este post teve o intuito de trazer luz para um recurso que permite grandes análises de dados e uma variedade no que tange a manipulação de resultados

Também mostrei como podemos limitar a ação de uma query com off set e como essa limitação afeta o resultado das funções de agregação

Link para o script do capítulo!

Espero que gostem, saúde!

‘