En plus de connaître les centres d'une distribution dans vos données, vous devez savoir à quel point les observations sont variées. Dans cet article, nous allons vous expliquer comment trouver la dispersion d'une distribution en SQL.

Avez-vous affaire à une population très uniforme ou très étalée ? Pour vraiment comprendre ce que disent les chiffres, vous devez connaître la réponse à cette question.

Dans la deuxième partie de cette série, nous avons abordé les sujets suivants comment calculer les centres de distribution. Tout comme dans le cas du centre, il existe plusieurs façons de mesurer la dispersion de la distribution en SQL. De même, il existe de nombreuses définitions différentes de la dispersion de la distribution. Nous allons aborder les plus populaires : l'étendue, l'écart interquartile, la moyenne absolue, l' écart quadratique moyen, la variance, l'écart type et le coefficient de variation. J'expliquerai également le terme degrés de liberté. Enfin, nous examinerons la différence entre la variance et l'écart-type pour les échantillons et pour les populations. Après avoir parcouru cet article, vous serez capable de trouver la dispersion d'une distribution en SQL par vous-même.

Étendue

L'étendue est la simple distance entre la valeur maximale et la valeur minimale que prend la variable. (Une variable est un attribut d'une observation, représenté comme une colonne dans un tableau). Il s'agit de la mesure la plus simple de la dispersion. La formule de l'étendue est la suivante :

R = vmax - vmin

Les fonctions d'agrégation T_SQL MAX et MIN calculent l'étendue d'une variable, comme indiqué ci-dessous :

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

Le code génère la sortie suivante :

Range
-----
   70

Inter-Quartile Range

Plongeons dans le calcul des quartiles en SQL. La médiane est la valeur qui divise la distribution en deux moitiés. Vous pouvez diviser la distribution davantage, par exemple, vous pouvez diviser chaque moitié en deux moitiés. Cela crée des quartiles : trois valeurs qui divisent la distribution en quarts.

Examinons ce processus de division, qui constitue la base du calcul des quartiles en SQL. Vous commencez par trier les rangées (cas, observations) sur une colonne sélectionnée (attribut, variable). Vous définissez le rang comme la position absolue d'une ligne dans votre séquence de lignes triées. Le rang centile d'une valeur est une mesure relative qui vous indique quel pourcentage de toutes les (n) observations a une valeur inférieure à une valeur sélectionnée.

En divisant les observations en quarts, vous obtenez trois percentiles (à 25%, 50% et 75% de toutes les rangées). Vous pouvez lire les valeurs aux quartiles. Le premier quartile, situé à 25 %, est appelé quartile inférieur. Le deuxième quartile est la médiane (50 %). Le troisième, à 75 %, est le quartile supérieur. Si vous soustrayez le quartile inférieur (Q1) du quartile supérieur (Q3), vous obtenez la formule de l'écart interquartile (IQR) :

IQR = Q3 - Q1

Pour calculer les quartiles en SQL et obtenir l'IQR, il suffit d'utiliser la fonction analytique 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;

Cette requête renvoie le résultat suivant :

IQR
---
 17

Comme la médiane, l'IQR est résistant au changement. Cela signifie qu'il n'est pas sensible à une variation brutale d'une seule observation. Cette résistance est logique, car vous n'utilisez que deux observations clés. Lorsque vous constatez une grande différence entre l'étendue et l'écart interquartile d'une même variable, cela signifie que certaines valeurs de la distribution sont assez éloignées de la valeur moyenne.

Écart absolu de la moyenne

Pour l'IQR, vous n'utilisez que deux observations clés : le quartile inférieur et le quartile supérieur. Existe-t-il une mesure qui prendrait en compte toutes les observations ? Oui.

Vous pouvez mesurer la distance entre chaque valeur et la valeur moyenne et l'appeler l'écart. La somme de toutes les distances vous donne une mesure de la dispersion de votre population. Mais vous devez tenir compte du fait que certaines distances sont positives alors que d'autres sont négatives ; en fait, elles s'annulent mutuellement, de sorte que le total vous donne exactement zéro.

Il en va de même si vous faites la moyenne des écarts, ce qui constitue une mesure inutile de la dispersion. Vous résolvez ce problème en ignorant les signes positifs/négatifs et en utilisant les valeurs absolues des distances entre les valeurs et la moyenne.

En calculant la moyenne des écarts absolus, vous obtenez la formule de l'écart absolu moyen (MAD) :

À partir de la formule de l'écart absolu moyen, vous pouvez voir que vous devez d'abord calculer la moyenne. Au début, il est tentant d'essayer d'utiliser la fonction d'agrégation AVG et d'utiliser le résultat comme entrée dans la fonction SUM. Cependant, le serveur SQL ne peut pas exécuter une fonction d'agrégation sur une expression contenant un agrégat ou une sous-requête ; par conséquent, nous devons stocker la valeur moyenne (de AVG) dans une variable :

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;

Vous obtenez la sortie suivante :

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

Écart quadratique moyen

Une autre façon d'éviter les problèmes de signes de déviation consiste à élever au carré chaque déviation. En modifiant légèrement la formule MAD - plus précisément en calculant la moyenne des écarts au carré au lieu des écarts absolus - on obtient la formule de l'écart quadratique moyen (MSD) :

