Análise de dados com SQL: médias

pexels-photo-616353.jpeg
Foto por Lukas

Estatística: a ciência que diz que se você comeu um frango e eu não comi nenhum então teremos comido meio frango cada um, em média. (Dino Segre)

Este é o segundo artigo sobre como utilizar a linguagem SQL para realizar análise de dados, tratando agora de médias. Em sua primeira versão foi publicado junto com distribuição de frequências mas acréscimos no artigo original fizeram com que fosse necessário dividi-lo em dois, cada um tratando de um assunto.

1. Introdução

Não é objetivo deste artigo tratar dos conceitos de estatística mas ele contém uma rápida revisão para a compreensão dos exemplos de programação em sql presentes neste artigo. De qualquer forma recomenda-se a leitura prévia de livros didáticos para quem não tem conhecimentos básicos em estatística. Por favor leia com atenção o artigo e, tendo sugestões para melhorias, poste-as ao final, nos comentários do artigo. Seu retorno é fundamental para que o conteúdo dessa nova série seja útil.

Na Estatística Descritiva dois tipos de medidas são geralmente utilizadas: a primeira é a que descreve o valor central dos dados observados; e a segunda descreve como elas estão dispersas. Estas medidas são conhecidas como “medidas de tendência central” e “medidas de dispersão”. Das medidas de tendência central podemos citar moda, mediana e média.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.

3. Média

No verbete sobre média aritmética do wikipedia encontramos o seguinte trecho: “Um número é a média aritmética de dois outros quando o excesso do primeiro para o segundo é igual ao excesso do segundo para o terceiro, a média geométrica quando a proporção do segundo para o terceiro é igual à proporção do primeiro para o segundo, e a média harmônica quando a quantidade que o primeiro excede o segundo em relação ao primeiro é igual à quantidade que o segundo excede o terceiro em relação ao terceiro”, segundo definição de Arquitas de Tarento. Óbvio que o conceito de média aritmética era para somente dois valores; e assim o foi por muito tempo.

3.1 Média aritmética

3.1.1. Média aritmética simples

Conceito. Média é a medida de tendência central mais comumente utilizada para descrever resumidamente uma distribuição de frequências ou uma lista de números. Dentre as vários formas de calcular média, a média aritmética é a medida de tendência central mais conhecida e utilizada no dia a dia. Conforme consta no verbete da wikipedia sobre média, sendo n o número total de valores e xi cada valor, em que i = 1, …, n, a média aritmética é a soma dos valores xi dividido pelo número total de valores n, conforme a seguinte fórmula:

p049_formula 3.1

O símbolo do cálculo de média aritmética é , no caso de amostras, ou 𝞵, no caso da população.

Aplicação. O primeiro exemplo é o cálculo da média do valor de ticket médio, utilizando informações da tabela #ticketmedio gerada no capítulo anterior. Como a coluna ticketMedio contém o valor do ticket médio de cada cliente, basta somar os valores de ticket médio e então dividir pelo número de clientes:

-- código #3.1
-- © José Diz / Porto SQL

SELECT sum (ticketMedio) as [Soma ticket médio],
       count(*) as [Qtd clientes],
       (sum (ticketMedio) / count (*)) as [Média aritmética]
  from #ticketmedio;

O resultado é o seguinte:

p049_codigo #3.1

No SQL Server estão disponíveis algumas funções matemáticas e uma delas é a AVG(), que calcula a média aritmética. Desta forma, o código sql #3.1 pode ser simplificado para

-- código #3.2
-- © José Diz / Porto SQL

SELECT avg (ticketMedio) as [Média aritmética]
  from #ticketmedio;

3.1.2. Média aritmética ponderada

Conceito. Quando os dados estiverem agrupados em tabela de distribuição de frequência podemos utilizar a média aritmética ponderada, com a seguinte fórmula:

p049_formula 3.2

