Fonctions de la fenêtre SQL

Vous commencez ici? Cette leçon fait partie d’un didacticiel complet sur l’utilisation de SQL pour l’analyse de données. Vérifiez le début.

Dans cette leçon, nous allons couvrir:

• Introduction aux fonctions de fenêtre
• Syntaxe de base du fenêtrage
• Les suspects habituels: SUM, COUNT et AVG
• ROW_NUMBER ()
• RANK () et DENSE_RANK ()
• NTILE
• LAG et LEAD
• Définition d’un alias de fenêtre
• Techniques de fenêtrage avancées

Cette leçon utilise les données du programme Capital Bikeshare de Washington DC, qui publie des données historiques détaillées au niveau du voyage 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 rider_type: « Registered » indique un abonnement mensuel au programme de covoiturage, « Casual » indique que le passager a acheté un pass 3 jours. Les champs start_time et end_time ont été nettoyés de leur forme d’origine pour s’adapter au format de date SQL. Ils sont stockés dans cette table sous forme d’horodatages.

Introduction aux fonctions de fenêtre

La documentation de PostgreSQL « fait un excellent travail en introduisant le concept des fonctions de fenêtre:

Une fonction de fenêtre effectue un calcul sur un ensemble de lignes de tableau qui sont en quelque sorte liées à la ligne en cours. Ceci est comparable au type de calcul qui peut être effectué avec une fonction d’agrégation. Mais contrairement aux fonctions d’agrégation classiques, l’utilisation d’une fenêtre n’entraîne pas le regroupement des lignes en une seule ligne de sortie – les lignes conservent leurs identités distinctes. En arrière-plan, la fonction window peut accéder à plus que la ligne actuelle du résultat de la requête.

L’exemple le plus pratique de ceci est un total cumulé:

Vous pouvez voir que la requête ci-dessus crée une agrégation (running_total) sans utiliser GROUP BY. Décomposons la syntaxe et voyons comment cela fonctionne.

Syntaxe de fenêtrage de base

La première partie de l’agrégation ci-dessus, SUM(duration_seconds) , ressemble beaucoup à n’importe quelle autre agrégation. L’ajout de OVER la désigne comme une fonction de fenêtre. Vous pouvez lire l’agrégation ci-dessus comme « prenez la somme de duration_seconds sur l’ensemble de l’ensemble de résultats, dans l’ordre par start_time. « 

Si vous » souhaitez réduire la fenêtre de l’ensemble de données à des groupes individuels au sein l’ensemble de données, vous pouvez utiliser PARTITION BY pour ce faire:

La requête ci-dessus regroupe et classe la requête par start_terminal . Dans chaque valeur de start_terminal, elle est triée par start_time, et les totaux cumulés sur la ligne actuelle et toutes les lignes précédentes de duration_seconds. Faites défiler vers le bas jusqu’à ce que la valeur start_terminal change et vous remarquerez que running_total recommence. C’est ce qui se passe lorsque vous groupez en utilisant PARTITION BY. Au cas où vous « seriez toujours perplexe par ORDER BY, il commande simplement par la colonne désignée (s) de la même manière que le ferait la clause ORDER BY, sauf qu’elle traite chaque partition comme distincte. Il crée également le total cumulé. Sans ORDER BY, chaque valeur sera simplement une somme de toutes les valeurs de duration_seconds dans son start_terminal. Essayez d’exécuter la requête ci-dessus sans ORDER BY pour vous faire une idée:

Les ORDER et PARTITION définir 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être et les agrégations standard dans le même requête. Plus précisément, vous ne pouvez pas « inclure de fonctions de fenêtre dans une clause GROUP BY.

Les suspects habituels: SUM, COUNT et AVG

Lorsque vous utilisez des fonctions de fenêtre, vous pouvez appliquer les mêmes agrégats que vous le feriez dans des circonstances normales – SUM, COUNT et AVG. Le moyen le plus simple de les comprendre est de réexécuter l’exemple précédent avec quelques fonctions supplémentaires. Faites

Alternativement, les mêmes fonctions avec ORDER BY:

Assurez-vous de brancher ces deux requêtes précédentes en Mode et de les exécuter. Ce problème pratique suivant est très similaire aux exemples, essayez donc de modifier le code ci-dessus plutôt que de partir de zéro.

