O princípio de Pareto, a curva ABC e consultas SQL

Bom Dia Pessoal,

Devo não nego, pago quando puder. Já nem sei quantas promessas de postagens aqui no blog estou devendo, mas é hora de pagar as que me lembro. Uma idéia já antiga de postar um artigo voltado ao mundo dos negócios é referente à elaboração de uma curva ABC em T-SQL. Já tinha pensado em algo do tipo, e uma dúvida no MSDN ("Qual o caminho para mim Criar uma tabela para CURVA ABC ??") acabou me incentivando a escrever esse artigo. Confesso que demorei um pouco, pois, com tantos afazeres é meio complicado escrever mais um daqueles artigos bíblicos cheio de conceitos, números e instruções SQL até chegar ao resultado final. Para sentir que realmente sou graduado em administração de empresas, vamos aos detalhes da curva ABC em T-SQL.

O princípio de Pareto

Vilfredo Pareto era um economista italiano que observou que 80% da renda do país estava em poder de apenas 20% da população. Posteriormente, outros estudiosos começaram a perceber que esse mesmo princípio se aplicava em muitas outras áreas como contabilidade, economia, matemática e acreditem existe aplicação desse princípio até no report de bugs para a Microsoft ("CEO: 80-20 Rule Applies To Bugs, Not Just Features"). Esse princípio costuma ser mencionado nos cursos de gerência de projetos, gestão de qualidade, estatística, etc. É um princípio bem simples e muitas vezes é chamado da "regra do 80/20". Ex:

  • 80% dos problemas são causados por 20% das causas
  • 80% do faturamento foi gerado por 20% dos clientes
  • 80% das pessoas mais bem sucedidas estudaram em 20% das universidades disponíveis

Claro que a regra não é perfeita e nem sempre a proporção de causa e efeito será de acordo com a regra de pareto (existem variações que utilizam a regra 70/30 por exemplo). O que a regra tem de mais importante é mostrar que pequenos grupos são responsáveis por grandes resultados. Isso é natural, pois, não vivemos em um mundo completamente uniforme e sempre haverá maiores concentrações em torno de algumas poucas ocorrências. Se esses pequenos grupos forem estudados, compreendidos e controlados naturalmente será possível impactar nos grandes resultados. Atacar poucas causas que provocam grandes efeitos é muito mais interessante do que atacar muitas causas que provocam poucos efeitos. Nesse sentido, o diagrama de pareto funciona como um mecanismo de priorizar que causas devem ser trabalhadas em primeiro lugar.

A curva ABC

A curva ABC (não é custeio ABC) é uma especialização do princípio de pareto e também aplica a regra 80/20 e normalmente mostra o formato de curva com a evolução das causas e o resultado acumulado de seus efeitos que normalmente resulta na forma de uma curva, pois, à medida que mais causas são levadas em consideração, os resultados obtidos não aumentam na mesma proporção. A curva é uma figura muito natural, pois, se 20% das causas tem 80% por cento dos resultados, não é de se estranhar que 30% consigam pouca coisa acima dos 80% e que os 40% das causas também não subam muito esse patamar.

Normalmente a idéia de curva ABC aplicada ao mundo dos negócios é utilizada na relação clientes x faturamento, estoque x valor, profissionais x custo, etc. Por ser uma aplicação do princípio de pareto, para que a mesma seja construída, é necessário obedecer algumas etapas:

  • Determinar o objetivo do diagrama, ou seja, qual será a relação de causas e efeitos a serem estudados;
  • Definir um critério de categorização para a causas e uma unidade de mensuração para os efeitos;
  • Em uma tabela, organize os dados com as categorias das causas de forma ordenada (em ordem crescente ou decrescente) em relação aos efeitos;
  • Realize os cálculos de freqüência bem como o total e a porcentagem de cada item sobre o total acumulado;
  • Com base nos pontos de correspondência, trace a curva ABC