Entretanto, às vezes não temos os dados originais para calcular a média aritmética mas sim a tabela com a distribuição de frequências por determinada coluna. Nestes casos podemos calcular a média aritmética da coluna usando os valores de frequência como ponderação. Aqui temos duas possibilidades: a classe é um valor ou a classe é um intervalo de valores.

Aplicação. Na tabela temporária #classe, gerada no capítulo 2, temos a distribuição de frequências por classes onde cada classe é um intervalo e com seus valores inicial e final. Neste caso, para calcular a média utilizamos o ponto médio da classe. Por exemplo, se a classe inicia em 5 e termina em 9 temos então 7 como o ponto médio e é esse valor que é utilizado no cálculo da média.

O passo seguinte é implementar a fórmula de cálculo da média aritmética ponderada para distribuição de frequências em intervalos:

-- código #3.3
-- © José Diz / Porto SQL

SELECT sum (((inicio + fim) / 2) * freqabs)
       / sum (freqabs) as [Média aritmética ponderada]
  from #classe;

que retorna o seguinte resultado:

p049_codigo #3.3

Para um mesmo conjunto de dados temos dois valores diferentes: 1.678,1178 e 5.633,2985. A diferença é porque no cálculo de média aritmética ponderada do código #3.3 foram utilizados intervalos de valores e não os próprios valores.

Para gerar a distribuição de frequências por valor temos o seguinte código sql

-- código #3.4
-- © José Diz / Porto SQL

-- gera distribuição de frequências por valor
SELECT ticketMedio, count(*) as Freq
  from #ticketmedio
  group by ticketMedio
  order by ticketMedio;

e cujo resultado é o seguinte (trecho inicial):

p049_codigo #3.4

Assim, ao utilizarmos a distribuição de frequências por valor, e não por intervalo de valores, obtemos o mesmo valor de média aritmética:

-- código #3.5
-- © José Diz / Porto SQL

-- gera distribuição de frequências por valor
with freqValor as (
SELECT ticketMedio, count(*) as Freq
  from #ticketmedio
  group by ticketMedio
)
-- calcula a média aritmética ponderada
SELECT sum (ticketMedio * Freq) as [Soma ticket médio],
       sum(Freq) as [Qtd clientes],
       (sum (ticketMedio * Freq) / sum(Freq)) as [Média aritmética ponderada]
  from freqValor;

que retorna o seguinte resultado:

p049_codigo #3.1

Mesmos valores da média aritmética calculada no item 3.1.

3.2. Média aparada

Conceito. A média aparada (trimmed) ou truncada consiste em calcular a média somente após descartar parte da amostra (ou de uma distribuição de frequências) em seus extremos. A quantidade de observações a serem descartadas geralmente é definida como percentual mas também pode ser dada como uma quantidade fixa de pontos. Para a maioria das aplicações estatísticas é realizado o descarte de 5% a 25% em cada extremo.

Esta média é uma estimativa útil porque é menos sensível a desvios nas observações (outliers) do que a média aritmética.

Aplicação. Uma forma de obter os extremos a serem descartados é através do uso de OFFSET e FETCH na cláusula ORDER BY. Entretanto, é necessário previamente obter o número de linhas que a tabela possui para então calcular o número de linhas a ignorar nos extremos.

-- código #3.6
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- define o percentual a descartar
declare @percentual real;
set @percentual= 0.05;

