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

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:

CREATE OR REPLACE VIEW sales_pgday AS
    SELECT
        f.title,
        c.name AS category,
        sum(p.amount) AS total_sales
        FROM
            (((((payment p JOIN rental r ON ((p.rental_id = r.rental_id)))
                JOIN inventory i ON ((r.inventory_id = i.inventory_id)))
                JOIN film f ON ((i.film_id = f.film_id)))
                JOIN film_category fc ON ((f.film_id = fc.film_id)))
                JOIN category c ON ((fc.category_id = c.category_id)))
        GROUP BY c.name, f.title
    ;

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

           title            |  category   | total_sales
-----------------------------+-------------+-------------
 AMADEUS HOLY                | Action      |       33.79
 AMERICAN CIRCUS             | Action      |      167.78
 ANTITRUST TOMATOES          | Action      |       37.90
 BAREFOOT MANCHURIAN         | Action      |       66.82
 BERETS AGENT                | Action      |       78.78
 BRIDE INTRIGUE              | Action      |       21.81
 BULL SHAWSHANK              | Action      |       21.84
 CADDYSHACK JEDI             | Action      |       51.84
 CAMPUS REMEMBER             | Action      |       90.81
 CASUALTIES ENCINO           | Action      |       72.91
 CELEBRITY HORN              | Action      |       32.76
 CLUELESS BUCKET             | Action      |      112.75
 CROW GREASE                 | Action      |       18.88
 DANCES NONE                 | Action      |       31.86
 DARKO DORADO                | Action      |       82.89
 DARN FORRESTER              | Action      |       93.82
 DEVIL DESIRE                | Action      |       83.85
 DRAGON SQUAD                | Action      |       27.89
 DREAM PICKUP                | Action      |       81.78
 DRIFTER COMMANDMENTS        | Action      |      141.76
...

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:

SELECT category, sum(total_sales)
    FROM sales_pgday
    GROUP BY category;

E assim teremos:

  category   |   sum
-------------+---------
 Action      | 4375.85
 Animation   | 4656.30
 Children    | 3655.55
 Classics    | 3639.59
 Comedy      | 4383.58
 Documentary | 4217.52
 Drama       | 4587.39
 Family      | 4226.07
 Foreign     | 4270.67
 Games       | 4281.33
 Horror      | 3722.54
 Music       | 3417.72
 New         | 4361.57
 Sci-Fi      | 4756.98
 Sports      | 5314.21
 Travel      | 3549.64
(16 linhas)

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

SELECT
    title,
    category,
    total_sales,
    rank() OVER (ORDER BY total_sales DESC) AS rank
FROM sales_pgday;

Agora vejamos o resultado:

            title            |  category   | total_sales | rank
-----------------------------+-------------+-------------+------
 TELEGRAPH VOYAGE            | Music       |      231.73 |    1
 WIFE TURN                   | Documentary |      223.69 |    2
 ZORRO ARK                   | Comedy      |      214.69 |    3
 GOODFELLAS SALUTE           | Sci-Fi      |      209.69 |    4
 SATURDAY LAMBS              | Sports      |      204.72 |    5
 TITANS JERK                 | Sci-Fi      |      201.71 |    6
 TORQUE BOUND                | Drama       |      198.72 |    7
 HARRY IDAHO                 | Drama       |      195.70 |    8
 INNOCENT USUAL              | Foreign     |      191.74 |    9
 HUSTLER PARTY               | Comedy      |      190.78 |   10
 PELICAN COMFORTS            | Documentary |      188.74 |   11
 CAT CONEHEADS               | Comedy      |      181.70 |   12
 ENEMY ODDS                  | Music       |      180.71 |   13
 BUCKET BROTHERHOOD          | Travel      |      180.66 |   14
 RANGE MOONWALKER            | Family      |      179.73 |   15
 MASSACRE USUAL              | Games       |      179.70 |   16
 VIDEOTAPE ARSENIC           | Games       |      178.71 |   17
 DOGMA FAMILY                | Animation   |      178.70 |   18
 APACHE DIVINE               | Family      |      178.69 |   19
 VELVET TERMINATOR           | Comedy      |      177.74 |   20
...

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

rank() OVER (ORDER BY total_sales DESC) AS rank

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:

SELECT
    title,
    category,
    total_sales,
    rank() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank
FROM sales_pgday;

Assim teremos:

            title            |  category   | total_sales | rank
-----------------------------+-------------+-------------+------
 FOOL MOCKINGBIRD            | Action      |      175.77 |    1
 AMERICAN CIRCUS             | Action      |      167.78 |    2
 STAGECOACH ARMAGEDDON       | Action      |      154.74 |    3
 EASY GLADIATOR              | Action      |      150.77 |    4
 MINDS TRUMAN                | Action      |      149.80 |    5
 KISSING DOLLS               | Action      |      147.80 |    6
 TRIP NEWTON                 | Action      |      145.72 |    7
 DRIFTER COMMANDMENTS        | Action      |      141.76 |    8
 SUSPECTS QUILLS             | Action      |      133.70 |    9
 WATERFRONT DELIVERANCE      | Action      |      121.83 |   10
...
 DRAGON SQUAD                | Action      |       27.89 |   56
 BULL SHAWSHANK              | Action      |       21.84 |   57
 BRIDE INTRIGUE              | Action      |       21.81 |   58
 CROW GREASE                 | Action      |       18.88 |   59
 LAWRENCE LOVE               | Action      |       15.87 |   60
 MONTEZUMA COMMAND           | Action      |       11.91 |   61
 DOGMA FAMILY                | Animation   |      178.70 |    1
 SUNRISE LEAGUE              | Animation   |      170.76 |    2
 TITANIC BOONDOCK            | Animation   |      154.77 |    3
 FORRESTER COMANCHEROS       | Animation   |      146.73 |    4
 FALCON VOLUME               | Animation   |      127.77 |    5
...

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:

SELECT
    title,
    category,
    total_sales,
    rank() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank,
    sum(total_sales) OVER (PARTITION BY category ORDER BY total_sales DESC) AS sum
FROM sales_pgday;

E teremos os seguintes dados:

            title            |  category   | total_sales | rank |   sum
-----------------------------+-------------+-------------+------+---------
 FOOL MOCKINGBIRD            | Action      |      175.77 |    1 |  175.77
 AMERICAN CIRCUS             | Action      |      167.78 |    2 |  343.55
 STAGECOACH ARMAGEDDON       | Action      |      154.74 |    3 |  498.29
 EASY GLADIATOR              | Action      |      150.77 |    4 |  649.06
 MINDS TRUMAN                | Action      |      149.80 |    5 |  798.86
 KISSING DOLLS               | Action      |      147.80 |    6 |  946.66
 TRIP NEWTON                 | Action      |      145.72 |    7 | 1092.38
 DRIFTER COMMANDMENTS        | Action      |      141.76 |    8 | 1234.14
 SUSPECTS QUILLS             | Action      |      133.70 |    9 | 1367.84
 WATERFRONT DELIVERANCE      | Action      |      121.83 |   10 | 1489.67
 TRUMAN CRAZY                | Action      |      121.77 |   11 | 1611.44
 CLUELESS BUCKET             | Action      |      112.75 |   12 | 1724.19
 SOUTH WAIT                  | Action      |      107.78 |   13 | 1831.97
 DARN FORRESTER              | Action      |       93.82 |   14 | 1925.79
 CAMPUS REMEMBER             | Action      |       90.81 |   15 | 2016.60
 FORREST SONS                | Action      |       87.82 |   16 | 2104.42
 DEVIL DESIRE                | Action      |       83.85 |   17 | 2188.27
 DARKO DORADO                | Action      |       82.89 |   18 | 2271.16
 DREAM PICKUP                | Action      |       81.78 |   19 | 2352.94
 WEREWOLF LOLA               | Action      |       78.86 |   20 | 2431.80
