Quelle est la différence entre un GROUP BY et un PARTITION BY ?

Les fonctions de fenêtre sont un excellent ajout à SQL, et elles peuvent vous rendre la vie beaucoup plus facile si vous savez comment les utiliser correctement. Aujourd'hui, nous allons aborder les différences entre un GROUP BY et un PARTITION BY. Nous commencerons par les bases et vous amènerons lentement à un point où vous pourrez poursuivre vos recherches par vous-même.

PARTITION BY et GROUP BY

Les clauses PARTITION BY et GROUP BY sont fréquemment utilisées en SQL lorsque vous devez créer un rapport complexe. Si le retour des données elles-mêmes est utile (et même nécessaire) dans de nombreux cas, des calculs plus complexes sont souvent requis. C'est là que GROUP BY et PARTITION BY interviennent. Bien qu'elles soient très similaires dans la mesure où elles effectuent toutes deux des regroupements, il existe des différences essentielles. Nous allons analyser ces différences dans cet article.

GROUP BY

La clause GROUP BY est utilisée dans les requêtes SQL pour définir des groupes sur la base de certains critères donnés. Ces critères sont ceux que l'on retrouve habituellement sous forme de catégories dans les rapports. Voici des exemples de critères de regroupement :

• grouper tous les employés par leur niveau de salaire annuel
• grouper tous les trains par leur première station
• regrouper les revenus et les dépenses par mois
• regrouper les étudiants en fonction de la classe dans laquelle ils sont inscrits.

L'utilisation de la clause GROUP BY transforme les données en un nouvel ensemble de résultats dans lequel les enregistrements originaux sont placés dans différents groupes à l'aide des critères que nous fournissons. Vous trouverez plus de détails sur la clause GROUP BY dans cet article.

Nous pouvons effectuer d'autres actions ou calculs sur ces groupes, dont la plupart sont étroitement liés aux fonctions d'agrégation. Pour rappel, les fonctions d'agrégation sont utilisées pour agréger nos données et, par conséquent, nous perdons les détails originaux dans le résultat de la requête. Il existe de nombreuses fonctions d'agrégation, mais les plus couramment utilisées sont COUNT, SUM, AVG, MIN et MAX.

Si vous souhaitez vous entraîner à utiliser la clause GROUP BY, nous vous recommandons notre cours interactif Créer des rapports en SQL. Les fonctions agrégées et la clause GROUP BY sont essentielles à la rédaction de rapports en SQL.

Prenons l'exemple suivant. Ici, nous avons le train contenant les informations sur les trains, la table journey contenant les informations sur les trajets effectués par les trains, et la table route tableau contenant les informations sur les itinéraires des trajets. Regardez ci-dessous les données et la façon dont les tables sont liées :

Exécutons la requête suivante qui renvoie les informations sur les trains et les trajets associés en utilisant les tables train et la table journey .

SELECT
        train.id,
        train.model,
        journey.*
FROM train
INNER JOIN journey ON journey.train_id = train.id
ORDER BY
        train.id ASC;

Voici le résultat :

Vous pouvez voir que le train avec id = 1 a 5 lignes différentes, le train avec id = 2 a 4 lignes différentes, etc.

Maintenant, exécutons une requête avec les deux mêmes tables en utilisant une GROUP BY.

SELECT
  	train.id,
	train.model,
	COUNT(*) AS routes
FROM train
INNER JOIN journey ON journey.train_id = train.id
GROUP BY
  	train.id,
	train.model
ORDER BY
  	train.id ASC;

Et le résultat est le suivant :

À partir du résultat de la requête, vous pouvez voir que nous avons agrégé les informations, nous indiquant le nombre de trajets pour chaque train. Dans le processus, nous avons perdu les détails au niveau des lignes de la table journey de la table.

