Comment utiliser la clause SQL PARTITION BY avec OVER

Au cœur de chaque appel de fonction fenêtre se trouve une clause OVER qui définit comment les fenêtres des enregistrements sont construites. Dans la clause OVER, il peut y avoir une sous-clause facultative PARTITION BY qui définit les critères d'identification des enregistrements à inclure dans chaque fenêtre. Poursuivez votre lecture et franchissez une étape importante dans le développement de vos compétences en SQL !

Qu'est-ce que la clause PARTITION BY en SQL ?

L'expression SQL PARTITION BY est une sous-clause de la clause OVER, qui est utilisée dans presque toutes les invocations de fonctions de fenêtre comme AVG(), MAX() et RANK(). Comme de nombreux lecteurs le savent probablement, les fonctions de fenêtre opèrent sur des cadres de fenêtre qui sont des ensembles de lignes qui peuvent être différents pour chaque enregistrement du résultat de la requête. C'est là qu'intervient la sous-clause SQL PARTITION BY: elle permet de définir les enregistrements qui doivent faire partie du cadre de fenêtre associé à chaque enregistrement du résultat.

Cet article explique le SQL PARTITION BY et ses utilisations avec des exemples. Comme il est profondément lié aux fonctions de fenêtre, vous pouvez d'abord lire certains articles sur les fonctions de fenêtre, comme "SQL Window Function Example With Explanations", où vous trouverez de nombreux exemples. Si vous souhaitez en savoir plus sur les fonctions de fenêtre, il existe également un article intéressant contenant de nombreux pointeurs vers d'autres articles sur les fonctions de fenêtre.

La première chose à laquelle il faut s'intéresser est la syntaxe. Voici comment utiliser la clause SQL PARTITION BY:

SELECT
    ,
     OVER(PARTITION BY  [ORDER BY ])
FROM table;

Examinons un exemple qui utilise une clause PARTITION BY. Nous allons utiliser la table suivante appelée car_list_prices:

Pour chaque voiture, nous voulons obtenir la marque, le modèle, le prix, le prix moyen sur l'ensemble des voitures, et le prix moyen sur le même type de voiture (pour avoir une meilleure idée de la façon dont le prix d'une voiture donnée se compare aux autres voitures). Voici la requête :

SELECT
    car_make,
    car_model,
    car_price,
    AVG(car_price) OVER() AS "overall average price",
    AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"
FROM car_list_prices

Le résultat de la requête est le suivant :

La requête ci-dessus utilise deux fonctions de fenêtre. La première est utilisée pour calculer le prix moyen de toutes les voitures de la liste de prix. Elle utilise la fonction fenêtre AVG() avec une clause OVER vide comme nous le voyons dans l'expression suivante :

AVG(car_price) OVER() AS "overall average price"

La deuxième fonction de fenêtre est utilisée pour calculer le prix moyen d'un car_type spécifique comme standard, premium, sport, etc. C'est ici que nous utilisons une clause OVER avec une sous-clause PARTITION BY comme nous le voyons dans cette expression :

AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price"

Les fonctions de fenêtre sont assez puissantes, non ? Si vous souhaitez en apprendre davantage en faisant des exercices bien préparés, je vous suggère le cours Fonctions de fenêtragequi vous permettra d'apprendre et de vous familiariser avec l'utilisation des fonctions de fenêtre dans les bases de données SQL.

Approfondir la clause SQL PARTITION BY

La clause GROUP BY regroupe un ensemble d'enregistrements en fonction de critères. Cela nous permet d'appliquer une fonction (par exemple, AVG() ou MAX()) à des groupes d'enregistrements pour obtenir un résultat par groupe.

Par exemple, supposons que nous voulions obtenir le prix moyen et le prix le plus élevé pour chaque marque. Utilisez la requête suivante :

SELECT car_make,
       AVG(car_price) AS average_price,
       MAX(car_price) AS top_price
FROM   car_list_prices
GROUP BY car_make

Voici le résultat de cette requête :

Par rapport aux fonctions de fenêtre, GROUP BY réduit les enregistrements individuels en un groupe. Par conséquent, vous ne pouvez pas faire référence à un champ d'enregistrement individuel, c'est-à-dire que seules les colonnes de la clause GROUP BY peuvent être référencées.