ROW_NUMBER ()

ROW_NUMBER() fait exactement ce que cela donne: affiche le numéro d’une ligne donnée. Il commence par 1 et numérote les lignes en fonction de la partie ORDER BY de l’instruction window. ROW_NUMBER() ne vous oblige pas à spécifier une variable entre parenthèses:

L’utilisation de la clause PARTITION BY vous permettra de recommencez à compter 1 dans chaque partition. La requête suivante recommence le décompte pour chaque terminal:

RANK () et DENSE_RANK ()

RANK() est légèrement différent de ROW_NUMBER(). Si vous commandez par start_time, par exemple, il se peut que certains terminaux aient des trajets avec deux heures de départ identiques. Dans ce cas, on leur donne le même rang, alors que ROW_NUMBER() leur donne des nombres différents. Dans la requête suivante, vous remarquez les 4e et 5e observations pour start_terminal 31000 – elles ont toutes deux un rang de 4 et le résultat suivant reçoit un rang de 6:

Vous pouvez également utiliser DENSE_RANK() au lieu de RANK() en fonction de votre application. Imaginez une situation dans laquelle trois entrées ont la même valeur. En utilisant l’une ou l’autre des commandes, ils obtiendront tous le même rang. Pour cet exemple, disons que c’est « 2 ». Voici comment les deux commandes évalueraient différemment les résultats suivants:

• RANK() donnerait aux lignes identiques un rang de 2, puis ignorerait les rangs 3 et 4, donc le résultat suivant serait 5
• DENSE_RANK() donnerait toujours à toutes les lignes identiques un rang de 2, mais la ligne suivante serait 3 – non les classements seraient ignorés.

NTILE

Vous pouvez utiliser les fonctions de fenêtre pour identifier le centile (ou quartile, ou toute autre subdivision) d’une ligne donnée. La syntaxe est NTILE(*# of buckets*). Dans ce cas, ORDER BY détermine la colonne à utiliser pour déterminer les quartiles (ou le nombre de « tuiles que vous préciser). Par exemple:

En 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 seulement vous avait deux enregistrements et que vous mesuriez des centiles, vous vous attendez à ce qu’un enregistrement définisse le 1er percentile et que l’autre enregistre le 100ème percentile. En utilisant la fonction NTILE, ce que vous « verriez réellement est un enregistrement dans le 1er centile et un dans le 2ème centile. Vous pouvez le voir dans les résultats pour start_terminal 31000: la colonne percentile ressemble simplement à un classement numérique. Si vous faites défiler jusqu’à start_terminal 31007, vous peut voir qu’il calcule correctement les percentiles car il y a plus de 100 enregistrements pour ce start_terminal. Si vous « travaillez avec de très petites fenêtres, gardez cela à l’esprit et envisagez d’utiliser des quartiles ou des petits bandes.

LAG et LEAD

Il peut souvent être utile de comparer les lignes aux lignes précédentes ou suivantes, en particulier si vous « avez reçu les données dans un ordre logique. Vous pouvez utiliser LAG ou LEAD pour créer des colonnes qui tirent des valeurs d’autres lignes – tout ce que vous avez à faire est de saisir la colonne à partir de laquelle extraire et le nombre de lignes éloignées que vous souhaitez effectuer. LAG tire des lignes précédentes et LEAD tire des lignes suivantes:

Ceci est particulièrement utile si vous souhaitez calculer 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 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 clairs, vous pouvez l’envelopper dans une requête externe pour supprimer les valeurs nulles:

Définition d’un alias de fenêtre

Si vous prévoyez d’écrire plusieurs fonctions de fenêtre dans la même requête, en utilisant la même fenêtre, vous pouvez créer un alias.Prenons l’exemple de NTILE ci-dessus:

Cela peut être réécrit comme:

Le WINDOW clause, si elle est incluse, doit toujours venir après la clause WHERE.

Techniques de fenêtrage avancées

Vous pouvez consulter une liste complète des fenêtres fonctions dans Postgres (la syntaxe utilisée par Mode) dans la documentation Postgres. Si vous utilisez des fonctions de fenêtre sur une base de données connectée, vous devriez consulter le guide de syntaxe approprié pour votre système.