Analyse statistique SQL Partie 1 : Calcul des fréquences et des histogrammes

Les développeurs de bases de données et de Business Intelligence (BI) créent quotidiennement un grand nombre de rapports, et les analyses de données en font partie intégrante. Si vous vous demandez si vous pouvez effectuer des analyses statistiques en SQL, la réponse est "oui". Lisez mon article pour apprendre à le faire !

Les statistiques sont très utiles en tant qu'étape initiale d'une analyse plus approfondie, c'est-à-dire pour l'aperçu des données et l'évaluation de leur qualité. Cependant, les possibilités d'analyse statistique en SQL sont quelque peu limitées car il n'existe pas beaucoup de fonctions statistiques dans SQL Server. . En outre, une bonne compréhension des statistiques n'est pas très courante parmi les praticiens T-SQL. Dans SQL Server 2016, vous pouvez utiliser R pour calculer toutes sortes de mesures statistiques, mais de nombreux développeurs et administrateurs de bases de données SQL Server ne programment pas en R. Et tous les sites ne sont pas passés à SQL Server 2016.

Cette série expliquera les bases de l'analyse statistique SQL. Le code utilisé est basé sur mes expériences réelles. Je m'occupe de projets de BI, notamment de data mining, et je dois souvent créer de nombreuses requêtes statistiques dans les phases initiales d'un projet. Pendant ces projets, le seul logiciel sur lequel je peux compter est souvent un SGBDR.

Optimisation des requêtes statistiques SQL

L'optimisation des requêtes statistiques est différente de l'optimisation des requêtes transactionnelles. Afin de calculer les statistiques, la requête scanne généralement toutes les données. Si la requête est trop lente, vous pouvez préparer un échantillon aléatoire de vos données et l'analyser. Cependant, si les requêtes suivent aveuglément les formules, elles effectuent souvent plusieurs analyses des données. Optimiser de telles requêtes signifie minimiser le nombre de balayages. Pour y parvenir, vous devez développer un algorithme qui utilise des mathématiques supplémentaires pour convertir les formules en équivalents qui peuvent être mieux optimisés dans SQL Server ou tout autre SGBDR. Vous devez également comprendre SQL en profondeur. Par exemple, vous devez avoir une très bonne compréhension des fonctions et des calculs de la fenêtre SQL.

En plus d'expliquer les statistiques et les requêtes statistiques, cette série vous donnera également des idées pour optimiser les requêtes statistiques et non statistiques.

Préparation de vos données pour l'analyse statistique SQL

Avant de commencer l'analyse, vous devez comprendre ce que vous analysez. En statistiques, vous analysez des cas en utilisant leurs variables. Dans la terminologie du SGBDR, vous pouvez considérer un cas comme une ligne de table et une variable comme une colonne de la même table. Pour la plupart des analyses statistiques, vous préparez une seule table ou vue. Parfois, il n'est pas si facile de définir exactement votre cas. Par exemple, si vous effectuez une analyse du risque de crédit, vous pouvez définir une famille comme un cas plutôt qu'un seul client.

Lorsque vous préparez des données pour une analyse statistique SQL, vous devez transformer les données sources en conséquence. Pour chaque cas, vous devez encapsuler toutes les informations disponibles dans les colonnes de la table que vous allez analyser.

Variables continues et discrètes

Avant de commencer un examen sérieux des données, vous devez comprendre comment les valeurs des données sont mesurées dans votre ensemble de données. Vous devrez peut-être vérifier cela auprès d'un expert en la matière et analyser le système d'entreprise qui est la source de vos données. Il existe plusieurs façons de mesurer les valeurs des données et différents types de colonnes :