Par exemple, supposons que vous souhaitiez créer un rapport contenant le modèle, le prix et le prix moyen de la marque. Vous ne pouvez pas le faire en utilisant GROUP BY, car les enregistrements individuels de chaque modèle sont réduits en raison de la clause GROUP BY car_make. Pour ce genre de chose, vous devez utiliser les fonctions de fenêtre, comme nous le voyons dans l'exemple suivant :

SELECT car_make,
       car_model,
       car_price,
       AVG(car_price) OVER (PARTITION BY car_make) AS average_make
FROM   car_list_prices

Le résultat de cette requête est le suivant :

Pour ceux qui veulent aller plus loin, je suggère l'article ""Quelle est la différence entre un GROUP BY et un PARTITION BY ?" avec de nombreux exemples utilisant les fonctions agrégat et fenêtre.

En plus de la clause PARTITION BY, il existe une autre clause appelée ORDER BY qui établit l'ordre des enregistrements dans le cadre de la fenêtre. Certaines fonctions de fenêtre nécessitent une clause ORDER BY. Par exemple, les fonctions de fenêtre LEAD() et LAG() ont besoin que la fenêtre d'enregistrement soit ordonnée car elles accèdent à l'enregistrement précédent ou suivant à partir de l'enregistrement actuel.

Un cadre de fenêtre est composé de plusieurs lignes définies par les critères de la clause PARTITION BY. Cependant, nous pouvons spécifier des limites ou des bornes au cadre de la fenêtre comme nous le voyons dans l'image suivante :

Les limites inférieures et supérieures de la clause OVER peuvent être :

• UNBOUNDED PRECEDING
• n PRECEDING
• CURRENT ROW
• n FOLLOWING
• UNBOUNDED FOLLOWING

Lorsque nous ne spécifions aucune limite dans une clause OVER, son cadre de fenêtre est construit sur la base de certaines valeurs limites par défaut. Elles dépendent de la syntaxe utilisée pour appeler la fonction de fenêtre. Le tableau suivant montre les limites par défaut du cadre de la fenêtre.

Il existe un article détaillé intitulé "SQL Fonctions de fenêtrage Cheat Sheet" dans lequel vous trouverez de nombreux détails syntaxiques et exemples concernant les différentes limites du cadre de fenêtre.

La clause SQL PARTITION BY en action

Dans cette section, nous présentons quelques exemples de la clause SQL PARTITION BY. Tous sont basés sur la table paris_london_flightsutilisée par une compagnie aérienne pour analyser les résultats commerciaux de cette route pour les années 2018 et 2019. Voici un sous-ensemble de ces données :

Exemple 1

La première requête génère un rapport comprenant le site flight_number, aircraft_model avec la quantité de passagers transportés, et le revenu total. La requête est présentée ci-dessous :

SELECT DISTINCT
       flight_number,
       aircraft_model,
	SUM(num_of_passengers) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_passengers,
	SUM(total_revenue) OVER (PARTITION BY flight_number, aircraft_model)
                                                            AS total_revenue
FROM paris_london_flights
ORDER BY flight_number, aircraft_model;

Puisque le nombre total de passagers transportés et le revenu total sont générés pour chaque combinaison possible de flight_number et aircraft_model, nous utilisons la clause suivante PARTITION BY pour générer un ensemble d'enregistrements avec le même numéro de vol et le même modèle d'avion :

OVER (PARTITION BY flight_number, aircraft_model)

Ensuite, pour chaque ensemble d'enregistrements, nous appliquons les fonctions de fenêtre SUM(num_of_passengers) et SUM(total_revenue) pour obtenir les métriques total_passengers et total_revenue présentées dans l'ensemble de résultats suivant.

Exemple 2

Dans la requête suivante, nous montrons l'évolution de l'activité en comparant les mesures d'un mois à celles du mois précédent. Nous créons un rapport utilisant des fonctions de fenêtre pour montrer la variation mensuelle du nombre de passagers et des recettes.

WITH year_month_data AS (
  SELECT DISTINCT
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   SUM(number_of_passengers)
              OVER (PARTITION BY EXTRACT(YEAR FROM scheduled_departure),
                                  EXTRACT(MONTH FROM scheduled_departure)
                   ) AS passengers
   FROM  paris_london_flights
  ORDER BY 1, 2
)
SELECT  year,
        month,
	 passengers,
	 LAG(passengers) OVER (ORDER BY year, month) passengers_previous_month,
	 passengers - LAG(passengers) OVER (ORDER BY year, month) AS passengers_delta
FROM year_month_data;