Sim, admito que a descrição não é nada intuitiva e que apenas com ela é realmente complicado montar uma curva ABC. Vejamos com um exemplo prático. Para demonstrar a elaboração da curva ABC, utilizei-me dos ingredientes necessários para a elaboração da cesta básica conforme a lista no link: http://www.ufrnet.br/~scorpius/39-Cesta%20basica%20e%20seg%20alim.htm

Composição e Preço da Cesta Básica em Natal/RN em Abril de 2001

Produtos Valor (R$)
Carne de 2ª 6,28
Leite pasteurizado 0,89
Feijão preto 1,60
Arroz de 2ª 1,03
Farinha de mandioca 0,96
Batata doce 1,64
Tomate 1,42
Pão francês 2,32
Café 4,90
Banana prata 0,76
Óleo de soja 1,17
Açúcar 0,79
Margarina 0,90

Essa lista tem os doze ingredientes considerados na cesta básica na cidade de Natal (RN), no ano de 2001. O total da cesta básica é de R$ 24,66 e se ordenarmos os produtos na ordem decrescente e o seu percentual sobre o total teremos a seguinte tabela:

Posição Produtos Valor (R$) % do Total % do Total Acumulado
01 Carne de 2ª 6,28 25,4663% 25,4663%
02 Café 4,90 19,8702% 45,3366%
03 Pão francês 2,32 9,4079% 54,7445%
04 Batata doce 1,64 6,6504% 61,3950%
05 Feijão preto 1,60 6,4882% 67,8832%
06 Tomate 1,42 5,7583% 73,6415%
07 Óleo de soja 1,17 4,7445% 78,3861%
08 Arroz de 2ª 1,03 4,1768% 82,5629%
09 Farinha de mandioca 0,96 3,8929% 86,4558%
10 Margarina 0,90 3,6496% 90,1054%
11 Leite pasteurizado 0,89 3,6091% 93,7145%
12 Açúcar 0,79 3,2036% 96,9181%
13 Banana prata 0,76 3,0819% 100,0000%

A tabela em questão nos permite concluir que apenas quatro dos treze itens já são responsáveis por mais de 60% do valor da cesta básica e que os outros 9 são responsáveis por quase 40%. O que será mais efetivo ? Focar na redução de preço de quatro produtos ou se preocupar com outros nove ? O foco nos quatro primeiros produtos é bem mais efetivo, pois, eles são apenas quatro e representam 60% do valor da cesta básica. Em contrapartida, focar nos outros nove, além de representar mais produtos a serem estudados será menos efetivos, pois, representam apenas 40% do total.

Essa é a "sacada" do princípio de pareto. Ele permite priorizar a atenção em menos "causas" para entender "mais efeitos". Evidente que a proporção não foi de 80/20, seria necessário avaliar os sete primeiros produtos para chegar perto de 80% e isso representa um pouco mais de 50% de todos os produtos (7/13), mas é notável que os primeiros produtos (o lado 20) é responsável pela maior fatia do preço (o lado 80).

De posse dos produtos, basta apenas traçar a curva ABC e mostra a evolução gradual do preço à medida que mais produtos são adicionados. O gráfico abaixo foi feito no Excel. Para manter a legibilidade do gráfico, considerei apenas a posição do produto e não o seu nome nas legendas.

Visualmente é perceptível que alguns poucos produtos tem um papel fundamental na formação do preço da cesta básica enquanto alguns outros praticamente não contribuem com o valor final. Como os primeiros produtos sempre terão um papel mais importante (afinal a lista é decrescente) é normal que o gráfico comece muito acentuado e posteriormente comece a declinar-se até que o valor total seja atingido. Esse comportamento que transforma o gráfico em um curva e a divisão desses produtos é que caracteriza a curva como A, B e C. Em relação aos intervalos A, B e C as seguintes características são observadas:

Intervalo Qtd Produtos % Produtos % Preço % Médio
A 3 23,0769% 54,7445% 18,2481%
B 4 30,7692% 23,6415% 5,9103%
C 6 46,1538% 21,6139% 3,6023%

