Analisi statistica SQL Parte 3: Misurare la diffusione di una distribuzione

Oltre a conoscere i centri di una distribuzione nei dati, è necessario sapere quanto sono varie le osservazioni. In questo articolo vi spiegheremo come trovare lo spread di una distribuzione in SQL.

Avete a che fare con una popolazione molto uniforme o molto diffusa? Per capire veramente cosa dicono i numeri, è necessario conoscere la risposta a questa domanda.

Nella seconda parte di questa serie, abbiamo parlato di come calcolare i centri di distribuzione. Proprio come nel caso del centro, esistono diversi modi per misurare la diffusione della distribuzione in SQL. Inoltre, esistono molte definizioni diverse per la diffusione della distribuzione. Discuteremo le più diffuse: l'intervallo, l'intervallo interquartile, la media assoluta, lo scarto quadratico medio, la varianza, la deviazione standard e il coefficiente di variazione. Spiegherò anche il termine gradi di libertà. Infine, considereremo la differenza tra varianza e deviazione standard per i campioni e per le popolazioni. Dopo aver letto questo articolo, sarete in grado di trovare da soli lo spread di una distribuzione in SQL.

Intervallo

L'intervallo è la distanza semplice tra il valore massimo e il valore minimo che la variabile assume. (Una variabile è un attributo di un'osservazione, rappresentato come una colonna in una tabella). È la misura più semplice della diffusione. La formula dell'intervallo è:

R = vmax - vmin

Le funzioni aggregate T_SQL MAX e MIN calcolano l'intervallo di una variabile, come mostrato di seguito:

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

Il codice genera il seguente output:

Range
-----
   70

Intervallo inter-quartile

Passiamo al calcolo dei quartili in SQL. La mediana è il valore che divide la distribuzione in due metà. È possibile dividere la distribuzione in più parti, ad esempio dividere ogni metà in due metà. In questo modo si creano i quartili: tre valori che dividono la distribuzione in quarti.

Esaminiamo questo processo di divisione, la base per il calcolo dei quartili in SQL. Si inizia con l'ordinare le righe (casi, osservazioni) in base a una colonna selezionata (attributo, variabile). Si definisce il rango come la posizione assoluta di una riga nella sequenza di righe ordinate. Il rango percentile di un valore è una misura relativa che indica quale percentuale di tutte le (n) osservazioni ha un valore inferiore al valore selezionato.

Dividendo le osservazioni in quarti, si ottengono tre percentili (al 25%, 50% e 75% di tutte le righe). È possibile leggere i valori in corrispondenza dei quartili. Il primo quartile, al 25%, è chiamato quartile inferiore. Il secondo quartile è la mediana (50%). Il terzo, al 75%, è il quartile superiore. Sottraendo il quartile inferiore (Q1) dal quartile superiore (Q3), si ottiene la formula dell'intervallo inter-quartile (IQR):

IQR = Q3 - Q1

Per calcolare i quartili in SQL e ottenere l'IQR è sufficiente usare la funzione analitica 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;

Questa query restituisce il seguente risultato:

IQR
---
 17

Come la mediana, l'IQR è resistente alle variazioni. Ciò significa che non è sensibile alle oscillazioni di una singola osservazione. La resistenza è logica, perché si utilizzano solo due osservazioni chiave. Quando si nota una grande differenza tra l'intervallo e l'intervallo interquartile della stessa variabile, significa che alcuni valori della distribuzione sono molto lontani dal valore medio.

Deviazione assoluta media

Per l'IQR si utilizzano solo due osservazioni chiave: il quartile inferiore e quello superiore. Esiste una misura che tenga conto di tutte le osservazioni? Sì.

È possibile misurare la distanza tra ciascun valore e il valore medio e chiamarla deviazione. La somma di tutte le distanze dà una misura della dispersione della popolazione. Ma bisogna considerare che alcune distanze sono positive, mentre altre sono negative; in realtà, si annullano a vicenda, quindi il totale dà esattamente zero.

Lo stesso vale se si fa la media delle deviazioni, quindi questa sarebbe una misura inutile della diffusione. Il problema si risolve ignorando i segni positivi/negativi e utilizzando i valori assoluti delle distanze tra i valori e la media.

Calcolando la media delle deviazioni assolute, si ottiene la formula della deviazione assoluta media (MAD):

Dalla formula della MAD si evince che è necessario calcolare prima la media. In un primo momento, si è tentati di provare a farlo utilizzando la funzione aggregata AVG e utilizzando il risultato come input della funzione SUM. Tuttavia, SQL Server non può eseguire una funzione aggregata su un'espressione contenente un aggregato o una sottoquery; pertanto, è necessario memorizzare il valore medio (da AVG) in una variabile:

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;

Si ottiene il seguente risultato:

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

Scarto quadratico medio

Un altro modo per evitare i problemi dei segni di deviazione è quello di elevare al quadrato ogni deviazione. Con una leggera modifica della formula MAD, in particolare calcolando la media degli scarti al quadrato invece degli scarti assoluti, si ottiene la formula dello scarto quadratico medio (MSD):