Vous pouvez comparer cet ensemble de résultats au précédent et vérifier que le nombre de lignes renvoyées par la première requête (nombre d'itinéraires) correspond à la somme des nombres de la colonne agrégée (routes) du résultat de la deuxième requête.

Bien que vous puissiez utiliser des fonctions d'agrégation dans une requête sans clause GROUP BY, cela est nécessaire dans la plupart des cas. Les fonctions d'agrégation fonctionnent comme suit :

1. Vous générez des groupes à l'aide d'une instruction GROUP BY en spécifiant une ou plusieurs colonnes qui ont la même valeur dans chaque groupe.
2. La fonction d'agrégation calcule le résultat.
3. Les lignes d'origine sont "réduites". Vous pouvez accéder aux colonnes de l'instruction GROUP BY et aux valeurs produites par les fonctions d'agrégation, mais les détails d'origine au niveau des lignes ne sont plus présents.

Dans la plupart des cas, il n'y a pas de problème à "réduire" les lignes. Parfois, cependant, vous devez combiner les détails d'origine au niveau des lignes avec les valeurs renvoyées par les fonctions d'agrégation. Cela peut être fait avec des sous-requêtes en reliant les lignes de la table d'origine avec le jeu résultant de la requête à l'aide de fonctions d'agrégation. Vous pouvez également adopter une approche différente, comme nous le verrons plus loin.

PARTITION PAR

En fonction de ce que vous devez faire, vous pouvez utiliser un PARTITION BY dans nos requêtes pour calculer des valeurs agrégées sur les groupes définis. Le PARTITION BY est combiné avec le OVER() et les fonctions de fenêtres pour calculer les valeurs agrégées. C'est très similaire à GROUP BY et aux fonctions d'agrégation, mais avec une différence importante : lorsque vous utilisez PARTITION BY, les détails au niveau des lignes sont préservés et ne sont pas réduits. En d'autres termes, vous disposez toujours des détails de niveau ligne d'origine ainsi que des valeurs agrégées. Toutes les fonctions d'agrégation peuvent être utilisées comme des fonctions de fenêtre.

Examinons la requête suivante. En plus de train et journeynous incorporons maintenant la table des routes.

SELECT
  	train.id,
	train.model,
	route.name,
	route.from_city,
	route.to_city,
	COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes,
	COUNT(*) OVER () AS routes_total
FROM train
INNER JOIN journey ON journey.train_id = train.id
INNER JOIN route ON journey.route_id = route.id;

Voici le résultat de la requête :

A partir de l'ensemble des résultats, nous notons plusieurs points importants :

• Nous n'avons pas utilisé de GROUP BY mais nous avons quand même obtenu des valeurs agrégées (routes et routes_total).
• Nous avons les mêmes colonnes (id et model) que celles de GROUP BY dans la requête précédente, mais les détails originaux au niveau des lignes ont été préservés. Les valeurs agrégées sont répétées dans toutes les lignes avec les mêmes valeurs d'id et de modèle. C'est normal ; par exemple, nous avons 5 enregistrements de trajet pour id = 1, qui ont tous des valeurs identiques pour ces colonnes.
• Nous avons également des valeurs dans les colonnes nom, from_city, et to_city qui sont différentes pour une valeur donnée de id. Si nous avions utilisé un GROUP BY sur les colonnes id et model, ces détails de niveau ligne auraient été perdus.
• COUNT(*) OVER () AS routes_total a produit le même nombre agrégé, 30, que COUNT et GROUP BY. Dans cet ensemble de résultats, cependant, cette valeur est incluse dans chaque ligne.
• La partie COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes est très intéressante. Nous avons défini le groupe sur lequel cette fonction fenêtre doit être utilisée avec la clause PARTITION BY. Par conséquent, dans la colonne routes, nous avons un compte de lignes pour ce groupe uniquement. Les fonctions de fenêtre sont appliquées après le filtrage des lignes, ce qui permet de conserver les détails au niveau des lignes tout en définissant les groupes à l'aide de PARTITION BY.

L'utilisation de fonctions d'agrégation standard comme fonctions de fenêtre avec le mot-clé OVER() nous permet de combiner les valeurs agrégées et de conserver les valeurs des lignes d'origine. Nous pouvons accomplir la même chose en utilisant des fonctions d'agrégation, mais cela nécessite des sous-requêtes pour chaque groupe ou partition.

Il est important de noter que toutes les fonctions d'agrégation standard peuvent être utilisées comme des fonctions de fenêtre comme celle-ci.

Fonctions de fenêtrage

Outre les fonctions d'agrégation, il existe d'autres fonctions de fenêtre importantes, telles que :

• ROW_NUMBER(). Renvoie le numéro de séquence de la ligne dans le jeu de résultats.
• RANK(). Similaire à ROW_NUMBER(), mais peut prendre une colonne comme argument. L'ordre de classement est déterminé par la valeur de cette colonne. Si deux lignes ou plus ont la même valeur dans cette colonne, ces lignes ont toutes le même rang. Le rang suivant sera déterminé à partir du nombre équivalent de rangs supérieurs ; par exemple, si deux rangs partagent un rang de 10, le rang suivant sera 12.
• DENSE_RANK(). Très similaire à RANK(), sauf qu'il n'y a pas de "trous". Dans l'exemple précédent, si deux lignes partagent un rang de 10, le rang suivant sera 11.
• NTILE. Utilisé pour calculer les quartiles, les déciles ou tout autre centile.
• LAG & LEAD. Utilisé pour extraire des valeurs de la ligne précédente (LAG) ou suivante (LEAD).

Il n'existe pas de règle générale concernant l'utilisation des fonctions de fenêtre, mais vous pouvez développer un sens de l'utilisation. Je vous recommande vivement de suivre le cours Fonctions de fenêtrage ; vous y trouverez tous les détails que vous souhaitez connaître !

PARTITION BY et GROUP BY : Similitudes et différences

Bien que nous utilisions la plupart du temps un GROUP BY, il existe de nombreux cas où un PARTITION BY serait un meilleur choix. Dans certains cas, vous pouvez utiliser un GROUP BY en utilisant des sous-requêtes pour simuler un PARTITION BY, mais cela peut aboutir à des requêtes très complexes.

Terminons par les similitudes et les différences les plus importantes :

• Similitude : Les deux sont utilisés pour renvoyer des valeurs agrégées.
• Différence : L'utilisation d'une clause GROUP BY réduit les lignes d'origine ; pour cette raison, vous ne pouvez pas accéder aux valeurs d'origine plus tard dans la requête. En revanche, l'utilisation d'une clause PARTITION BY conserve les valeurs originales tout en nous permettant de produire des valeurs agrégées.
• Différence : La clause PARTITION BY est combinée avec les fonctions OVER() et windows pour ajouter beaucoup plus de fonctionnalités.