-- calcula quantidade de linhas
declare @qtdLinhas int, @salto int, @tamBloco int;
set @qtdLinhas= (SELECT count(*) from #ticketmedio);
set @salto= ceiling ((@qtdLinhas * @percentual));
set @tamBloco= ceiling (@qtdLinhas * (1 - (2 * @percentual)));

-- calcula a média, após limpeza
with LeMiolo as (
SELECT ticketMedio
  from #ticketmedio
  order by ticketMedio asc
  offset @salto rows
  fetch next @tamBloco rows only
)
SELECT sum(ticketMedio) as [Soma ticket médio], 
       count(*) as [Qtd clientes],
       avg (ticketMedio) as [Média aparada]
  from LeMiolo;

que retorna o seguinte resultado:

p049_codigo #3.6

Considerando-se a distribuição irregular das observações, com 98% concentrado na primeira classe da distribuição de frequências, o uso da média aparada descartou os valores ao final que distorceram a média aritmética.

Também é possível calcular a média aparada utilizando a função de janela NTILE(); útil para instalações com SQL Server anterior a 2012.

-- código #3.7
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- define o percentual a descartar
declare @percentual real;
set @percentual= 0.05;

-- calcula número de blocos
declare @qtdBloco int;
set @qtdBloco= (1 / @percentual);

-- calcula a média aparada
with Blocos as (
SELECT ticketMedio,
       ntile (@qtdBloco) over (order by ticketMedio) as numBloco
  from #ticketmedio
)
SELECT sum(ticketMedio) as [Soma ticket médio], 
       count(*) as [Qtd clientes],
       avg (ticketMedio) as [Média aparada]
  from Blocos
  where numBloco between 2 and (@qtdBloco -1);

E se o que tivermos for somente a distribuição de frequências, é possível calcular a média aparada? Sim, mas é necessário algum esforço adicional de programação.

3.3. Média móvel

Conceito. A média móvel analisa subconjuntos sequenciais da amostra, calculando a média aritmética para cada subconjunto. Considerando-se tamanho fixo do subconjunto, o primeiro elemento da média móvel é obtido tomando a média do subconjunto fixo inicial da série numérica. A seguir é calculada a média para o subconjunto seguinte e assim em diante. Geralmente são análises temporais e comumente utilizadas para suavizar flutuações em curto prazo.

Aplicação. Uma aplicação típica da média móvel é a avaliação diária de vendas nos últimos X dias. Considerando-se a tabela Sales.SalesOrderHeader, o exemplo a seguir calcula a média móvel semanal nos últimos 20 dias.

-- código #3.8
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- obtém data da venda mais recente
declare @DataRecente datetime;
SELECT @DataRecente= max (OrderDate)
  from AdventureWorks.Sales.SalesOrderHeader);             

-- calcula data inicial a consultar
declare @InicioPrimeiraSemana datetime,
        @InicioUltimaSemana datetime;
set @InicioUltimaSemana= dateadd (day, -6, @DataRecente);
set @InicioPrimeiraSemana= dateadd (day, -19,
                                    @InicioUltimaSemana);

--
with
-- calcula vendas de cada dia
VendasDia as (
SELECT OrderDate, sum (TotalDue) as somaDia
  from AdventureWorks.Sales.SalesOrderHeader
  where OrderDate >= @InicioPrimeiraSemana
  group by OrderDate
),
-- calcula média móvel da semana
MediaSemana as (
SELECT OrderDate,
       avg (somaDia) 
             over (order by OrderDate
                   rows between current row 
                                and 6 following) as somaSemana
  from VendasDia
)
-- emite relatório
SELECT convert (char(10), OrderDate, 103) as [Data inicial],
       convert (char(10), dateadd (day, +6, OrderDate), 103) as [Data final],
       somaSemana as [Vendas na semana]
  from MediaSemana
  where OrderDate <= @InicioUltimaSemana;

O primeiro passo do código sql anterior é obter qual foi a data de venda mais recente; no caso, a última venda foi registrada em 30/6/2014. Considerando-se esta data, temos que a última semana compreende o período de 24/6/2014 a 30/6/2014. Como deve ser retornada a média móvel semanal dos 20 últimos dias, então na variável @InicioPrimeiraSemana é calculada a data inicial da primeira semana: 5/6/2014. A partir dessa data é calculada a média móvel, obtendo-se então o seguinte resultado:

p049_codigo #3.8

Simples de implementar quando se utiliza a função de janela AVG().

Passo a passo. O primeiro passo do código sql anterior foi obter qual a data de venda mais recente:

-- código #3.8 trecho 1
-- © José Diz / Porto SQL

