No primeiro post da revisão sobre as Window Functions, abordei os conceitos básicos, como ela é criada pela linguagem SQL e seu processamento lógico pelo engine do banco de dados.

Aprofundando sobre o assunto e continuando o anterior, neste, quero tratar sobre como as funções analíticas se comportam e como podemos extrair melhores análises e modelos com estas ferramentas.

ÍNDICE:

  1. WINDOW AGGREGATE FUNCTIONS
  2. ORDEM E FRAMING EM WINDOW FUNCTIONS
  3. FUNÇÕES DE RANK
  4. FUNÇÕES ESTATÍSTICAS
  5. OFF-SET FUNCTIONS
  6. CONCLUSÃO
  7. OLA!
  8. SIGA NAS REDE SOCIAIS:
    1. OBRIGADO!

WINDOW AGGREGATE FUNCTIONS

Em alguns exemplos do artigo anterior, vimos que é possível utilizar as funções de agregação dentro de uma WF.

A forma como o cálculo é organizado, isso é, como o banco vai processar, vai depender exclusivamente da nossa construção da consulta. Aliado às opções da WF, podemos criar queries interessantes.

Vejamos a query abaixo:

Exemplo de aplicação das funções de agregação com window function.
Exemplo de aplicação das funções de agregação com window function.

Percebam que criei duas colunas com as WF, sendo a primeira chamada TOTAL_SALES e a TOTAL_CUST. 

Na TOTAL_SALES, como não foi especificado nenhuma partição, o que o banco fez foi trazer o total das vendas de todos os produtos. Na segunda coluna, TOTAL_CUST, temos o total por cada cliente; pois há particionamento.

Na primeira coluna, seria algo similar a esse cálculo, porém, sem aplicar para todas as linhas. Apenas o valor linear.

Recriando o resultado da WF com SUM.
Recriando o resultado da WF com SUM.

Para a TOTAL_CUST, o problema começa a aparecer. A medida que vamos adicionando colunas na query e, por consequência, no GROUP BY, o cálculo vai se desmantelando.

No primeiro exemplo, veja a construção:

Especificando o problema das funções de agregação sem WF.
Especificando o problema das funções de agregação sem WF.

Adicionando a coluna ORDERDATE.

Adição da ORDERDATE e alteração do resultado da query.
Adição da ORDERDATE e alteração do resultado da query.

Além dos recursos analíticos que a WINDOW FUNCTION resolve, o que vemos, é que ela também facilita a criação da query.

Outra estrutura presente é o framing, que dita como a operação irá ocorrer.

Adicionando um framing na WF, reorganizando o cálculo da query.
Adicionando um framing na WF, reorganizando o cálculo da query.

Aqui, acontecem as seguintes situações:

  1. O banco soma  a linha anterior com a atual;
  2. Computa o resultado na linha atual;
  3. Soma com o valor da próxima linha.

Comparando com o resultado do primeiro exemplo, podemos perceber que é o mesmo.

Podemos comparar os elementos de framing como um filtro interno no particionamento de uma query. Porém, diferente do filtro convencional, temos um modo de operação da função de agregação.

Outro ponto que devo chamar atenção é que, embora similares, o GROUP BY e o Partitioning da WF são coisas diferentes. Sendo que o segundo, cria pequenos blocos com valores iguais.

A medida que vamos adicionando colunas na query e, por consequência, no GROUP BY, o cálculo vai se desmantelando… Além dos recursos analíticos que a WINDOW FUNCTION resolve, o que vemos, é que ela também facilita a criação da query.

Uma grande vantagem em utilizar as WF com função de agregação é no cálculo de porcentagem. Se quiséssemos utilizar apenas as funções de agregação, teríamos que utilizar no mínimo, duas CTEs e o código ficaria um tanto embolado. 

Porém, veja como as window function facilitam o trabalho.

  • Vermelho → eu tenho o cálculo do quanto aquela compra do cliente representa no faturamento total;
  • Preto → o quanto a compra do cliente representa no faturamento daquele ano.
Cálculo de porcentagem simplificado com o uso de Window Function.
Cálculo de porcentagem simplificado com o uso de Window Function.

Como meu particionamento para a segunda coluna foi por ano, o SQL Server computa o total no range e divide pelo total geral, chegando ao resultado.

Um outro exemplo calculando a porcentagem por território. Veja como temos um código mais limpo e simples.

Repare que no exemplo da esquerda, há quatro subqueries sendo executadas no código. Em bancos muito grandes, isso pode trazer lentidão.

Refazendo a query com estrutura de subqueries.
Refazendo a query com estrutura de subqueries.

Note que a quantidade de subquery na segunda consulta. Essa é a melhor maneira de explicitar a vantagem de utilizar Window Functions em queries analíticas.