• Variables discrètes Les variables continues ne peuvent prendre une valeur que dans un domaine limité de valeurs possibles. Les valeurs discrètes comprennent les variables catégoriques ou nominales qui n'ont pas d'ordre naturel. Les états, les codes d'état et les couleurs en sont des exemples.
  • Lesrangs peuvent également prendre une valeur uniquement à partir d'un ensemble discret de valeurs. Ils ont un ordre mais ne permettent aucune arithmétique. Les exemples incluent les rangs d'opinion et les valeurs numériques réelles binées (groupées, discrétisées).
  • Il existe également certains types spécifiques de variables catégorielles. Les variables à valeur unique ou constantes ne sont pas très intéressantes pour l'analyse car elles n'apportent aucune information. Les variables à deux valeurs ou dichotomiques ont deux valeurs qui sont minimalement nécessaires pour toute analyse. Les variables binaires sont des variables dichotomiques spécifiques qui prennent uniquement les valeurs 0 et 1.
• Variables continues peut prendre n'importe laquelle d'un nombre illimité de valeurs possibles ; cependant, le domaine lui-même peut avoir une limite inférieure et/ou supérieure.
  • Lesintervalles ont une ou deux limites, ont un ordre et permettent certaines soustractions de type arithmétique (mais ne permettent pas toujours la sommation). Les exemples incluent les dates, les heures et les températures.
  • Lesvariables numériques vraies permettent toutes les opérations arithmétiques. Les exemples incluent les montants et les valeurs.
  • Lesvariables monotones sont un type spécifique de variables continues qui augmentent de façon monotone sans limite. Si elles sont simplement des identifiants, elles peuvent ne pas être intéressantes. Elles peuvent néanmoins être transformées (classées en catégories) si l'identifiant qui ne cesse de croître contient des informations sur l'ordre chronologique (les identifiants inférieurs sont plus anciens que les identifiants supérieurs).

Données utilisées pour les analyses statistiques SQL

Pour cet article et tous les articles à venir, j'utilise la base de données de démonstration AdventureWorksDW2014. Vous pouvez télécharger une sauvegarde complète de cette base de données depuis le site d'exemples de Microsoft SQL Server. J'exécute l'ensemble du code sur SQL Server 2016 Developer Edition.

Je préfère utiliser la base de données exemple AdventureWorks pour SQL Server 2014 plutôt que la base de données exemple WideWorldImportersDW de SQL Server 2016. La base de données WideWorldImporters est très utile pour démontrer les nouvelles fonctionnalités de SQL Server 2016, mais ses données ne présentent pas les corrélations et les associations nécessaires à l'analyse statistique.

Utilisation de la distribution de fréquences en SQL pour comprendre les variables discrètes