...
 REAR TRADING                | Action      |       32.83 |   52 | 4163.15
 CELEBRITY HORN              | Action      |       32.76 |   53 | 4195.91
 DANCES NONE                 | Action      |       31.86 |   54 | 4227.77
 SIDE ARK                    | Action      |       29.88 |   55 | 4257.65
 DRAGON SQUAD                | Action      |       27.89 |   56 | 4285.54
 BULL SHAWSHANK              | Action      |       21.84 |   57 | 4307.38
 BRIDE INTRIGUE              | Action      |       21.81 |   58 | 4329.19
 CROW GREASE                 | Action      |       18.88 |   59 | 4348.07
 LAWRENCE LOVE               | Action      |       15.87 |   60 | 4363.94
 MONTEZUMA COMMAND           | Action      |       11.91 |   61 | 4375.85
 DOGMA FAMILY                | Animation   |      178.70 |    1 |  178.70
 SUNRISE LEAGUE              | Animation   |      170.76 |    2 |  349.46
 TITANIC BOONDOCK            | Animation   |      154.77 |    3 |  504.23
 FORRESTER COMANCHEROS       | Animation   |      146.73 |    4 |  650.96
 FALCON VOLUME               | Animation   |      127.77 |    5 |  778.73
 MISSION ZOOLANDER           | Animation   |      126.82 |    6 |  905.55
 DOORS PRESIDENT             | Animation   |      123.81 |    7 | 1029.36
 SLEEPLESS MONSOON           | Animation   |      121.80 |    8 | 1151.16
 THIEF PELICAN               | Animation   |      117.81 |    9 | 1268.97
 HORN WORKING                | Animation   |      112.76 |   10 | 1381.73
...

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:

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

O resultado será:

  category   |            title            | somatorio |  total  |       porcentagem
-------------+-----------------------------+-----------+---------+--------------------------
 Action      | FOOL MOCKINGBIRD            |    175.77 | 4375.85 |   4.01681958933692882500
 Action      | AMERICAN CIRCUS             |    343.55 | 4375.85 |   7.85104608247540477900
 Action      | STAGECOACH ARMAGEDDON       |    498.29 | 4375.85 |  11.38727332975307654500
 Action      | EASY GLADIATOR              |    649.06 | 4375.85 |  14.83277534650410777300
 Action      | MINDS TRUMAN                |    798.86 | 4375.85 |  18.25611024143880617500
 Action      | KISSING DOLLS               |    946.66 | 4375.85 |  21.63373973056663276800
 Action      | TRIP NEWTON                 |   1092.38 | 4375.85 |  24.96383559765531268200
 Action      | DRIFTER COMMANDMENTS        |   1234.14 | 4375.85 |  28.20343476124638641600
 Action      | SUSPECTS QUILLS             |   1367.84 | 4375.85 |  31.25884113943576676500
 Action      | WATERFRONT DELIVERANCE      |   1489.67 | 4375.85 |  34.04298593416136293500
 Action      | TRUMAN CRAZY                |   1611.44 | 4375.85 |  36.82575956671275295100
 Action      | CLUELESS BUCKET             |   1724.19 | 4375.85 |  39.40240181907515111300
 Action      | SOUTH WAIT                  |   1831.97 | 4375.85 |  41.86546613800747283400
 Action      | DARN FORRESTER              |   1925.79 | 4375.85 |  44.00950672440782933600
 Action      | CAMPUS REMEMBER             |   2016.60 | 4375.85 |  46.08476067506884376700
...
 Action      | REAR TRADING                |   4163.15 | 4375.85 |  95.13923009243918324400
 Action      | CELEBRITY HORN              |   4195.91 | 4375.85 |  95.88788463955574345600
 Action      | DANCES NONE                 |   4227.77 | 4375.85 |  96.61597175405921135300
 Action      | SIDE ARK                    |   4257.65 | 4375.85 |  97.29881051681387616100
 Action      | DRAGON SQUAD                |   4285.54 | 4375.85 |  97.93617240079070352000
 Action      | BULL SHAWSHANK              |   4307.38 | 4375.85 |  98.43527543220174366100
 Action      | BRIDE INTRIGUE              |   4329.19 | 4375.85 |  98.93369288252568072500
 Action      | CROW GREASE                 |   4348.07 | 4375.85 |  99.36515191334255059000
 Action      | LAWRENCE LOVE               |   4363.94 | 4375.85 |  99.72782430842007838500
 Action      | MONTEZUMA COMMAND           |   4375.85 | 4375.85 | 100.00000000000000000000
 Animation   | DOGMA FAMILY                |    178.70 | 4656.30 |   3.83781113759852243200
 Animation   | SUNRISE LEAGUE              |    349.46 | 4656.30 |   7.50510061636922019600
 Animation   | TITANIC BOONDOCK            |    504.23 | 4656.30 |  10.82898438674484032400
 Animation   | FORRESTER COMANCHEROS       |    650.96 | 4656.30 |  13.98019887034770096400
 Animation   | FALCON VOLUME               |    778.73 | 4656.30 |  16.72422309559091982900
 Animation   | MISSION ZOOLANDER           |    905.55 | 4656.30 |  19.44784485535725790900
 Animation   | DOORS PRESIDENT             |   1029.36 | 4656.30 |  22.10682301398105792200
 Animation   | SLEEPLESS MONSOON           |   1151.16 | 4656.30 |  24.72263385091166806300