Um fato interessante é que se filtrarmos com o WHERE,  a query passa a considerar esse valor como a totalidade. Veja.

Exibindo a restrição do WHERE com WF.
Exibindo a restrição do WHERE com WF.

Como a WF logicamente ‘acontece’ entre o SELECT e o ORDER BY,  o WHERE limita os dados em que ela irá trabalhar. Uma vez que a query entrega apenas um único território, ele é a totalidade da ação da WF.

Aumentando a quantidade de território. 

Aumentando a ação com o WHERE.
Aumentando a ação com o WHERE.

Como podemos notar, a soma da porcentagem dos territórios retornados devem dar 100%.

ORDEM E FRAMING EM WINDOW FUNCTIONS

Sempre que queremos ditar como será a ação da query sobre o particionamento criado, precisamos ordenar e especificar o subconjunto que a função irá trabalhar, como se fosse o OFFSET – FETCH.

O framing dentro da WF tem duas funções:

  • Limitar a quantidade de linhas;
  • Orientar a ação da função de agregação.

Quando explicitamos o frame na query, ele atribui uma orientação para o cálculo que iremos performar.

Como a WF logicamente ‘acontece’ entre o SELECT e o ORDER BY,  o WHERE limita os dados em que ela irá trabalhar. Uma vez que a query entrega apenas um único território, ele é a totalidade da ação da WF.

Na coluna RUNSELL, temos o que alguns chamam de running total, das vendas de acordo com determinado vendedor. Isso só é possível graças ao framing atribuído na query.

Cálculo do total acumulado com window function e aplicação de framing na construção.
Cálculo do total acumulado com window function e aplicação de framing na construção.

Antes de continuar, preciso chamar atenção para uma situação.

Sempre que houver mais de um framing na query com WF e estes possuírem ORDER BY divergentes, o último diratá o ordenamento do resultado.

Alterando o ORDER BY dentro do framing, divergindo entre as duas colunas, veja o resultado.

Exemplificando a diferença do ORDER BY nos framings.
Exemplificando a diferença do ORDER BY nos framings.

O que passou a ditar a exibição do resultado foi a segunda coluna. Além disso, percebam que a declaração do frame é igual.

Os frames dentro de uma WF são criados através de duas cláusulas no comando.

  • UNBOUNDED PRECEDING
  • UNBOUNDED FOLLOWING

O que as duas expressões fazem é especificar, a partir da linha atual, como o cálculo ocorrerá. 

Na primeira coluna, RUNTOTAL, já conhecemos o frame e como ele opera. Sempre somando a linha atual com a próxima e acumulando. Daí  o resultado acumulado.

Porém, quando tratamos do UNBOUNDED FOLLOWING a estrutura muda. Para criarmos esse framing, precisamos dizer ao SQL Server quantas linhas ele deve processar na operação.

Manipulação do framing dentro da WF UNBOUNDED FOLLOWING.
Manipulação do framing dentro da WF UNBOUNDED FOLLOWING.

Na coluna FL_TOTAL ela processa a linha 1 somando com a linha 2, obtendo o resultado de $5741,96 para a linha 1. O mesmo acontece para a linha 2 e 3, sendo que a última linha do frame não irá somar pois a janela do cliente 11000 fechou.

Já para a coluna CUST_TOTAL há a soma da linha 1 com as linhas 2 e 3, obtendo o resultado $8248.99, e o mesmo vale para a linha 2 e 3. Sendo que a linha 2 irá somar apenas com a linha 3, já que não existe outra para esse frame.

Essa operação só foi possível pois para cada uma delas eu indiquei como o SQL Server deveria processar a operação dentro do frame com as linhas. Note que na FL_TOTAL ele somou com 1 e na CUST_TOTAL com 2.

Caso não seja indicado nenhum número, a soma ocorre normalmente. Como mostra a imagem abaixo.

Execução padrão de framing quando não especificado as linhas.
Execução padrão de framing quando não especificado as linhas.

Uma das grandes vantagens na manipulação de frames é a facilidade em visualizar o anterior e próximo pedido de um determinado cliente em relação a linha atual.

Utilizando framing para visualizar os pedidos anteriores e próximos da linha atual.
Utilizando framing para visualizar os pedidos anteriores e próximos da linha atual.

Em manipulações de frame com partições muito grandes e, como no caso abaixo, com diversas linhas para um mesmo dia, pode ser interessante adicionar um tie-break no código para tornar o resultado o mais determinístico possível.

É uma boa prática criar queries que atendam esse requisito.

Boa prática para criação de window function.
Boa prática para criação de window function.

Outro exemplo de bom uso do framing com CTE é exibir a quantidade de pedidos nos últimos 3 dias ou qualquer outra quantidade de dias.