Pode-se perceber que os produtos do intervalo A contribuem em média 18,21% para a formação do preço da cesta básica já que apenas 3 produtos respondem por 54% do preço. No caso dos produtos do intervalo B, essa contribuição cai para próxima de 6% e a categoria C cai ainda mais chegando a tocar os 3,6%. Isso evidencia o fato de que poucos produtos tem forte impacto na formação do preço (intervalo A), alguns produtos tem impacto médio (intervalo B) e muitos produtos tem pouco impacto (intervalo C). Seguindo as regras, a atenção deve ser focada primeiro no intervalo A, depois no B e por último C, já que essa ordem trará os melhores resultados com o menor esforço de forma mais rápida.

Os percentuais são meramente didáticos já que o exemplo possui pouquíssimas observações (apenas treze) e que um produto aqui ou ali, pode fazer realmente muita diferença, já que 1 em 13 representa 7%. A curva ABC nunca irá obedecer a percentuais exatos (a idéia maior é exemplificar a distribuição desigual entre as causas e os efeitos), mas uma classificação ABC típica apresenta uma distribuição de 20% das causas no intervalo A e que estas respondem por 65% dos efeitos. As causas no intervalo B representam 30% das causas e 25% dos efeitos. Por último os restantes 50% (intervalo C) das causas provocam 10% dos efeitos.

Implementação no SQL Server 2005 e 2008

Agora que a teoria está exemplificada, vejamos uma aplicação nas versões 2005 e 2008 do SQL Server. Utilizo essas versões por serem mais recentes e disponibilizarem uma série de funções úteis para consultas desse tipo como as funções de ranqueamento (Row_Number, Rank, Dense_Rank, NTile). O uso dessas funções é fundamental para evitar subqueries de acumulação e os indesejáveis cursores. Para aqueles que não tem à mão o uso dessas funções não há muita alternativa senão os cursores ou utilizar a lógica na aplicação. Para aqueles que não conhecem as funções de ranqueamento disponíveis a partir do SQL Server 2005, a leitura do artigo abaixo é indicada:

SQL Server 2005 New Features: As funções de ranqueamento
http://www.plugmasters.com.br/sys/materias/536/1/SQL-Server-2005-New-Features%3A-As-fun%E7%F5es-de-ranqueamento

Para ser imparcial, ao invés de colocar as instruções SQL, utilizarei o resultado de uma consulta feita no AdventureWorks2008 junto com o SQL Server 2008. A consulta abaixo, obtém a lista de subcategorias de produtos e o total vendido de cada subcategoria e cria uma tabela a partir dessa consulta. A criação de uma tabela com o resultado é necessária para facilitar os scripts utilizados. Possivelmente em ambiente de produção isso será encapsulado em uma View ou em uma CTE, já que tabelas (sejam temporárias ou permanentes) incorrerão em mais uso de I/O. Para aqueles que não possuem o AdventureWorks2008, eu disponibilizei as instruções de INSERT juntamente com os scripts utilizados no final do artigo. 

SELECT
    Identity(INT,1,1) As Posicao, PS.Name As Categoria,
    CAST(SUM(LineTotal) As INT) As ValorVendido
INTO Vendas
FROM Production.ProductSubcategory As PS
    INNER JOIN Production.Product As P ON PS.ProductSubcategoryID = P.ProductSubcategoryID
    INNER JOIN Sales.SalesOrderDetail As SOD ON P.ProductID = SOD.ProductID
GROUP BY PS.Name ORDER BY ValorVendido DESC

O resultado dessa consulta produziu 35 linhas ordenadas pelas subcategorias (agora categorias) que mais venderam. A primeira coluna é a criação de um Identity em tempo de execução que já será contemplado como a coluna "Posição". Essa coluna poderia ser obtida com o uso do Row_Number, mas acho que o Identity é mais direto. A criação da tabela já adiantou algumas etapas como a geração dos valores já na ordem descrescente e uma coluna de posição (é útil para contagem de categorias em consultas mais avançadas). O quadro abaixo mostra os 10 primeiros itens dessa tabela:

Posição Categoria ValorVendido
01 Road Bikes 43.909.437
02 Mountain Bikes 36.445.443
03 Touring Bikes 14.296.291
04 Mountain Frames 4.713.930
05 Road Frames 3.851.350
06 Touring Frames 1.642.327
07 Jerseys 752.259
08 Wheels 680.831
09 Helmets 484.048
10 Shorts 413.600

Apenas os três primeiros itens já dão uma idéia muito forte de concentração no total vendidos, pois, são itens na casa de dezenas de milhões enquanto que o sétimo item já aparece nas casas de centenas de milhares, ou seja, logo nota-se uma grande concentração de vendas em poucas categorias (possivelmente as categorias do intervalo A). Antecipando-me à implementação, adianto que o total vendido é de 109.846.364 e isso quer dizer que as duas primeiras categorias têm uma enorme concentração nessa cifra.

O próximo passo é calcular o percentual de cada categoria vendida em relação ao total vendido, para que se saiba quanto em termos percentuais, cada categoria colabora com o total vendido. Uma implementação inicial consiste em capturar o total vendido de todas as categorias em uma variável e efetuar a divisão do valor vendido de cada categoria pelo valor dessa variável. Essa é uma boa implementação e talvez a mais performática (para fins declarativos, pode-se até utilizar subqueries ou CTEs), mas como a idéia é demonstrar novos recursos, irei utilizar a cláusula OVER conforme o script abaixo:

;WITH Res (Posicao, Categoria, ValorVendido, TotalVendido) As (
SELECT
    Posicao, Categoria, ValorVendido,
    SUM(ValorVendido) OVER (PARTITION BY 1) As TotalVendido
FROM Vendas)

SELECT
    Posicao, Categoria, ValorVendido, TotalVendido,
    ROUND(ValorVendido / CAST(TotalVendido As Decimal(12,2)),4) As Perc
FROM Res

O resultado da consulta é exibido na tabela abaixo (simplificada para fins de exibição):

Posição Categoria ValorVendido TotalVendido Perc
01 Road Bikes 43.909.437 109.846.364 0.3997
02 Mountain Bikes 36.445.443 109.846.364 0.3318
03 Touring Bikes 14.296.291 109.846.364 0.1301
04 Mountain Frames 4.713.930 109.846.364 0.0429
32 Cleaners 18.406 109.846.364 0.0002
33 Locks 16.240 109.846.364 0.0001
34 Pumps 13.514 109.846.364 0.0001
35 Chains 9.377 109.846.364 0.0001

O uso da sintaxe PARTITION BY 1 fez com que todo o SET fosse considerado uma única partição. Isso possibilitou o uso de funções agregadas sem a utilização do GROUP BY exibindo uma coluna estática que representa o valor total (109.846.364). Estando disponíveis a coluna valor vendido e a coluna TotalVendido é possível fazer uma divisão para saber qual o percentual de agregação de cada categoria em relação ao total. Há um forte destaque para as duas primeiras que juntas representam apenas 6% da quantidade  de produtos (2/35) e mais de 70% do total vendido. É interessante notar que as três últimas categorias praticamente não colaboram para o total vendido, pois, suas contribuições individuais são muito baixas (entre 0,1% e 0,2%).

O próximo passo é fazer um total acumulado em relação à contribuição das categorias em relação ao total vendido. Já falei de consultas com subtotal (ou saldo) em posts anteriores como "Dicas e Truques sobre consultas complexas no SQL Server" e "Cálculos financeiros, consultas SQL e o mercado de ações – Parte I" e não vou me ater aos detalhes.