En SQL, la distribution de fréquences (généralement présentée sous la forme d'un tableau) est utilisée pour obtenir un aperçu rapide des variables discrètes. Elle peut montrer les valeurs réelles ainsi que leurs :

• Fréquence absolue
• Pourcentage absolu
• Fréquence cumulée
• Pourcentage cumulé

De plus, la distribution de fréquence SQL affiche un histogramme du pourcentage absolu des valeurs.

Ci-dessous, je vais vous montrer plusieurs façons de calculer la distribution de fréquence en SQL, en commençant par une méthode assez inefficace.

Distribution de fréquence en SQL sans utiliser Fonctions de fenêtrage

Le calcul de la fréquence absolue et du pourcentage absolu des valeurs est une agrégation simple. Cependant, le calcul de la fréquence cumulée et du pourcentage cumulé implique de calculer des totaux courants. Avant que SQL Server 2012 ne prenne en charge les fonctions d'agrégation par fenêtre, vous deviez utiliser des sous-requêtes corrélées ou des jointures non égales pour cette tâche. Aucune de ces méthodes n'est très efficace.

Exécutez le code suivant, qui utilise des sous-requêtes corrélées, pour analyser en SQL la distribution de fréquence de la variable NumberCarsOwned de la vue dbo.vTargetMaildans la base de données de démonstration AdventureWorksDW2014.

USE AdventureWorksDW2014;
GO
WITH freqCTE AS
(
SELECT v.NumberCarsOwned,
 COUNT(v.NumberCarsOwned) AS AbsFreq,
 CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) /
       (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc
FROM dbo.vTargetMail AS v
GROUP BY v.NumberCarsOwned
)
SELECT c1.NumberCarsOwned AS NCars,
 c1.AbsFreq,
 (SELECT SUM(c2.AbsFreq)
  FROM freqCTE AS c2
  WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumFreq,
 c1.AbsPerc,
 (SELECT SUM(c2.AbsPerc)
  FROM freqCTE AS c2
  WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumPerc,
 CAST(REPLICATE('*',c1.AbsPerc) AS varchar(100)) AS Histogram
  FROM freqCTE AS c1
ORDER BY c1.NumberCarsOwned;

Ceci génère le résultat suivant :

NCars  AbsFreq  CumFreq  AbsPerc  CumPerc  Histogram
-----  -------  -------  -------  -------  -----------------------------------
0         4238     4238       23       23  ***********************
1         4883     9121       26       49  **************************
2         6457    15578       35       84  ***********************************
3         1645    17223        9       93  *********
4         1261    18484        7      100  *******

Distribution de fréquence en SQL à l'aide de Fonctions de fenêtrage - Solution 1

Lorsque vous effectuez une analyse statistique en SQL, les fonctions d'agrégation de fenêtre s'avèrent utiles. Elles offrent une bien meilleure solution. Comme nous l'avons déjà indiqué, ces fonctions sont disponibles dans les versions 2012 et ultérieures de SQL Server.

Si vous examinez la première partie de la requête, vous remarquerez que la requête d'expression de table commune qui calcule les nombres absolus est la même que dans la requête précédente. Toutefois, les valeurs cumulatives - les totaux courants - sont calculées à l'aide des fonctions d'agrégation de la fenêtre.

WITH freqCTE AS
(
SELECT v.NumberCarsOwned,
 COUNT(v.NumberCarsOwned) AS AbsFreq,
 CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) /
       (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc
FROM dbo.vTargetMail AS v
GROUP BY v.NumberCarsOwned
)
SELECT NumberCarsOwned,
 AbsFreq,
 SUM(AbsFreq) 
  OVER(ORDER BY NumberCarsOwned 
       ROWS BETWEEN UNBOUNDED PRECEDING
	    AND CURRENT ROW) AS CumFreq,
 AbsPerc,
 SUM(AbsPerc)
  OVER(ORDER BY NumberCarsOwned
       ROWS BETWEEN UNBOUNDED PRECEDING
	    AND CURRENT ROW) AS CumPerc,
 CAST(REPLICATE('*',AbsPerc) AS VARCHAR(50)) AS Histogram
FROM freqCTE
ORDER BY NumberCarsOwned;

Le résultat de cette requête est le même que celui de la requête précédente.

Distribution de fréquences en SQL à l'aide de Fonctions de fenêtrage - Solution 2

J'ai trouvé une autre solution intéressante en utilisant les fonctions analytiques de fenêtre de SQL. La fonction CUME_DIST calcule la distribution cumulative, ou la position relative, d'une valeur dans un groupe de valeurs. Pour une ligne r, en supposant un ordre croissant, le CUME_DIST de r est le nombre de lignes dont la valeur est inférieure ou égale à la valeur de r, divisé par le nombre de lignes évaluées dans la partition ou le jeu de résultats de la requête. La fonction PERCENT_RANK calcule le rang relatif d'une ligne dans un groupe de lignes. Nous pouvons utiliser PERCENT_RANK pour évaluer le rang relatif d'une valeur dans un ensemble de résultats de requête ou une partition.

La requête d'analyse statistique SQL suivante calcule le numéro de ligne une fois partitionné sur la colonne NumberCarsOwned et le numéro de ligne une fois sur l'ensemble de l'entrée. Elle calcule également le rang en pourcentage et la distribution cumulative sur l'ensemble des données d'entrée.

SELECT NumberCarsOwned AS NCars,
 ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq,
 ROW_NUMBER() OVER(
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq,
 PERCENT_RANK()
  OVER(ORDER BY NumberCarsOwned) AS Pr_AbsPerc, 
 CUME_DIST()
  OVER(ORDER BY NumberCarsOwned, CustomerKey) AS Cd_CumPerc
FROM dbo.vTargetMail;

La sortie partielle, qui ne montre que les lignes pertinentes pour l'explication de l'algorithme de calcul des fréquences, est la suivante :

NCars  Rn_AbsFreq  Rn_CumFre  Pr_AbsPerc         Cd_CumPerc
-----  ----------  ---------  -----------------  --------------------
0               1          1                 0   5.4100843973166E-05
0               2          2                 0   0.000108201687946332
…               …          …                 …   …
0            4238       4238                 0   0.229279376758277
1               1       4239  0.22929178163718   0.229333477602251
…               …          …                 …   …
1            4883       9121  0.22929178163718   0.493453797879247
2               1       9122  0.493480495590543  0.49350789872322
…               …          …                 …   …

Comme vous pouvez le constater, le dernier numéro de ligne partitionné par NumberCarsOwned dans une catégorie représente en fait la fréquence absolue des valeurs de cette catégorie. Le dernier numéro de ligne non partitionné dans une catégorie représente la fréquence cumulée jusqu'à et y compris la catégorie actuelle. Par exemple, la fréquence absolue pour NumberCarsOwned = "0" est de 4 238 et la fréquence cumulée est de 4 238 ; pour NumberCarsOwned = "1", la fréquence absolue est de 4 883 et la fréquence cumulée est de 9 121.

Considérons ensuite la fonction CUME_DIST (la colonne Cd_CumPerc dans la sortie). CUME_DIST dans la dernière ligne d'une catégorie renvoie le pourcentage cumulé jusqu'à et y compris la catégorie. Si vous soustrayez le PERCENT_RANK (la colonne Pr_AbsPerc de l'édition) de la dernière ligne de la catégorie au CUME_DIST de la dernière ligne de la même catégorie, vous obtenez le pourcentage absolu de la catégorie. Par exemple, le pourcentage absolu pour la catégorie où NumberCarsOwned = "1 " est supérieur à 26 % (0,493453797879247 - 0,22929178163718 = 0,264162016242067).

La requête suivante calcule la distribution des fréquences en utilisant les observations des résultats de la requête précédente.

WITH freqCTE AS
(
SELECT NumberCarsOwned,
 ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq,
 ROW_NUMBER() OVER(
  ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq,
 ROUND(100 * PERCENT_RANK()
  OVER(ORDER BY NumberCarsOwned), 0) AS Pr_AbsPerc, 
 ROUND(100 * CUME_DIST()
  OVER(ORDER BY NumberCarsOwned, CustomerKey), 0) AS Cd_CumPerc
FROM dbo.vTargetMail
)
SELECT NumberCarsOwned AS NCars,
 MAX(Rn_AbsFreq) AS AbsFreq,
 MAX(Rn_CumFreq) AS CumFreq,
 MAX(Cd_CumPerc) - MAX(Pr_Absperc) AS AbsPerc,
 MAX(Cd_CumPerc) AS CumPerc,
 CAST(REPLICATE('*',MAX(Cd_CumPerc) - MAX(Pr_Absperc)) AS varchar(100)) AS Histogram
FROM freqCTE
GROUP BY NumberCarsOwned
ORDER BY NumberCarsOwned;

Bien que l'idée de cette dernière requête soit très intéressante, cette requête n'est pas aussi efficace que la seconde (utilisant la fonction d'agrégation par fenêtre). Par conséquent, la deuxième solution est celle qui est recommandée.

Conclusion

Dans cet article, vous avez appris à calculer la distribution de fréquence SQL pour des variables discrètes. Vous avez également vu une solution qui fait appel à une certaine créativité. Dans les articles suivants, vous découvrirez d'autres méthodes d'analyse statistique SQL. Le prochain article sera consacré au calcul des mesures statistiques de base pour les variables continues. Vous verrez également comment écrire des requêtes efficaces qui font appel à des connaissances mathématiques plutôt qu'à de la créativité.