Dans la requête ci-dessus, nous utilisons une clause WITH pour générer une CTE (CTE signifie common table expressions et est un type de requête permettant de générer une table virtuelle qui peut être utilisée dans le reste de la requête). Nous introduisons des données dans une table virtuelle appelée year_month_dataqui comporte 3 colonnes : year, month, et passengers avec le total des passagers transportés au cours du mois.

Ensuite, la deuxième requête (qui prend le CTE year_month_data en entrée) génère le résultat de la requête. La colonne passagers contient le total des passagers transportés associé à l'enregistrement actuel. Avec la fonction fenêtre LAG(passenger), nous obtenons la valeur de la colonne passagers de l'enregistrement précédent à l'enregistrement courant. Nous ORDER BY year and month :

LAG(passengers) OVER (ORDER BY year, month)
passengers_previous_month

Il obtient le nombre de passagers de l'enregistrement précédent, correspondant au mois précédent. Ensuite, nous avons le nombre de passagers pour le mois en cours et le mois précédent. Enfin, dans la dernière colonne, on calcule la différence entre les deux valeurs pour obtenir la variation mensuelle des passagers.

Troisième exemple

Pour notre dernier exemple, intéressons-nous aux retards des vols. Nous voulons obtenir différentes moyennes de retards pour expliquer les raisons de ces retards.

Nous utilisons un CTE pour calculer une colonne appelée month_delay avec le retard moyen pour chaque mois et obtenir le modèle d'avion. Ensuite, dans la requête principale, nous obtenons les différentes moyennes comme nous le voyons ci-dessous :

WITH paris_london_delays AS (
  SELECT DISTINCT
	   aircraft_model,
	   EXTRACT(YEAR FROM scheduled_departure) AS year,
	   EXTRACT(MONTH FROM scheduled_departure) AS month,
	   AVG(real_departure - scheduled_departure) AS month_delay
   FROM  paris_london_flights
   GROUP BY 1, 2, 3
)
SELECT  DISTINCT
     aircraft_model,
     year,
     month,
     month_delay AS monthly_avg_delay,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year) AS year_avg_delay,
     AVG(month_delay) OVER (PARTITION BY year) AS year_avg_delay_all_models,
     AVG(month_delay) OVER (PARTITION BY aircraft_model, year 
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                            ) AS rolling_average_last_4_months

FROM paris_london_delays
ORDER BY 1,2,3

Cette requête calcule plusieurs moyennes. La première est la moyenne par modèle d'avion et par année, ce qui est très clair. La seconde est la moyenne par année pour tous les modèles d'avions. Notez que nous utilisons uniquement la colonne année dans la clause PARTITION BY. La troisième et dernière moyenne est la moyenne glissante, où nous utilisons les 3 mois les plus récents et le mois en cours (c'est-à-dire la ligne) pour calculer la moyenne avec l'expression suivante :

AVG(month_delay) OVER (PARTITION BY aircraft_model, year
                               ORDER BY month
                               ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
                           ) AS rolling_average_last_4_months

La clause ROWS BETWEEN 3 PRECEDING AND CURRENT ROW du site PARTITION BY restreint le nombre de lignes (c'est-à-dire de mois) à inclure dans la moyenne : les 3 mois précédents et le mois en cours. Vous pouvez voir un résultat partiel de cette requête ci-dessous :

L'article "The RANGE Clause in SQL Fonctions de fenêtrage: 5 Practical Examples" explique comment définir un sous-ensemble de lignes dans le cadre de la fenêtre en utilisant RANGE au lieu de ROWS, avec plusieurs exemples. Un autre article intéressant est "Common SQL Fonctions de fenêtrage: Using Partitions With Ranking Functions" dans lequel la clause PARTITION BY est traitée en détail.

La puissance de Fonctions de fenêtrage et de la clause SQL PARTITION BY

Les fonctions de classement sont une ressource très puissante du langage SQL, et la clause SQL PARTITION BY joue un rôle central dans leur utilisation. Dans cet article, nous avons couvert le fonctionnement de cette clause et montré plusieurs exemples utilisant différentes syntaxes.

Avant de conclure, je vous propose un SQL avancé cours, où vous pourrez aller au-delà des bases et devenir un maître du SQL. Si vous voulez en savoir plus sur la clause OVER, il existe un article complet sur le sujet : "How to Define a Window Frame in SQL Fonctions de fenêtrage." Améliorez vos compétences et développez vos atouts !