;WITH Res (Posicao, Categoria, ValorVendido, TotalVendido) As (
SELECT
    Posicao, Categoria, ValorVendido,
    SUM(ValorVendido) OVER (PARTITION BY 1) As TotalVendido
FROM Vendas),

QPerc (Posicao, Categoria, ValorVendido, TotalVendido, Perc) As (

SELECT
    Posicao, Categoria, ValorVendido, TotalVendido,
    ValorVendido / CAST(TotalVendido As Decimal(12,2)) As Perc
FROM Res)

SELECT
    Posicao, Categoria, ValorVendido, TotalVendido, Perc,
    ROUND((SELECT SUM(TInt.Perc) FROM QPerc As TInt
        WHERE TInt.Posicao <= TOut.Posicao),4) As PercAcum
FROM QPerc As TOut

O resultado é idêntico à tabela anterior, mas agora com uma coluna adicional com o percentual acumulado:

Posição Categoria ValorVendido TotalVendido Perc Perc Acumulado
01 Road Bikes 43.909.437 109.846.364 0.3997 0.3997
02 Mountain Bikes 36.445.443 109.846.364 0.3318 0.7315
03 Touring Bikes 14.296.291 109.846.364 0.1301 0.8617
04 Mountain Frames 4.713.930 109.846.364 0.0429 0.9046
32 Cleaners 18.406 109.846.364 0.0002 0.9996
33 Locks 16.240 109.846.364 0.0001 0.9998
34 Pumps 13.514 109.846.364 0.0001 0.9999
35 Chains 9.377 109.846.364 0.0001 1.0000

Para reduzir um pouco a complexidade e o aninhamento de tantas CTEs no artigo, criarei uma View a partir desse ponto com base na consulta anterior.

CREATE VIEW vCategorias As
WITH Res (Posicao, Categoria, ValorVendido, TotalVendido) As (
SELECT
    Posicao, Categoria, ValorVendido,
    SUM(ValorVendido) OVER (PARTITION BY 1) As TotalVendido
FROM Vendas),

QPerc (Posicao, Categoria, ValorVendido, TotalVendido, Perc) As (

SELECT
    Posicao, Categoria, ValorVendido, TotalVendido,
    ValorVendido / CAST(TotalVendido As Decimal(12,2)) As Perc
FROM Res)

SELECT
    Posicao, Categoria, ValorVendido, TotalVendido, Perc,
    ROUND((SELECT SUM(TInt.Perc) FROM QPerc As TInt
        WHERE TInt.Posicao <= TOut.Posicao),4) As PercAcum
FROM QPerc As TOut

A classificação das categorias nos intervalos A, B e C podem se dar em virtude de suas quantidades, ou seja, o intervalo A representa 20% por cento da quantidade de categorias, o B fica em 30% e o C em 50%, ou pode-se fazer essa classificação em virtude da contribuição, ou seja, o intervalo A representa 65% do total vendido, o intervalo B fica com 25% e o intervalo C com 10%. O ideal seria que ambas as classificações retornassem o mesmo resultado, mas como disse anteriormente, não há garantias de que essas relações se comportem dessa forma em todas as situações. Em virtude da enorme discrepância (apenas duas categorias têm mais de 70% do total vendido), acredito que montar a curva com base nos percentuais de venda seja mais interessante. Para que esses intervalos possam ser cruzados, é importante delimitá-los. A CTE abaixo, cria esses intervalos:

;WITH Intervalos (Valor) As (
SELECT 0.65 UNION ALL
SELECT 0.90 UNION ALL
SELECT 1.00)

SELECT Intervalo, Valor FROM Intervalos

Essa CTE não tem absolutamente nenhum mistério. Ela apenas cria um SET com os valores necessários, ou seja, 65% para o intervalo A, 90% para o intervalo B (já que o acumulado de A em 65% com B em 25% dá 90%) e 100% para o intervalo C que acumula tudo já que a curva ABC acaba em C. Agora que os valores dos intervalos existem e os percentuais estão definidos, o próximo passo é combiná-los. É tentador fazer um simples JOIN, mas é preciso lembrar que não necessariamente os percentuais acumulados irão coincidir exatamente com os intervalos definidos.

