Dans cette leçon, nous aborderons :
Introduction aux fonctions de fenêtrage
Syntaxe de base du fenêtrage
Les suspects habituels : SUM, COUNT, et AVG
ROW_NUMBER()
RANK() et DENSE_RANK()
NTILE
LAG et LEAD
Définir un alias de fenêtre
Techniques avancées de fenêtrage
Ce tutoriel utilise des données du programme Capital Bikeshare de Washington DC, qui publie des données historiques détaillées au niveau des trajets sur leur site web. Les données ont été téléchargées en février 2014, mais se limitent aux données collectées au cours du premier trimestre de 2012. Chaque ligne représente un trajet. La plupart des champs sont explicites, sauf type_utilisateur : "Inscrit" indique un abonnement mensuel au programme de partage de vélos, "Occasionnel" indique que l'utilisateur a acheté un pass de 3 jours. Les champs heure_debut et heure_fin ont été nettoyés de leur format d'origine pour correspondre au format de date SQL — ils sont stockés dans cette table sous forme de timestamps.
La documentation de PostgreSQL fait un excellent travail pour introduire le concept des fonctions de fenêtrage :
Une fonction de fenêtrage effectue un calcul sur un ensemble de lignes de la table qui sont liées d'une certaine manière à la ligne courante. Cela est comparable au type de calcul qui peut être fait avec une fonction d'agrégation. Mais contrairement aux fonctions d'agrégation classiques, l'utilisation d'une fonction de fenêtrage ne regroupe pas les lignes en une seule ligne de sortie — les lignes conservent leur identité séparée. En arrière-plan, la fonction de fenêtrage est capable d'accéder à plus que la ligne courante du résultat de la requête.
L'exemple le plus pratique de cela est un total cumulé :
SELECT duree_secondes,
SUM(duree_secondes) OVER (ORDER BY heure_debut) AS total_cumulatif
FROM tutoriel.vélos_partage_q1_2012Vous pouvez voir que la requête ci-dessus crée une agrégation (total_cumulatif) sans utiliser GROUP BY. Décomposons la syntaxe et voyons comment cela fonctionne.
La première partie de l'agrégation ci-dessus, SUM(duree_secondes), ressemble beaucoup à n'importe quelle autre agrégation. L'ajout de OVER la désigne comme une fonction de fenêtrage. Vous pouvez lire l'agrégation ci-dessus comme "prendre la somme de duree_secondes sur l'ensemble du résultat, ordonné par heure_debut."
Si vous souhaitez réduire la fenêtre de l'ensemble de données à des groupes individuels dans l'ensemble de données, vous pouvez utiliser PARTITION BY pour le faire :
La requête ci-dessus groupe et ordonne la requête par station_debut. Dans chaque valeur de station_debut, elle est ordonnée par heure_debut, et le total cumulatif somme la ligne courante et toutes les lignes précédentes de duree_secondes. Faites défiler jusqu'à ce que la valeur de station_debut change et vous remarquerez que total_cumulatif recommence. C'est ce qui se passe lorsque vous utilisez PARTITION BY. Si vous êtes encore perplexe par ORDER BY, il ordonne simplement par la ou les colonnes désignées de la même manière que la clause ORDER BY, sauf qu'il traite chaque partition comme séparée. Il crée également le total cumulatif — sans ORDER BY, chaque valeur sera simplement une somme de toutes les valeurs de duree_secondes dans sa station_debut respective. Essayez d'exécuter la requête ci-dessus sans ORDER BY pour vous en faire une idée :
SELECT station_debut,
duree_secondes,
SUM(duree_secondes) OVER
(PARTITION BY station_debut) AS total_station_debut
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'ORDER BY et PARTITION BY définissent ce que l'on appelle la "fenêtre" — le sous-ensemble ordonné de données sur lequel les calculs sont effectués.
Remarque : Vous ne pouvez pas utiliser les fonctions de fenêtrage et les agrégations standard dans la même requête. Plus précisément, vous ne pouvez pas inclure de fonctions de fenêtrage dans une clause GROUP BY.
SUM, COUNT, et AVGLorsque vous utilisez des fonctions de fenêtrage, vous pouvez appliquer les mêmes agrégats que vous utiliseriez dans des circonstances normales — SUM, COUNT, et AVG. La manière la plus simple de comprendre ces fonctions est de réexécuter l'exemple précédent avec quelques fonctions supplémentaires. Par exemple :
SELECT station_debut,
duree_secondes,
SUM(duree_secondes) OVER
(PARTITION BY station_debut) AS total_cumulatif,
COUNT(duree_secondes) OVER
(PARTITION BY station_debut) AS nombre_cumulatif,
AVG(duree_secondes) OVER
(PARTITION BY station_debut) AS moyenne_cumulative
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'Alternativement, les mêmes fonctions avec ORDER BY :
SELECT station_debut,
duree_secondes,
SUM(duree_secondes) OVER
(PARTITION BY station_debut ORDER BY heure_debut)
AS total_cumulatif,
COUNT(duree_secondes) OVER
(PARTITION BY station_debut ORDER BY heure_debut)
AS nombre_cumulatif,
AVG(duree_secondes) OVER
(PARTITION BY station_debut ORDER BY heure_debut)
AS moyenne_cumulative
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'Assurez-vous d'exécuter ces deux requêtes dans votre environnement SQL. Le prochain problème pratique est très similaire aux exemples, alors essayez de modifier le code ci-dessus plutôt que de partir de zéro.
ROW_NUMBER()ROW_NUMBER() fait exactement ce que son nom indique — il affiche le numéro d'une ligne donnée. Il commence à 1 et numérote les lignes selon la partie ORDER BY de l'instruction de fenêtrage. ROW_NUMBER() ne nécessite pas de spécifier une variable entre les parenthèses :
SELECT station_debut,
heure_debut,
duree_secondes,
ROW_NUMBER() OVER (ORDER BY heure_debut)
AS numero_ligne
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'L'utilisation de la clause PARTITION BY vous permettra de recommencer la numérotation à 1 dans chaque partition. La requête suivante recommence le comptage pour chaque station :
SELECT station_debut,
heure_debut,
duree_secondes,
ROW_NUMBER() OVER (PARTITION BY station_debut
ORDER BY heure_debut)
AS numero_ligne
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'RANK() et DENSE_RANK()RANK() est légèrement différent de ROW_NUMBER(). Si vous ordonnez par heure_debut, par exemple, il se peut que certaines stations aient des trajets avec deux heures de début identiques. Dans ce cas, ils reçoivent le même rang, alors que ROW_NUMBER() leur attribue des numéros différents. Dans la requête suivante, vous remarquez les 4ème et 5ème observations pour station_debut 31000 — elles reçoivent toutes deux un rang de 4, et le résultat suivant reçoit un rang de 6 :
SELECT station_debut,
duree_secondes,
RANK() OVER (PARTITION BY station_debut
ORDER BY heure_debut)
AS rang
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'Vous pouvez également utiliser DENSE_RANK() au lieu de RANK() selon votre application. Imaginez une situation dans laquelle trois entrées ont la même valeur. En utilisant l'une ou l'autre commande, elles recevront toutes le même rang. Pour les besoins de cet exemple, disons que c'est "2". Voici comment les deux commandes évalueraient les résultats suivants différemment :
RANK() donnerait aux lignes identiques un rang de 2, puis sauterait les rangs 3 et 4, donc le résultat suivant serait 5.
DENSE_RANK() donnerait toujours aux lignes identiques un rang de 2, mais la ligne suivante serait 3 — aucun rang ne serait sauté.
NTILEVous pouvez utiliser les fonctions de fenêtrage pour identifier dans quel percentile (ou quartile, ou toute autre subdivision) une ligne donnée se situe. La syntaxe est NTILE(nombre_de_tranches). Dans ce cas, ORDER BY détermine quelle colonne utiliser pour déterminer les quartiles (ou le nombre de "tranches" que vous spécifiez). Par exemple :
SELECT station_debut,
duree_secondes,
NTILE(4) OVER
(PARTITION BY station_debut ORDER BY duree_secondes)
AS quartile,
NTILE(5) OVER
(PARTITION BY station_debut ORDER BY duree_secondes)
AS quintile,
NTILE(100) OVER
(PARTITION BY station_debut ORDER BY duree_secondes)
AS percentile
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
ORDER BY station_debut, duree_secondesEn regardant les résultats de la requête ci-dessus, vous pouvez voir que la colonne percentile ne calcule pas exactement comme vous pourriez vous y attendre. Si vous n'aviez que deux enregistrements et que vous mesuriez les percentiles, vous vous attendriez à ce qu'un enregistrement définisse le 1er percentile, et l'autre enregistrement définisse le 100ème percentile. En utilisant la fonction NTILE, ce que vous verriez en réalité est un enregistrement dans le 1er percentile, et un dans le 2ème percentile. Vous pouvez voir cela dans les résultats pour station_debut 31000 — la colonne percentile ressemble simplement à un classement numérique. Si vous faites défiler jusqu'à station_debut 31007, vous pouvez voir qu'elle calcule correctement les percentiles car il y a plus de 100 enregistrements pour cette station. Si vous travaillez avec des fenêtres très petites, gardez cela à l'esprit et envisagez d'utiliser des quartiles ou des bandes similaires.
LAG et LEADIl se peut souvent qu'il soit utile de comparer des lignes à des lignes précédentes ou suivantes, surtout si vos données sont dans un ordre qui a du sens. Vous pouvez utiliser LAG ou LEAD pour créer des colonnes qui extraient des valeurs d'autres lignes — tout ce que vous avez à faire est de spécifier quelle colonne extraire et à combien de lignes de distance vous souhaitez effectuer l'extraction. LAG extrait des lignes précédentes et LEAD extrait des lignes suivantes :
SELECT station_debut,
duree_secondes,
LAG(duree_secondes, 1) OVER
(PARTITION BY station_debut ORDER BY duree_secondes) AS lag,
LEAD(duree_secondes, 1) OVER
(PARTITION BY station_debut ORDER BY duree_secondes) AS lead
FROM tutoriel.vélos_partage_q1_2012
WHERE heure_debut < '2012-01-08'
ORDER BY station_debut, duree_secondesCela est particulièrement utile si vous souhaitez calculer des différences entre les lignes :
La première ligne de la colonne difference est nulle car il n'y a pas de ligne précédente à partir de laquelle on va extraire. De même, l'utilisation de LEAD créera des valeurs nulles à la fin de l'ensemble de données. Si vous souhaitez rendre les résultats un peu plus propres, vous pouvez les encapsuler dans une sous-requête pour supprimer les valeurs nulles :
Si vous prévoyez d'écrire plusieurs fonctions de fenêtrage dans la même requête, en utilisant la même fenêtre, vous pouvez créer un alias. Prenons l'exemple NTILE ci-dessus :
Cela peut être réécrit comme suit :
La clause WINDOW, si elle est incluse, doit toujours venir après la clause WHERE.
Vous pouvez consulter une liste complète des fonctions de fenêtrage dans Postgres (la syntaxe utilisée par Mode) dans la documentation de Postgres. Si vous utilisez des fonctions de fenêtrage sur une base de données connectée, vous devriez consulter le guide de syntaxe approprié pour votre système.
Fonctions de fenêtrage : Elles permettent d'effectuer des calculs sur un ensemble de lignes liées à la ligne courante sans regrouper les résultats en une seule ligne.
Syntaxe de base : Utilisez OVER pour désigner une fonction de fenêtrage et PARTITION BY pour diviser les données en groupes.
Agrégats courants : SUM, COUNT, et AVG peuvent être utilisés avec des fonctions de fenêtrage.
Numérotation des lignes : ROW_NUMBER(), RANK(), et DENSE_RANK() permettent de numéroter les lignes selon un ordre spécifique.
Subdivision des données : NTILE permet de diviser les données en quartiles, quintiles, percentiles, etc.
Comparaison de lignes : LAG et LEAD permettent de comparer des lignes avec des lignes précédentes ou suivantes.
Alias de fenêtre : Utilisez WINDOW pour définir un alias de fenêtre et simplifier les requêtes avec plusieurs fonctions de fenêtrage.
Ce tutoriel vous a introduit aux concepts de base et avancés des fonctions de fenêtrage en SQL. Avec ces outils, vous pouvez effectuer des analyses de données plus complexes et obtenir des insights plus approfondis à partir de vos ensembles de données.