Vous vous êtes peut-être demandé pourquoi je n'ai pas utilisé de fonctions d'agrégation de fenêtres pour le calcul de l'écart quadratique moyen. Bien sûr, c'est possible. Je vais le faire pour le calcul du MSD. Essayons avec la requête suivante !

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

Malheureusement, cette approche est assez naïve. La requête renvoie error 4109 - windowed functions cannot be used in the context of another windowed function or aggregate.

Vous devez utiliser la fonction d'agrégation de la fenêtre dans une expression de table commune, puis effectuer l'agrégation finale dans une requête externe. L'extrait de code suivant montre comment procéder :

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;

Cela renvoie le résultat souhaité :

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

Degrés de liberté et variance

Supposons que vous n'ayez qu'une seule observation (n=1). Cette observation est également la moyenne de votre échantillon, mais il n'y a pas d'écart du tout. Vous ne pouvez calculer l'écart que si n est supérieur à 1. Seuls les (n-1) éléments d'information vous aident à calculer la dispersion, en considérant que la première observation est votre moyenne. Ces éléments d'information sont appelés degrés de liberté.

Considérez les degrés de liberté comme le nombre d'éléments d'information qui peuvent varier. Par exemple, imaginez une variable qui peut prendre cinq états discrets différents. Vous n'avez besoin de calculer que les fréquences de quatre états pour connaître la distribution de la variable ; la fréquence du dernier état est déterminée par les fréquences des quatre premiers états calculés. Elles ne peuvent pas varier car le pourcentage cumulé de tous les états doit être égal à 100.

La somme de tous les écarts, sans tenir compte des signes positifs/négatifs, est toujours égale à zéro. Pour cette raison, la formule de la variance utilise les écarts au carré. Il n'y a que (n-1) écarts libres ; le dernier est strictement déterminé par les autres. La définition de la variance (Var) est similaire à celle de la DMS ; vous remplacez simplement le nombre de cas n par les degrés de liberté (n-1) :

C'est la formule de la variance d'un échantillon, qui peut être utilisée comme un estimateur de la variance de la population. Imaginez maintenant que vos données représentent la population complète. Dans ce cas, toutes les observations contribuent de manière égale au calcul de la variance, et les degrés de liberté n'ont aucun sens. La variance d'une population (VarP) est alors définie avec la même formule que le TMS :

Bien sûr, lorsque vous avez un grand échantillon, la différence entre Var et VarP est minime.

Transact-SQL comprend une fonction d'agrégation (la fonction VAR) qui calcule la variance d'un échantillon comme estimateur. La fonction VARP calcule la variance de la population. L'utilisation de l'une ou l'autre dans une requête est très simple. L'exemple suivant calcule les deux variances pour la colonne "YearlyIncome". Il les compare également de deux manières : en les divisant, et en divisant le nombre de cas moins un par le nombre de cas. Cette dernière montre que la différence n'est que le résultat des degrés de liberté utilisés dans le calcul de la variance de l'échantillon comme estimateur de la variance de la population :

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;

La requête renvoie le résultat suivant :

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

Écart-type et coefficient de variation

Pour compenser le fait que les déviations sont élevées au carré dans la formule de la variance, vous pouvez prendre la racine carrée de la variance. C'est la définition de l'écart-type (σ) :

Vous pouvez utiliser cette formule pour calculer l'écart-type dans SQL, à la fois de la population et d'un échantillon - il suffit d'utiliser la variance appropriée dans la formule.

Supposons que nous dérivions les mesures absolues d'un écart. L'interprétation est assez évidente pour une variable unique : plus les valeurs des mesures sont grandes, plus la variable est étalée dans les observations. Mais les mesures absolues ne peuvent pas être utilisées pour comparer l'écart entre deux ou plusieurs variables. Par conséquent, nous devons dériver des mesures relatives.

Nous pouvons dériver les mesures relatives de l'écart pour n'importe laquelle des mesures absolues mentionnées. Cependant, nous ne ferons que la plus populaire : l'écart-type. La définition de l écart-type relatif (également connu sous le nom de coefficient de variation, ou CV) est une simple division de l'écart-type par la valeur moyenne :

T-SQL inclut une fonction d'agrégation permettant de calculer l'écart type en SQL pour la population (STDEVP) et une autre pour calculer l'écart type d'un échantillon en tant qu'estimateur (STDEV). Le calcul de l'écart-type en SQL ainsi que du coefficient de variation est donc simple. La requête suivante calcule les écarts-types pour les données suivantes "Age" et "Revenu annuel" et le coefficient de variation pour celles-ci :

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;

Voici le résultat :

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

Après avoir calculé l'écart type en SQL, vous pouvez voir que l'écart type pour "Revenu annuel" est beaucoup plus élevé que pour "Age"Cependant, l'écart relatif, le coefficient de variation, n'est pas si différent.

Conclusion

Les centres de distribution, en particulier la valeur moyenne, sont probablement les mesures les plus malmenées en statistique. La moyenne ne signifie pas grand-chose si l'on ne mentionne pas la dispersion. Il existe plusieurs mesures de la dispersion : l'écart-type, la variance et le coefficient de variation sont les plus importants. Comme maintenant vous en savez plus sur la mesure de la dispersion d'une distribution en SQL et que des sujets tels que le calcul des quartiles en SQL ou l'écriture de requêtes pour obtenir l'écart-type en SQL ne vous sont pas étrangers, vous avez fait passer votre analyse statistique au niveau supérieur !