Brincando com funções de janelas (Window Functions)

<pre lang=”sql”>Tempos atrás me deparei com um problema que era criar uma função para gerar uma CURVA ABC. Eu não tinha a menor idéia do que ser tratava, mas resumindo, uma Curva ABC é uma separação entre faixas de valores, como os 20% mais ricos e o 20% mais pobres. Se quiser mais detalhes, veja a definição do Wikipédia. Apesar do conceito ser simples, fazer isso com SQL convencional não é. E quem me conhece, sabe que eu só utilizo PL no banco de dados, quando é absolutamente necessário. O motivo é simples, o PL não deixa muita margem para o otimizador do banco de dados encontrar a melhor forma de agrupar os dados. E eu sei que o otimizador é muito mais inteligente que eu.

Bom, resumindo a história, eu precisei lançar mão das novas funções de janela, incorporadas na versão 8.4 do PostgreSQL. Para exemplificar aqui, vou utilizar uma base de exemplo conhecida de todos, o PAGILA. Eu tentei o DELLSTORE, mas os dados não apareceram tão bem distribuídos para efeito de visualização.

Bom, depois de criar uma base e importar os dados do PAGILA, eu criei uma visão para facilitar a minha vida e simplificar a análise, deixando de lado detalhes desnecessários:

Com um select você verá os dados mais ou menos assim:

O primeiro passo para calcular a curva ABC sobre as vendas de cada filme, para cada categoria de filme (os filmes mais vendidos e os menos vendidos em cada categoria) é calcular o total de vendas. Uma vez que queremos os 20% mais vendidos e os 20% menos vendidos, precisamos do total (100%) para calcular a porcentagem:

E assim teremos:

Agora vejamos uma função de janela em ação aqui. Ainda não é a que vamos utilizar, é apenas um exemplo:

Agora vejamos o resultado:

Agora vamos olhar com calma os dados (tirei aqui apenas as 20 primeiras linhas) e a parte do SELECT que diz:

Veja, o campo está utilizando a função RANK, sobre uma janela de dados ordenados pelo total de vendas. O campo RANK simplesmente diz qual é mais significativo, sobre a amostra (OVER) determinada. Agora queremos saber quais são os mais vendidos por categoria. O curioso é que ao invés de utilizar o GROUP BY que afetaria todas as colunas, vamos utilizar o PARTITION BY dentro da definição da janela, assim, isto afetará apenas a nossa última coluna:

Assim teremos:

Notem que agora o rank é realizado para cada categoria, o rank é ordenado pelas vendas. Vale a pena executar o SQL no seu computador para verificar os dados com mais calma.

Bom, mas eu quero saber dos 20% mais vendidos. O problema aqui é que você precisaria ir somando o total de vendas de cada produto até chegar em 20% do total. Ocorre que você pode utilizar as funções de agregação como SUM, COUNT e outras junto com a sua janela. Vejamos como isso funciona:

E teremos os seguintes dados:

Note que o valor da soma (SUM) vai acumulando registro a registro até que uma nova categoria apareça. Isto é uma coisa muito difícil de se fazer com SQL puro, você não consegue fazer cálculos com base nos registros anteriores.

Agora nós podemos juntar esta última consulta com a primeira para calcular a porcentagem de vendas sobre o total da categoria. Vamos colocar cada uma das consultas como uma entrada em FROM:

O resultado será:

O resultado é a porcentagem do total de vendas sendo acumulado até 100% e depois começando novamente para outra categoria. Com isso fica fácil atribuir A para os 20% mais significativos, B para os próximos 60% e C para os 20% menos significativos. Aqui, irei utilizar uma função CASE, que embora ocupe um bom espaço no nosso SQL é algo bastante trivial:

E finalmente temos a nossa curva ABC:

É claro que existem N coisas que você pode fazer com funções de janela. A documentação possui algumas explicações sobre este recurso e também uma lista com várias funções que você pode testar com a sua base, aproveitando como ponto de partida os exemplos mostrados aqui.

Boa diversão!

4 comentários sobre “Brincando com funções de janelas (Window Functions)

  1. Apenas uma dica.

    CASE
    WHEN (somatorio / total) 0.2 AND (somatorio / total) <= 0.8 THEN 'B'
    ELSE 'C' END AS curva_abc

    O case também pode ser escrito assim:

    CASE
    WHEN (somatorio / total) <= 0.2 THEN 'A'
    WHEN (somatorio / total) <= 0.8 THEN 'B'
    ELSE 'C' END AS curva_abc

  2. O total da categoria também pode ser calculado com Window Function:

    SELECT
    sales.category,
    title,
    somatorio,
    total,
    (somatorio / total) * 100 AS porcentagem
    FROM
    (SELECT
    title,
    category,
    sum(total_sales) OVER (PARTITION BY category ORDER BY total_sales DESC) AS somatorio,
    sum(total_sales) OVER (PARTITION BY category) AS tot
    FROM sales_pgday) sales

  3. Telles, tudo bem?

    As Window function funcionam, mas têm um efeito colateral.
    Um filme muito vendido que ultrapasse a faixa estabelecida,
    seria erroneamente classificado. Por exemplo:
    Se o primeiro livro mais vendido atinge sozinho 40% em sua categoria,
    ele seria erroneamente classificado como ‘B’. Ou estou enganado?
    De qualquer forma, a extração parcial pelo acumulado dos percentuais
    permite submeter os registros a uma function que drible esse problema.

    Abraços.

    Maurício

Dúvidas, sugestões, críticas, comentários e cervejas são bem vindos!!!