Vi sarete chiesti perché non ho utilizzato alcuna funzione di aggregazione a finestra per il calcolo della MAD. Naturalmente, questo è possibile. Lo farò per il calcolo dell'MSD. Proviamo con la seguente query!

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

Purtroppo questo approccio è piuttosto ingenuo. La query restituisce error 4109 - windowed functions cannot be used in the context of another windowed function or aggregate.

È necessario utilizzare la funzione di aggregazione a finestra all'interno di un'espressione di tabella comune e poi eseguire l'aggregazione finale in una query esterna. Il seguente frammento di codice mostra come si fa:

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;

Il risultato è quello desiderato:

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

Gradi di libertà e varianza

Supponiamo di avere una sola osservazione (n=1). Questa osservazione è anche la media del campione, ma non c'è alcuno spread. È possibile calcolare la dispersione solo se n è superiore a 1. Solo le (n-1) informazioni aiutano a calcolare lo spread, considerando che la prima osservazione è la media. Queste informazioni sono chiamate gradi di libertà.

Si pensi ai gradi di libertà come al numero di informazioni che possono variare. Ad esempio, immaginiamo una variabile che può assumere cinque diversi stati discreti. Per conoscere la distribuzione della variabile è sufficiente calcolare le frequenze di quattro stati; la frequenza dell'ultimo stato è determinata dalle frequenze dei primi quattro stati calcolati. Esse non possono variare perché la percentuale cumulativa di tutti gli stati deve essere uguale a 100.

La somma di tutti gli scarti, senza ignorare i segni positivi/negativi, è sempre zero. Per questo motivo, la formula della varianza utilizza gli scarti al quadrato. Ci sono solo (n-1) deviazioni libere; l'ultima è strettamente determinata dalle altre. La definizione di varianza (Var) è simile a quella della DMS; basta sostituire il numero di casi n con i gradi di libertà (n-1):

Questa è la formula della varianza di un campione, che può essere utilizzata come stimatore della varianza della popolazione. Immaginate ora che i vostri dati rappresentino l'intera popolazione. In questo caso, tutte le osservazioni contribuiscono al calcolo della varianza in modo uguale e i gradi di libertà non hanno senso. La varianza di una popolazione (VarP) si definisce, quindi, con la stessa formula dell'MSD:

Naturalmente, quando si dispone di un campione di grandi dimensioni, la differenza tra Var e VarP è minima.

Transact-SQL include una funzione aggregata (la funzione VAR) che calcola la varianza di un campione come stimatore. La funzione VARP calcola la varianza della popolazione. L'utilizzo di una delle due funzioni in una query è molto semplice. L'esempio seguente calcola entrambe le varianze per la colonna "YearlyIncome". Le confronta inoltre in due modi: dividendole e dividendo il numero di casi meno uno per il numero di casi. Quest'ultima mostra che la differenza è solo il risultato dei gradi di libertà utilizzati nel calcolo della varianza del campione come stimatore della varianza della popolazione:

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;

L'interrogazione restituisce il seguente risultato:

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

Deviazione Standard e Coefficiente di Variazione

Per compensare il fatto che le deviazioni sono al quadrato nella formula della varianza, si può prendere la radice quadrata della varianza. Questa è la definizione di deviazione standard (σ):

È possibile utilizzare questa formula per calcolare la deviazione standard in SQL, sia della popolazione che di un campione - basta utilizzare la varianza appropriata nella formula.

Supponiamo di aver ricavato le misure assolute di uno spread. L'interpretazione è abbastanza evidente per una singola variabile: più grandi sono i valori delle misure, maggiore è la dispersione della variabile nelle osservazioni. Ma le misure assolute non possono essere utilizzate per confrontare lo spread tra due o più variabili. Pertanto, è necessario ricavare misure relative.

Possiamo ricavare le misure relative della dispersione per qualsiasi misura assoluta menzionata. Tuttavia, ci occuperemo solo di quella più popolare: la deviazione standard. La definizione di deviazione standard relativa (nota anche come coefficiente di variazione, o CV) è una semplice divisione della deviazione standard per il valore medio:

T-SQL include una funzione aggregata per calcolare la deviazione standard in SQL per la popolazione (STDEVP) e una per calcolare la deviazione standard di un campione come stimatore (STDEV). Il calcolo della deviazione standard in SQL e del coefficiente di variazione è quindi semplice. La seguente query calcola le deviazioni standard per i valori di "Età" e "Reddito annuo" e il coefficiente di variazione:

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;

Ecco il risultato:

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

Dopo aver calcolato la deviazione standard in SQL, si può notare che la deviazione standard di "Reddito annuo" è molto più alta di quella di "Età"tuttavia, lo spread relativo, il coefficiente di variazione, non è molto diverso.

Conclusione

I centri di distribuzione, in particolare il valore medio, sono probabilmente le misure più abusate in statistica. La media non ha molto significato senza menzionare lo spread. Esistono diverse misure della dispersione: la deviazione standard, la varianza e il coefficiente di variazione sono le più importanti. Ora che sapete di più sulla misurazione della dispersione di una distribuzione in SQL e che argomenti come il calcolo dei quartili in SQL o la scrittura di query per ottenere la deviazione standard in SQL non vi sono estranei, avete portato la vostra analisi statistica a un livello superiore!