...

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:

SELECT
    sales.category,
    title,
    TRUNC((somatorio / total) * 100) AS porcentagem,
    CASE
        WHEN (somatorio / total)  0.2  AND  (somatorio / total) <= 0.8 THEN 'B'
        ELSE 'C' END AS curva_abc
    FROM
        (SELECT
            category,
            sum(total_sales) total
          FROM sales_pgday
          GROUP BY category) AS tot,
        (SELECT
            title,
            category,
            sum(total_sales) OVER (PARTITION BY category ORDER BY total_sales DESC) AS somatorio
          FROM sales_pgday) sales
    WHERE tot.category = sales.category;

E finalmente temos a nossa curva ABC:

  category   |            title            | porcentagem | curva_abc
-------------+-----------------------------+-------------+-----------
 Action      | FOOL MOCKINGBIRD            |           4 | A
 Action      | AMERICAN CIRCUS             |           7 | A
 Action      | STAGECOACH ARMAGEDDON       |          11 | A
 Action      | EASY GLADIATOR              |          14 | A
 Action      | MINDS TRUMAN                |          18 | A
 Action      | KISSING DOLLS               |          21 | B
 Action      | TRIP NEWTON                 |          24 | B
 Action      | DRIFTER COMMANDMENTS        |          28 | B
 Action      | SUSPECTS QUILLS             |          31 | B
 Action      | WATERFRONT DELIVERANCE      |          34 | B
 Action      | TRUMAN CRAZY                |          36 | B
...
 Action      | EXCITEMENT EVE              |          69 | B
 Action      | BAREFOOT MANCHURIAN         |          70 | B
 Action      | HANDICAP BOONDOCK           |          72 | B
 Action      | PARK CITIZEN                |          73 | B
 Action      | UPRISING UPTOWN             |          74 | B
 Action      | MOCKINGBIRD HOLLYWOOD       |          76 | B
 Action      | PATRIOT ROMAN               |          77 | B
 Action      | GRAIL FRANKENSTEIN          |          78 | B
 Action      | SHRUNK DIVINE               |          79 | B
 Action      | CADDYSHACK JEDI             |          81 | C
 Action      | GOSFORD DONNIE              |          82 | C
 Action      | ENTRAPMENT SATISFACTION     |          83 | C
 Action      | SPEAKEASY DATE              |          84 | C
 Action      | MIDNIGHT WESTWARD           |          85 | C
 Action      | FANTASY TROOPERS            |          86 | C
 Action      | WOMEN DORADO                |          87 | C
...
 Action      | DRAGON SQUAD                |          97 | C
 Action      | BULL SHAWSHANK              |          98 | C
 Action      | BRIDE INTRIGUE              |          98 | C
 Action      | CROW GREASE                 |          99 | C
 Action      | LAWRENCE LOVE               |          99 | C
 Action      | MONTEZUMA COMMAND           |         100 | C
 Animation   | DOGMA FAMILY                |           3 | A
 Animation   | SUNRISE LEAGUE              |           7 | A
 Animation   | TITANIC BOONDOCK            |          10 | A
 Animation   | FORRESTER COMANCHEROS       |          13 | A
 Animation   | FALCON VOLUME               |          16 | A
 Animation   | MISSION ZOOLANDER           |          19 | A
 Animation   | DOORS PRESIDENT             |          22 | B
 Animation   | SLEEPLESS MONSOON           |          24 | B
 Animation   | THIEF PELICAN               |          27 | B
 Animation   | HORN WORKING                |          29 | B
 Animation   | GANGS PRIDE                 |          32 | B
...

É 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

    Curtir

  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

    Curtir

  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

    Curtir

  4. Pingback: SQL para DBAs | Savepoint

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s