Como exibir a quantidade de pedidos X períodos anteriroes.
Como exibir a quantidade de pedidos X períodos anteriroes.

As Window functions facilitam a contagem de pedidos por dia, desde que seja criado com o frame correto.

Como exibir a quantidade de pedidos com window function e o framing.
Como exibir a quantidade de pedidos com window function e o framing.

É importante destacar que mesmo a quantidade sendo repetida, perceba que os pedidos são distintos. Não há repetição de valores.

Uma das limitações quando se trata de implementação de window function no SQL Server é na contagem distinta. Como a linguagem T-SQL não possui DISTINCT AGGREGATE, precisamos de uma solução.

Em manipulações de frame com partições muito grandes e, como no caso abaixo, com diversas linhas para um mesmo dia, pode ser interessante adicionar um tie-break no código para tornar o resultado o mais determinístico possível.

Por exemplo, imagine que queremos calcular a quantidade de vendas distintas que um vendedor fez e os clientes que compraram. Para isso, esbarramos novamente nas CTE’s.

Construção da query para exibir pedidos distintos.
Construção da query para exibir pedidos distintos.

E o resultado da query abaixo:

Resultado da query anterior.
Resultado da query anterior.

Perceba que o vendedor de ID 276 realizou duas vendas distintas, como mostra a coluna NUM_CUSTS. Destacando no resultado, temos duas vendas cada uma para um cliente.

Se filtrar a query pela data 31/05/2011, verá que não existe uma terceira venda para outro cliente.

E somando os pedidos de todos os vendedores:

Agregando número de pedidos por vendedor (Salesperson).
Agregando número de pedidos por vendedor (Salesperson).

FUNÇÕES DE RANK

São funções que possuem a característica de criar um valor numérico para as linhas retornadas da consulta e que, dependendo da query, podem ser manipuladas.

Algumas delas já vimos em exemplos anteriores, mas vamos relembrar:

  • RANK
  • DENSE_RANK
  • NTILE
  • ROW_NUMBER

Como já vimos exemplos com a ROW_NUMBER, vamos para a função NTILE.

A função cria ‘divisões’ dentro do conjunto de resultados baseado na quantidade de linhas que a consulta retornará.

É a única função desse grupo que aceita um input como parâmetro. Esse input servirá de divisor para as linhas do resultado.

Sempre que houver mais de um framing na query com WF e estes possuírem ORDER BY divergentes, o último da query irá ditar o ordenamento do resultado.

Então, se uma query retorna 31000 linhas e a quantidade de divisões por 10, teremos grupos de 3100 linhas em cada.

Demonstração da função NTILE no conjunto de dados.

Já as funções de RANK funcionam como uma ROW_NUMBER, mas empatam caso esteja em uma situação igual. Porém, isso não se aplica a DENSE_RANK.

Quando utilizada, ela cria um ranking com base nos valores distintos.

Resultado da função DENSE_RANK.
Resultado da função DENSE_RANK.

Como o ordenamento está por data, o ranking dos dados somente mudarão quando a data mudar. Como ocorreu na coluna DRANK

Uma aplicação bem interessante é selecionar um bucket específico dentro do resultado de uma query com DENSE_RANK. Para isso, utilizamos TVF e CTE.

Como selecionar buckets de dados específicos com TVF e CTE.
Como selecionar buckets de dados específicos com TVF e CTE.

FUNÇÕES ESTATÍSTICAS

As funções estatísticas do SQL Server são do grupo de análise de dados dentro das Window Functions. Elas trabalham com percentuais, atuando de acordo com o conjunto de dados aplicado.

Existem dois subgrupos de função estatísticas no SQL Server:

  1. RANK DISTRIBUTION → com as funções PERCENT_RANK E CUME_DIST
  2. INVERSE DISTRIBUTION → com as funções PERCENTILE_RANK E PERCENTILE_DISC.

Como as funções PERCENT_RANK E CUME_DIST são, em sua base, rank, elas exigem o uso de ORDER BY.

Quando utilizado as funções de RANK DISTRIBUTION, entenda que elas vão criar um ratio que irá de 0 até 1 – podendo ter decimal. Com esse ratio, os dados serão distribuídos de acordo com seu encaixe.

A função PERCENT_RANK irá retornar a posição relativa que cada valor está, dentro do range entre 0 e 1. 

Já a função CUME_DIST calcula uma distribuição acumulada que varia entre 0 e 1, distribuindo os dados de acordo com a sua proximidade do valor mais alto (1).

Vamos entender melhor com a query abaixo:

Construção de análises estatísticas com WF.
Construção de análises estatísticas com WF.

Caso não passe nenhum valor numérico para as funções, ela não distinguirá os dados que pertencem a tal grupo.

Agora vamos entender o resultado da query:

Resultado das funções PERCENT_RANK e CUME_DIST.
Resultado das funções PERCENT_RANK e CUME_DIST.

Na coluna PCT_CUST cada compra feita tem uma posição no percentil estatístico criado pela função. Então, a compra de valor $37.25, por ser de baixo valor, está no percentil inferior dos dados.

Porém, olhando para o final do resultado, as compras dos clientes de ID 29940, 29923 e 30103 estão no percentil mais alto, o que se justifica, se olharmos o valor de suas compras.

Para a coluna CDIST_CUST, embora se pareça com a PCT_CUST, o que ela nos traz para análise são os grupos acumulados dos clientes que estão em suas respectivas distâncias do valor máximo.

Por exemplo, os clientes 29867 e 29576 ocupam posições diferentes em cada função estatística. Como a segunda coluna entrega uma ‘distribuição’, é como se estes clientes estivessem no grupo que se encontra no 0.02 até o 0.04.

Para uma melhor visualização, até por ser mais precisa, utilize três casas decimais. Veja.

Dica de como melhorar a exibição com função estatísticas.
Dica de como melhorar a exibição com função estatísticas.

Podemos perceber que os resultados são diferentes e entregam maior capacidade de análise.

O interessante dessas funções estatísticas é a criação de queries que permitem classificar os clientes de acordo com seu ‘perfil de compra’.

Classificação de clientes com funções estatísticas.
Classificação de clientes com funções estatísticas.

Agora que vimos os exemplos e entendemos como as funções de distribuição por rank funcionam, avançamos para as funções de distribuição inversa.

As funções desse grupo aceitam um valor como input e desse valor, dentro do conjunto de dados, retornam aqueles que estão mais próximos.

Descobrindo valores discretos com funções estatísticas.
Descobrindo valores discretos com funções estatísticas.

Para entendermos o resultado, vamos destrinchar o que a query está fazendo.

Começamos que estas duas funções atuam de forma diferente por utilizarem a cláusula WITHIN GROUP. Isso quer dizer que ‘dentro do grupo RATE (salário)’, ela está calculando o percentil do valor particionado pelo departamento da empresa.

Então, o valor de cálculo da função é a coluna salário, a repartição é a coluna departamento e o valor do percentil 0.8.

Olhando unicamente o resultado da query relembrando o que cada função faz:

A função PERCENTILE_DISC irá calcular, dentro do grupo que ela está sendo aplicado, qual o valor que condiz com a ‘linha de corte’ – valor passado como input para a função.

Já a função PERCENTILE_CONT irá calcular um valor discreto com base no percentile desejado e os valores que estão no conjunto de dados.

Análise do resultado.
Análise do resultado.

Desse modo podemos perceber que para o percentil 0.8 da função PERCENTILE_CONT (coluna CONT_RATE) o salário que corresponde a esse parâmetro, dentro do departamento ‘Document Control’, seria de $17.019.

Esse é um valor ‘escondido’, não percebido, dentro da faixa salarial analisada neste grupo. Como o valor do percentil foi de 0.8, este se encontra entre 16.82 e 17.85, uma vez que ele não pode ultrapasar o teto – valor máximo.

Indo para a coluna DISC_RATE, o resultado é um pouco diferente. Para ela, o que temos é o 16.82, que significa o salário que se encontra no percentil 0.8 do grupo analisado.

Lembrando que os valores são de acordo com cada departamento, especificado no PARTITION BY.

OFF-SET FUNCTIONS

Por fim, mas não menos importante, estão as funções de manipulação de conjunto, as OFF-SET.

Essas funções criam um subconjunto com base no resultado primário da tabela.

Neste grupo de funções conseguimos retornar o primeiro e último valor do conjunto, bem como seu antecessor e sucessor.

Neste grupo temos: LAG, LEAD, FIRST_VALUE E LAST_VALUE

Windows function com funções de OFF-SET.
Windows function com funções de OFF-SET.

CONCLUSÃO

Neste novo artigo sobre Window Function resolvi revisar todas as funções que estão implementadas no SQL Server, que é o banco dos meus estudos.

Mostrei com exemplos práticos os usos que podemos fazer delas e como facilitam a criação de queries, deixando-as mais simples e legíveis.

As funções de janelamento funcionam muito bem com CTEs e TVFs, faça bastante uso para entregar ótimos resultados e análises.

Domine bem as funções estatísticas.

Até o próximo.

OLA!

Se chegou até aqui e gostou do pequeno tutorial, deixe seu like compartilhe. Ajude o blog a alcançar mais pessoas!

Baixe o script aqui:

Caso queira receber em primeira mão esse tipo de conteúdo, se inscreva abaixo:

SIGA NAS REDE SOCIAIS:

OBRIGADO!