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.
