Análise estatística SQL Parte 3: Medindo a dispersão da distribuição

Além de conhecer os centros de uma distribuição em seus dados, você precisa saber o quão variadas são as observações. Neste artigo, explicaremos como encontrar a propagação de uma distribuição em SQL.

Você está lidando com uma população muito uniforme ou muito espalhada? Para entender realmente o que os números estão dizendo, você precisa saber a resposta a esta pergunta.

Na segunda parte desta série, discutimos como calcular os centros de distribuição. Assim como no caso do centro, há várias maneiras de medir a propagação da distribuição em SQL. Além disso, há muitas definições diferentes para a propagação da distribuição. Vamos discutir as mais populares: a faixa, a faixa interquartílica, a média absoluta, o desvio médio quadrático, a variância, o desvio padrão e o coeficiente de variação. Explicarei também o termo graus de liberdade. Finalmente, vamos considerar a diferença entre variância e desvio padrão para as amostras e para as populações. Depois de passar por esta entrada, você poderá encontrar a propagação de uma distribuição em SQL por conta própria.

Gama

O alcance é a distância simples entre o valor máximo e o valor mínimo que a variável toma. (Uma variável é um atributo de uma observação, representada como uma coluna em uma tabela). É a medida mais simples de dispersão. A fórmula para o intervalo é:

R = vmax - vmin

As funções agregadas T_SQL MAX e MIN calculam o intervalo de uma variável, como mostrado abaixo:

USE AdventureWorksDW2014;
SELECT MAX(Age) - MIN(Age) AS Range
FROM dbo.vTargetMail;

O código gera a seguinte saída:

Range
-----
   70

Inter-Quartile Range

Vamos mergulhar no cálculo de quartis em SQL. A mediana é o valor que divide a distribuição em duas metades. Pode-se dividir mais a distribuição - por exemplo, pode-se dividir cada metade em duas metades. Isto cria quartis: três valores que dividem a distribuição em trimestres.

Vamos examinar este processo de divisão, a base para o cálculo de quartis em SQL. Você começa com linhas de ordenação (casos, observações) em uma coluna selecionada (atributo, variável). Você define a classificação como a posição absoluta de uma linha em sua seqüência de linhas ordenadas. A classificação percentual de um valor é uma medida relativa que informa qual porcentagem de todas as (n) observações tem um valor inferior a um valor selecionado.

Ao dividir as observações em quartos, você obtém três percentis (a 25%, 50%, e 75% de todas as linhas). Você pode ler os valores nos quartis. O primeiro quartil, no ponto 25%, é chamado de quartil inferior. O segundo quartil é a mediana (50%). O terceiro, no ponto 75%, é o quartil superior. Se você subtrair o quartil inferior (Q1) do quartil superior (Q3), você obtém a fórmula para o intervalo interquartílico (IQR):

IQR = Q3 - Q1

Calcular quartis em SQL e obter o IQR é tão simples quanto usar a função analítica PERCENTILE_CONT:

SELECT DISTINCT 
 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY 1.0*Age) OVER () -
 PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY 1.0*Age) OVER () AS IQR
FROM dbo.vTargetMail;

Esta consulta retorna o seguinte resultado:

IQR
---
 17

Como a mediana, o IQR é resistente a mudanças. Isto significa que ele não é sensível a um balanço selvagem em uma única observação. A resistência é lógica, pois são utilizadas apenas duas observações-chave. Quando você vê uma grande diferença entre o intervalo e o intervalo interquartílico da mesma variável, isto significa que alguns valores na distribuição estão bastante distantes do valor médio.

Desvio Médio Absoluto

Para o IQR, são utilizadas apenas duas observações-chave: o quartil inferior e o quartil superior. Existe alguma medida que leve em conta todas as observações? Sim.

Você pode medir a distância entre cada valor e o valor médio e chamar-lhe o desvio. A soma de todas as distâncias lhe dá uma medida de como sua população está distribuída. Mas você deve considerar que algumas das distâncias são positivas enquanto outras são negativas; na verdade, elas se cancelam mutuamente, então o total lhe dá exatamente zero.

