Análise de dados com SQL: distribuição de frequências

pngwing

Alguns artigos que relacionam as habilidades desejadas para um cientista de dados destacam SQL como uma das 3 linguagens que deve conhecer, sendo R e Python as duas outras.

Este é o primeiro artigo sobre como utilizar a linguagem SQL para realizar análise de dados, tratando de distribuição de frequências.

1. Introdução

O objetivo desta série de artigos é demonstrar formas de se implementar em SQL os principais conceitos estatísticos. Por favor leia com atenção o artigo e, tendo sugestões para melhorias, poste-as nos comentários do artigo, ao final. Seu retorno é fundamental para que o conteúdo dessa nova série seja útil.

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.

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?”.

2. Distribuição de frequências

Conceito. Séries estatísticas são tabelas construídas seguindo determinados critérios que as diferenciam e as classificam, para sintetizar um conjunto de dados ou informações (Martins, pág. 10).

Segundo Stevenson (pág. 31), “Uma distribuição de frequências é um método de grupamento de dados em classes, ou intervalos, de tal forma que se possa determinar o número ou a percentagem de observações em cada classe”, sendo esse valor denominado de frequência de classe. A apresentação usual de distribuição de frequência é na forma tabular, mas também pode ser representada graficamente.

A distribuição de frequências usualmente é calculada para classes (intervalos); entretanto, os dados podem ser apresentados não agrupados, no caso de variável discreta com poucos valores.

Stevenson ainda define alguns conceitos:

  • dados brutos: conjunto de dados numéricos obtidos na coleta de dados;
  • frequência absoluta (ƒi): número de vezes que o elemento aparece na amostra ou na classe;
  • frequência relativa (ƒri): proporção de observações de um elemento (ou de uma classe) em relação ao número total de observações;
  • frequência absoluta acumulada: soma da frequência absoluta de uma classe ou de um dado valor com a frequência absoluta das classes ou dos valores anteriores (ou posteriores);
  • frequência relativa acumulada: soma da frequência relativa de uma classe ou de um dado valor com a frequência relativa das classes ou dos valores anteriores (ou posteriores).

As frequências acumuladas podem ser crescentes, decrescentes ou até mesmo constar ambas.

O primeiro passo é determinar a amplitude total, através da fórmula

p049_formula 2.1

O segundo passo é calcular o número de classes. No caso de distribuição com dados agrupados, para definir o número de classes (representado por k) pode-se utilizar a Fórmula de Sturges:

p049_formula 2.2

onde n é o tamanho da amostra (ou população).

O terceiro passo é definir a amplitude de classe, ao dividir a amplitude total pela quantidade de classes:

p049_formula 2.3

De posse desses valores monta-se a tabela com a distribuição de frequências.

Eis vídeo em que é explicado o processo de montagem de tabela de distribuição de frequências:


Aplicação. Para demonstração será montada a tabela de distribuição de frequências considerando-se o ticket médio por cliente registrado na tabela Sales.SalesOrderHeader do banco de dados Adventure Works. O código sql que gera o ticket médio por cliente está no anexo.

Cenário série agrupada com classe intervalar. Para definir as classes precisamos antes obter algumas informações como menor valor, maior valor, tamanho da amostra para então calcular a amplitude total, a quantidade de classes e a amplitude de cada classe:

-- código #2.1 parte 1
-- © José Diz / Porto SQL

declare @n int, @maior money, @menor money, @amplitude money,
        @k smallint, @amplitude_classe money;

SELECT @maior= max (ticketMedio),
       @menor= min (ticketMedio), @n= count(*)
  from #ticketmedio;

set @k= round (1 + 3.32 * log10 (@n), 0, 0);

set @amplitude= @maior - @menor;
set @amplitude_classe= ceiling ((@amplitude / @k) * 1000) / 1000;

que retorna o seguinte resultado:

p049_codigo #2.1

No cálculo de k foi utilizado o arredondamento. O valor de @amplitude foi declarado como money, o que significam 4 casas decimais. Observe que no cálculo da amplitude de classe foi necessário arredondar para cima o valor calculado (função CEILING), pois caso @amplitude não seja múltiplo de @k (o que é o normal) ocorreria truncamento no resultado da divisão e a última classe poderia ficar incompleta.

O passo seguinte é gerar as classes através do seguinte trecho de código sql, que utiliza CTE recursiva para definir as classes:

-- código #2.1 parte 2
-- © José Diz / Porto SQL

...
CREATE TABLE #classe (
     i smallint, inicio money, fim money, freqabs int default 0);

declare @c int;
set @c= 1;

with gera_classe as (
SELECT @c as classe, @menor as inicio_classe,
       (@menor + @amplitude_classe) as fim_classe
union all
SELECT (C.classe +1), C.fim_classe,
       (@amplitude_classe + C.fim_classe)
  from gera_classe as C
  where C.classe < @k
)
INSERT into #classe (i, inicio, fim)
  SELECT classe, inicio_classe, fim_classe
    from gera_classe;
...

O trecho de código sql anterior calcula os limites inferior e superior de cada classe, utilizando a amplitude de classe para definir esses limites. Por exemplo, para calcular o intervalo da primeira classe utilizamos o menor valor – 1,5183 – como início do intervalo e somando-se a amplitude de classe obtemos o final do intervalo da primeira classe:

1,518.3 + 10.113,559 = 10.115,077.3

Para o cálculo do intervalo da classe seguinte utilizamos o limite superior da classe anterior – 10.115,077.3 – como limite inferior da classe atual e a seguir adicionamos a amplitude de classe para se ter o limite superior da classe:

10.115,077.3 + 10.113,559 = 20.228,636.3

Esse processo se repete até calcular o limite superior do intervalo da última classe. Temos ao final a seguinte situação:

p049_codigo #2.2

E para contabilizar a quantidade de casos de cada classe, o que nos fornece a frequência absoluta da classe, temos o seguinte trecho de código sql:

-- código #2.1 parte 3
-- © José Diz / Porto SQL

...
-- contabiliza quantidade de casos por classe
with calc_freq as (
SELECT C.inicio, count(*) as qtd
  from #ticketmedio as T
        cross join #classe as C
  where T.ticketmedio >= C.inicio  
        and (T.ticketmedio < C.fim or C.i = @k)
  group by C.inicio
)
UPDATE C
  set freqabs= X.qtd
  from #classe as C
       inner join calc_freq as X on X.inicio = C.inicio;
...

Após a execução do trecho de código sql anterior nosso esboço da tabela de distribuição de frequências fica assim:

p049_codigo #2.3

Agora é juntar as partes e finalizar o código sql, calculando as demais informações da distribuição de frequências:

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

--
IF Object_ID ('tempdb..#classe', 'U') is not null
  DROP TABLE #classe;
go

-- gera classes
declare @n int, @maior money, @menor money, @amplitude money,
        @k smallint, @amplitude_classe money;

CREATE TABLE #classe (
     i smallint, inicio money, fim money, freqabs int default 0);

SELECT @maior= max (ticketMedio),
       @menor= min (ticketMedio), @n= count(*)
  from #ticketmedio;

set @k= round (1 + 3.32 * log10 (@n), 0, 0);

set @amplitude= @maior - @menor;
set @amplitude_classe= ceiling ((@amplitude / @k) * 1000) / 1000;

-- acrescenta frequência absoluta
declare @c int;
set @c= 1;

with gera_classe as (
SELECT @c as classe, @menor as inicio_classe,
       (@menor + @amplitude_classe) as fim_classe
union all
SELECT (C.classe +1), C.fim_classe,
       (@amplitude_classe + C.fim_classe)
  from gera_classe as C
  where C.classe < @k
)
INSERT into #classe (i, inicio, fim)
  SELECT classe, inicio_classe, fim_classe
    from gera_classe;

-- contabiliza quantidade de casos por classe
with calc_freq as (
SELECT C.inicio, count(*) as qtd
  from #ticketmedio as T
       cross join #classe as C
  where T.ticketmedio >= C.inicio  
        and (T.ticketmedio < C.fim or C.i = @k)
  group by C.inicio
)
UPDATE C
  set freqabs= X.qtd
  from #classe as C
       inner join calc_freq as X on X.inicio = C.inicio;

-- monta distribuição de frequência tabular
SELECT i, inicio, fim,
       ((inicio + fim) / 2) as [Ponto médio],
       freqabs as [Freq. absoluta],
       cast ((1.0 * freqabs / @n) as decimal(9,5)) as [Freq. relativa],
       sum (freqabs) over (order by i) as [Freq.abs. acumulada],
       cast (sum ((1.0 * freqabs / @n)) over (order by i) as decimal(9,5)) as [Freq.rel. acumulada]
  from #classe
  order by i;

que retorna a seguinte tabela com a distribuição de frequências:

p049_codigo #2.4

Ao analisar a tabela percebe-se que a maioria dos dados estão concentrados na primeira classe: 18.825 casos de um total de 19.119 estão nessa classe; ou seja, cerca de 98% da amostra. Valores fora do padrão (outliers) distorceram a distribuição de frequências e isto será analisado adiante.

O código #2.1 utilizou a função de janela SUM() para calcular as frequências acumuladas, o que simplificou a programação.

Cenário série agrupada com classe simples. A implementação deste cenário é mais simples pois não é necessário calcular os intervalos, sendo as estatísticas agrupadas para cada valor de ticket médio. O código sql para geração da tabela temporária com a distribuição de frequências é o seguinte:

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

IF Object_ID ('tempdb..#distfreqvalor', 'U') is not null
  DROP TABLE #distfreqvalor;
go

with
-- calcula frequência absoluta
Passo1 as (
SELECT ticketMedio,
       count(*) as freqAbs
  from #ticketmedio
  group by ticketMedio
),
-- calcula frequências relativa e acumuladas
Passo2 as (
SELECT ticketMedio, freqAbs,
       sum (freqAbs) over (order by ticketMedio
                           rows between unbounded preceding and current row) as freqAbsAcum,
       count(*) over() as n
  from Passo1
)
-- gera tabela temporária com a distribuição de frequências
SELECT ticketMedio, freqAbs, freqAbsAcum,
       cast ((1.0 * freqAbs / n) as decimal(9,5)) as freqRel,
       cast (sum ((1.0 * freqAbs / n)) over (order by ticketMedio) as decimal(9,5)) as freqRelAcum
  into #distfreqvalor
  from Passo2;

Uma vez gerada a tabela temporária, para exibir o conteúdo dela é algo bem simples:

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

SELECT ticketMedio, freqAbs as [Freq. absoluta], 
       freqRel as [Freq. relativa],
       freqAbsAcum as [Freq.abs. acumulada], 
       freqRelAcum as [Freq.rel. acumulada]
  from #distfreqvalor
  order by ticketMedio;

que retorna o seguinte resultado (trecho inicial):

p049_codigo #2.3


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

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

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