;WITH Intervalos (Valor) As (
SELECT 0.65 UNION ALL
SELECT 0.90 UNION ALL
SELECT 1.00)

SELECT
    Posicao, Categoria, ValorVendido, PercAcum,
    MIN(Valor) As Valor, CASE
        WHEN MIN(Valor) = 0.65 THEN ‘A’
        WHEN MIN(Valor) = 0.90 THEN ‘B’
        ELSE ‘C’ END As Intervalo
FROM vCategorias As C
INNER JOIN Intervalos As I ON C.PercAcum <= I.Valor
GROUP BY Posicao, Categoria, ValorVendido, PercAcum

O resultado da consulta é exibido na tabela abaixo:

Posição Categoria ValorVendido Perc Acumulado Valor Intervalo
01 Road Bikes 43.909.437 0.3997 0.65 A
02 Mountain Bikes 36.445.443 0.7315 0.90 B
03 Touring Bikes 14.296.291 0.8617 0.90 B
04 Mountain Frames 4.713.930 0.9046 1.00 C
32 Cleaners 18.406 0.9996 1.00 C
33 Locks 16.240 0.9998 1.00 C
34 Pumps 13.514 0.9999 1.00 C
35 Chains 9.377 1.0000 1.00 C

O group by pode provocar dúvidas nesse resultado, mas ele é de fato necessário (a menos que subqueries sejam usadas). Ao elaborar o predicado C.PercAcum <= I.Valor, é produzido um plano cartesiano (mesmo que parcial). O valor acumulado da categoria na posição 1 é de 0.3997. Esse valor é menor que 0.65, 0.90 e 1.00. Nessa ocorrência, o predicado irá produzir três registros, pois, todos os valores são superiores a 0.3997. Por isso é necessário obter o menor valor possível para que a categoria esteja colocada no intervalo correto.

Uma vez que os dados estejam prontos, basta apenas carregar essa consulta para a ferramenta de relatórios mais adequada (seja o Excel, o SSRS, etc). Não é obrigatório utilizar a última consulta. Apenas a View com os percentuais acumulados já é capaz de montar a curva. A última consulta talvez seja interessante se a ferramenta permitir o uso da coluna intervalo. Não é muito comum visto que a maioria dos gráficos são em duas dimensões, e já estariam plotados o eixo X com as categorias e o eixo Y com os percentuais.

Aos interessados, disponibilizei o script para geração da tabela venda bem como as consultas utilizadas em um projeto no SSMS 2008. O projeto está disponível no link abaixo:

Curva ABC
http://cid-f4f5c630410b9865.skydrive.live.com/self.aspx/ProjetosSQLServer/20091001%5E_CurvaABC.zip

Embora o projeto seja no SSMS 2008, é possível abrir os arquivos diretamente no notepad, caso o SSMS utilizado seja o 2005.

[ ]s,

Gustavo

12 Respostas para “O princípio de Pareto, a curva ABC e consultas SQL

  1. Parabéns pelo artigo. Muito bom, explicou muito bem sobre curva ABC e melhor ainda como gerar em T-SQL. Muito obrigado.

  2. Sou leigo em economia, mas nao em logica. Sua explicacao foi abrangente e a primeira etapa da explicacao, sem o especifico “T-SQL”, foi o bastante para catagoriza-la no campo “A” da curva do Paretto, na cirva de minha assimilacao conceitual, Sam de Mattos, SC, USA

  3. Esse é o meu professor.
    Abraços Gustavo.
    Ass: Felipe Duarte

  4. Meu caro, desenvolvo há 28 anos e vejo o quanto podemos aprender sempre… Parabéns pelo artigo!

    Cláudio Estezi
    Dominus Pilates Sistemas

  5. Excelente post!

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