O mesmo é verdade se você calcular a média dos desvios, então esta seria uma medida inútil de dispersão. Você resolve este problema ignorando os sinais positivos/negativos e usando os valores absolutos das distâncias entre os valores e a média.

Calculando a média dos desvios absolutos, você obtém a fórmula para o desvio médio absoluto (DMA):

Pela fórmula do DMA, você pode ver que primeiro precisa calcular a média. No início, é tentador tentar isto usando a função agregada do AVG e usando o resultado como uma entrada na função SUM. Entretanto, o SQL Server não pode realizar uma função agregada em uma expressão contendo um agregado ou uma subquisição; portanto, temos que armazenar o valor médio (de AVG) em uma variável:

DECLARE @mean AS NUMERIC(10,2);
SET @mean = (SELECT AVG(1.0*YearlyIncome) FROM dbo.vTargetMail);
SELECT SUM(ABS(YearlyIncome - @mean))/COUNT(*) AS MAD
FROM dbo.vTargetMail;

Você obtém o seguinte resultado:

MAD
------------
25474.966405

Desvio médio quadrático

Outra maneira de evitar os problemas de sinais de desvio para ajustar cada desvio. Com uma leve modificação da fórmula do DMA - especificamente, calculando a média dos desvios quadráticos ao invés dos desvios absolutos - obtém-se a fórmula para o desvio médio quadrático (DMA):

Você deve ter se perguntado por que eu não usei nenhuma função agregada de janela para o cálculo do MAD. É claro que isto é possível. Eu o farei para o cálculo do MSD. Vamos tentar com a seguinte pergunta!

SELECT 
 SUM( 
  SQUARE(YearlyIncome - 
   (AVG(1.0*YearlyIncome) OVER())
  )
 ) / COUNT(*) AS MSD
FROM dbo.vTargetMail;

Infelizmente, esta abordagem é bastante ingênua. A consulta retorna error 4109 - windowed functions cannot be used in the context of another windowed function or aggregate.

Você precisa usar a função de agregação de janela dentro de uma expressão de tabela comum, e depois fazer a agregação final em uma consulta externa. O seguinte trecho de código mostra como isso é feito:

WITH MSDCTE AS
(
SELECT 
  YearlyIncome, 
  AVG(1.0*YearlyIncome) OVER() AS Deviation
FROM dbo.vTargetMail
)
SELECT SUM(SQUARE(YearlyIncome - Deviation)) / COUNT(*) AS MSD
FROM MSDCTE;

Isto retorna o resultado desejado:

MSD
----------------
1042319181.07085

Graus de liberdade e desvio

Vamos supor que você tenha apenas uma observação (n=1). Esta observação também é a sua média amostral, mas não há nenhuma propagação. Você só pode calcular a dispersão se n exceder 1. Somente as (n-1) informações o ajudam a calcular o spread, considerando que a primeira observação é a sua média. Estes pedaços de informação são chamados graus de liberdade.

Pense em graus de liberdade como o número de pedaços de informação que podem variar. Por exemplo, imagine uma variável que pode tomar cinco estados discretos diferentes. Você só precisa calcular as freqüências de quatro estados para conhecer a distribuição da variável; a freqüência do último estado é determinada pelas freqüências dos primeiros quatro estados calculados. Elas não podem variar porque a porcentagem cumulativa de todos os estados deve ser igual a 100.

A soma de todos os desvios, sem ignorar os sinais positivos/negativos, é sempre zero. Por causa disso, a fórmula de variância usa desvios quadráticos. Existem apenas desvios (n-1) livres; o último é estritamente determinado pelos outros. A definição de variância (Var) é semelhante à definição do MSD; basta substituir o número de casos n pelos graus de liberdade (n-1):

Esta é a fórmula para a variação de uma amostra, que pode ser usada como um estimador da variação da população. Agora imagine que seus dados representam a população completa. Nesse caso, todas as observações contribuem igualmente para o cálculo da variância, e os graus de liberdade não fazem sentido. A variância de uma população (VarP) é definida, então, com a mesma fórmula que a MSD:

É claro, quando se tem uma amostra grande, a diferença entre Var e VarP é mínima.

O Transact-SQL inclui uma função agregada (a função VAR) que calcula a variância de uma amostra como um estimador. A função VARP calcula a variância da população. O uso de qualquer uma delas em uma consulta é muito simples. O exemplo a seguir calcula ambas as variâncias para a coluna "YearlyIncome". Também as compara de duas maneiras: dividindo-as, e dividindo o número de casos menos um pelo número de casos. Este último mostra que a diferença é apenas um resultado dos graus de liberdade usados no cálculo da variância da amostra como estimador para a variância da população:

SELECT VAR(1.0*YearlyIncome) AS SampleVariance,
  VARP(1.0*YearlyIncome) AS PopulationVariance,
  VARP(1.0*YearlyIncome) / VAR(1.0*YearlyIncome) AS SampleVsPopulation1,
  (1.0 * COUNT(*) - 1) / COUNT(*) AS SampleVsPopulation2
FROM dbo.vTargetMail;

A consulta retorna o seguinte resultado:

SampleVariance   PopulationVariance SampleVsPopulation1 SampleVsPopulation2
---------------- ------------------ ------------------- -------------------
1042375574.46912 1042319181.07081   0.999945899156027   0.999945899156

Desvio Padrão e Coeficiente de Variação

Para compensar por ter os desvios ao quadrado na fórmula da variância, pode-se tomar a raiz quadrada da variância. Esta é a definição do desvio padrão (σ):

Você pode usar esta fórmula para calcular o desvio padrão em SQL, tanto da população quanto de uma amostra - basta usar a variância apropriada na fórmula.

Suponha que tenhamos derivado as medidas absolutas de um spread. A interpretação é bastante evidente para uma única variável: quanto maiores forem os valores das medidas, mais espalhada é a variável nas observações. Mas as medidas absolutas não podem ser usadas para comparar a dispersão entre duas ou mais variáveis. Portanto, precisamos derivar medidas relativas.

Podemos derivar as medidas relativas da dispersão para qualquer uma das medidas absolutas mencionadas. Entretanto, faremos apenas a mais popular: o desvio padrão. A definição do desvio padrão relativo (também conhecido como coeficiente de variação, ou CV) é uma simples divisão do desvio padrão pelo valor médio:

O T-SQL inclui uma função agregada para calcular o desvio padrão em SQL para a população (STDEVP) e um para calcular o desvio padrão para uma amostra como estimador (STDEV). O cálculo do desvio padrão em SQL, assim como o coeficiente de variação, é, portanto, simples. A consulta a seguir calcula os desvios padrão para o "Idade" e "AnnualIncome" colunas e o coeficiente de variação para elas:

SELECT STDEV(1.0*Age) AS StDevAge,
 STDEV(1.0*YearlyIncome) AS StDevIncome,
 STDEV(1.0*Age) / AVG(1.0*Age) AS CVAge,
 STDEV(1.0*YearlyIncome) / AVG(1.0*YearlyIncome) AS CVIncome
FROM dbo.vTargetMail;

Aqui está o resultado:

StDevAge          StDevIncome       CVAge              CVIncome
----------------  ----------------  -----------------  -----------------
11.5178146121881  32285.8417029682  0.241654328044298	  0.563395923529214

Após o cálculo do desvio padrão em SQL, você pode ver que o desvio padrão para "AnnualIncome" é muito maior do que para "Idade"No entanto, a dispersão relativa, o coeficiente de variação, não é muito diferente.

Conclusão

Os centros de distribuição, especialmente o valor médio, são provavelmente as medidas mais abusadas nas estatísticas. A média não significa muito sem mencionar a propagação. Há várias medidas de dispersão: desvio padrão, variância e coeficiente de variação são as mais importantes. Como agora você sabe mais sobre medir a dispersão de uma distribuição em SQL e tópicos como calcular quartis em SQL ou escrever consultas para obter o desvio padrão em SQL não são estranhos a você, você levou sua análise estatística para o próximo nível!