...
-- obtém data da venda mais recente
declare @DataRecente datetime;
set @DataRecente= (SELECT max (OrderDate)
                   from AdventureWorks.Sales.SalesOrderHeader);
...

obtendo-se que a última venda foi registrada em 30/6/2014. Considerando-se esta data, temos que a última semana compreende então o período de 24/6/2014 a 30/6/2014 e estes são os valores da última semana no resultado do código #3.8.

Sabendo-se qual é a data mais recente foi possível determinar o conjunto de dados (amostra) lido na tabela Sales.SalesOrderHeader:

-- código #3.8 trecho 2
-- © José Diz / Porto SQL

...
-- calcula data inicial a consultar
declare @InicioPrimeiraSemana datetime,
        @InicioUltimaSemana datetime;
set @InicioUltimaSemana= dateadd (day, -6, @DataRecente);
set @InicioPrimeiraSemana= dateadd (day, -19,
                                    @InicioUltimaSemana);
...

Como deve ser retornada a média móvel semanal dos 20 últimos dias, então na variável @InicioPrimeiraSemana é calculada a data inicial da primeira semana: 5/6/2014, que permitiu filtrar o conjunto de linhas lidas ao período de 5/6/2014 a 30/6/2014.

No trecho seguinte foi então calculada a venda de cada dia:

-- código #3.8 trecho 3
-- © José Diz / Porto SQL

...
VendasDia as (
-- calcula vendas de cada dia
SELECT OrderDate, sum (TotalDue) as somaDia
  from AdventureWorks.Sales.SalesOrderHeader
  where OrderDate >= @InicioPrimeiraSemana
  group by OrderDate
),
...

E a parte principal do cálculo da média móvel está no seguinte trecho:

-- código #3.8 trecho 4
-- © José Diz / Porto SQL

...
MediaSemana as (
-- calcula média móvel da semana
SELECT OrderDate,
       avg (somaDia) 
             over (order by OrderDate
                   rows between current row 
                                and 6 following) as somaSemana
  from VendasDia
)
...

Neste caso AVG() é uma função de janela por causa da presença da cláusula OVER().

3.4. Observações sobre médias

Neste capítulo, para um mesmo conjunto de dados foi calculada a média mas com diferentes metodologias e obtendo-se valores divergentes; qual a causa? 😕

Como citado anteriormente, “média aritmética é a medida de tendência central mais conhecida e utilizada no dia a dia” e isto pode ocasionar utilizações e interpretações incorretas se a medida não for corretamente utilizada. Por exemplo, se uma pessoa está com um dos pés em um balde de água fria à temperatura de -10ºC (anticongelante misturado à água) e o outro pé em outro balde de água quente à temperatura de 40ºC, se calcularmos a média aritmética obtemos o valor de 25ºC e alguém que veja somente a média pode afirmar que a pessoa está em segurança pois a temperatura de 25ºC não é prejudicial ao corpo humano. 😞


O terceiro artigo desta série é sobre mediana e está disponível para leitura emAnálise de dados com SQL: mediana”.


A. Material de estudos

A.1. Verbetes

A.2. Documentação

A.3. Artigos

A.4. Videoaulas

A.5. Livros

B. Anexos

B.1 Calcula ticket médio por cliente

-- código #B.1
-- gera valores de ticket médio por cliente
IF Object_ID ('tempdb..#ticketmedio', 'U') is not null
  DROP TABLE #ticketmedio;
go

SELECT CustomerID, avg (TotalDue) as ticketMedio
  into #ticketmedio
  from AdventureWorks.Sales.SalesOrderHeader
  group by CustomerID;


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Análise de dados com SQL

Tendo sugestões para melhorias deste artigo, poste-as abaixo nos comentários. Seu retorno é fundamental para que o conteúdo dessa nova série seja útil.

Deixe um comentário

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 )

Foto do Google

Você está comentando utilizando sua conta Google. 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 )

Conectando a %s