Comment grouper par plusieurs colonnes en SQL

Lorsque vous analysez de grands ensembles de données, vous créez souvent des regroupements et appliquez des fonctions d'agrégation pour trouver des totaux ou des moyennes. Dans ces cas, l'utilisation de la clause GROUP BY avec plusieurs colonnes déploie tout son potentiel.

GROUP BY est une clause de la commande SELECT. Elle vous permet de calculer diverses statistiques pour un groupe de lignes. Par exemple, vous pouvez utiliser GROUP BY avec une table d'employés pour savoir combien d'employés sont de chaque sexe. Ou vous pouvez regrouper plusieurs colonnes pour déterminer l'âge moyen des véhicules pour chaque marque et chaque modèle dans une table vehicle_fleet. Dans cet article, nous allons examiner en détail le fonctionnement du regroupement par plusieurs colonnes.

Cet article suppose que vous savez déjà comment utiliser GROUP BY dans une requête SQL. Vous n'êtes pas familier avec GROUP BY? La meilleure façon d'apprendre cette construction SQL et d'autres constructions de base est de suivre notre cours interactif SQL pour les débutants. Il contient 129 exercices pratiques. Chaque exercice est accompagné d'une brève explication et d'une tâche à résoudre. Chaque fois que vous effectuez un exercice, vous renforcez votre confiance dans vos compétences SQL. Ce cours est également un excellent moyen de revoir les fonctionnalités SQL de base si vos connaissances sont un peu rouillées.

Commençons par un rappel sur un cas d'utilisation simple de GROUP BY.

GROUP BY 1 Colonne

Chaque combinaison des valeurs de colonne(s) spécifiée dans la clause GROUP BY constitue un groupe ; la commande SELECT avec une clause GROUP BY affiche une seule ligne pour chaque groupe. Il est également bon de noter que GROUP BY vous permet d'appliquer des fonctions d'agrégation sur des colonnes non incluses dans le sous-ensemble exceptionnel.

Voyons un exemple. J'ai créé une table appelée WorldWideFriends qui stocke des données sur mes amis dans différentes parties du monde :

J'aimerais utiliser les informations de cette table pour effectuer des recherches, par exemple pour obtenir une liste des pays où vivent mes amis, y compris le nombre d'amis vivant dans chaque pays.

Si je voulais savoir combien d'amis j'ai dans chaque pays, j'utiliserais GROUP BY avec la fonction d'agrégation COUNT():

SELECT 
  Country, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country;

Cette requête me donne un ensemble de résultats qui condense les lignes ayant le même pays en une seule ligne, tandis que COUNT(*) m'indique combien de lignes répétées il y a pour chaque pays :

La requête ci-dessus me donne les informations dont j'aurais besoin si, par exemple, je devais choisir le pays dans lequel voyager afin de rencontrer le plus grand nombre possible de mes amis. Si vous souhaitez en savoir plus sur l'utilisation de base de GROUP BY, je vous recommande nos articles What Is GROUP BY in SQL et How to Use GROUP BY.

Toutefois, même si je me rends dans un pays où vivent un grand nombre de mes amis, ces derniers peuvent se trouver dans différents États. Je n'ai peut-être pas le temps de me déplacer d'un État à l'autre pour leur rendre visite à tous. Je dois donc affiner un peu ma recherche pour trouver l'emplacement géographique où se trouve la plus grande concentration de mes amis.

GROUP BY 2 Columns

J'ai maintenant besoin de savoir comment mes amis sont répartis par État et par pays. Pour ce faire, j'ajoute la colonne State à ma requête précédente GROUP BY Pays (en les séparant par des virgules) et dans la clause SELECT. La requête ressemble à ceci :

SELECT 
  Country, 
  State, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State;

En regardant les résultats de cette requête, nous pouvons voir que certains des pays qui n'apparaissaient auparavant que dans une seule ligne apparaissent maintenant dans plusieurs lignes. La raison en est que lorsque nous ajoutons le champ State, la requête doit assembler les groupes avec les lignes qui ont la même valeur dans Country et State.

Dans la requête précédente, la ligne correspondant à 'Colombia' avait un 3 dans le champ HowMany. Dans ce cas, "Colombia" apparaît dans deux lignes avec des valeurs différentes pour l'État : une pour "Antioquia" et l'autre pour "Cundinamarca". Dans le champ HowMany, la ligne correspondant à "Antioquia" indique 1, tandis que la ligne correspondant à "Cundinamarca" indique 2. Cela signifie que, dans la liste désagrégée, il y a deux lignes avec Country = 'Colombia' et State = 'Cundinamarca', et une seule avec Country = 'Colombia' et State = 'Antioquia'.

La somme des valeurs de HowMany de ces deux lignes correspond logiquement à la valeur précédente de HowMany pour la ligne correspondant à 'Colombia'. Il en va de même pour tous les autres pays qui sont divisés en plusieurs rangées avec des états différents.

GROUP BY plusieurs colonnes

Enfin, si mon intention est de faire en sorte que mon voyage soit le plus court possible tout en rendant visite à un maximum d'amis, il me suffit d'ajouter la colonne City à ma requête - à la fois dans SELECT et dans GROUP BY - pour voir quelles villes ont le plus grand nombre d'amis :

SELECT 
  Country, 
  State, 
  City, 
  COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City;

Lorsque nous ajoutons des colonnes à GROUP BY, le nombre de lignes dans le résultat augmente. Cela est dû au fait que le nombre de combinaisons de valeurs possibles augmente. Lorsque j'ajoute la colonne City au SQL GROUP BY, la taille du résultat augmente considérablement :

Dans ce cas, je pense qu'il serait préférable de ne voir que les villes où il y a plus d'un de mes amis. Pour résumer les résultats, je vais donc utiliser la clause HAVING. Cette clause me permet de poser une condition sur les résultats des fonctions d'agrégation lorsque j'utilise GROUP BY. Ici, la condition à appliquer sera que le nombre d'amis soit supérieur à 1 (COUNT(*) > 1). Après avoir incorporé la clause HAVING, la requête ressemble à ceci :

SELECT Country, State, City, COUNT(*) AS HowMany
FROM WorldWideFriends
GROUP BY Country, State, City
HAVING COUNT(*) > 1;

De cette façon, le résultat de la requête est réduit à une seule ligne qui me montre la seule ville où il y a plus d'un de mes amis :

Autres façons d'utiliser GROUP BY avec plusieurs colonnes

Il est courant d'utiliser GROUP BY par plusieurs colonnes lorsque deux ou plusieurs des colonnes d'un résultat de requête forment une hiérarchie de classifications à plusieurs niveaux. De telles hiérarchies se retrouvent dans de nombreux domaines, tels que :

• Des données de vente détaillées dont la date de vente est divisée en année, trimestre et mois.
• Le catalogue de produits d'un fabricant organisé par famille, marque, ligne, modèle.
• La masse salariale des employés d'une entreprise organisée par direction, secteur, département.

Dans tous ces cas, différents sous-ensembles de colonnes peuvent être utilisés sur le site GROUP BY pour passer du général au particulier.

Utilisation de GROUP BY sur plusieurs colonnes : Regroupement d'une hiérarchie

Examinons un exemple d'ensemble de résultats de données de vente. Supposons que vous ayez une vue appelée ViewSales qui renvoie les informations suivantes :

Il est facile de voir que les premiers champs de cette table forment une hiérarchie, avec l'année comme niveau le plus élevé et la date comme niveau le plus bas. À l'aide de GROUP BY et de la fonction SUM(), nous pouvons obtenir le montant total des ventes par Year, par Quarter, par Month ou par Date. Si vous souhaitez obtenir le total des unités vendues et le prix unitaire moyen par Year et Quarter, vous devez spécifier ces deux colonnes dans les champs SELECT et GROUP BY:

SELECT 
  Year, 
  Quarter, 
  SUM(Quantity) AS TotalQty, 
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Year, Quarter;

Le résultat sera :

Veuillez noter que, bien qu'il y ait un ordre hiérarchique, les données des différentes colonnes de regroupement sont indépendantes les unes des autres. Cela signifie que si vous regroupez uniquement par Quarter au lieu de Year plus Quarter, les calculs d'agrégats combineront les informations du même trimestre pour toutes les années (c'est-à-dire que tous les T2 auront une seule ligne) :

SELECT 
  Quarter, 
  SUM(Quantity) AS TotalQty,
  AVG(Unit_Price) as AvgUnit_Prc
FROM ViewSales
GROUP BY Quarter;

Il ne s'agit pas d'une erreur ; vous devez simplement comprendre que les résultats expriment des idées différentes. La seconde requête vous permet de comparer les performances des ventes entre différents trimestres, quelle que soit l'année (par exemple, pour détecter les facteurs saisonniers qui affectent les ventes à la même période de l'année), tandis que la première compare les ventes pour chaque année et chaque trimestre.

Utilisation de GROUP BY sur plusieurs colonnes : Regroupement non hiérarchique

Dans l'exemple précédent, nous avons vu que le regroupement par plusieurs colonnes nous permet de passer du général au particulier lorsque nous avons des ensembles de données dont les colonnes forment une hiérarchie de données. Mais dans les situations où un ensemble de résultats est composé de colonnes qui ne forment pas une hiérarchie, l'utilisation de GROUP BY avec plusieurs colonnes nous permet de découvrir des vérités cachées dans de grands ensembles de données ; elle combine des attributs qui, à première vue, n'ont aucun rapport entre eux.

Par exemple, imaginons que nous disposions d'une table nommée Downloads qui stocke des informations sur les personnes ayant téléchargé des films à partir d'un service de streaming au cours des deux dernières années. Cette table comporte une ligne pour chaque téléchargement, et chaque ligne comprend les informations suivantes sur chaque personne ayant téléchargé un film :

• Age
• Gender
• Nationality

Chaque ligne saisit également ces attributs sur chaque film téléchargé :

• Genre
• Year
• Country

En utilisant GROUP BY avec plusieurs de ces colonnes et la fonction COUNT(*), nous pouvons détecter des corrélations entre les colonnes. Par exemple, pour connaître les préférences en matière de genre de film en fonction de l'âge, on peut taper :

SELECT 
  Age, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Age, Genre

Comme résultats, nous obtiendrons quelque chose comme ceci :

Nous pourrions également utiliser GROUP BY 3 colonnes, pour connaître (par exemple) les préférences de genre par sexe et nationalité :

SELECT 
  Gender, 
  Nationality, 
  Genre, 
  COUNT(*) AS Downloads
FROM Downloads
GROUP BY Gender, Nationality, Genre

Et nous obtiendrions quelque chose comme ça :

GROUP BY avec plusieurs colonnes renvoie des informations à facettes

GROUP BY est un outil puissant qui permet d'extraire des informations de grands ensembles de données difficiles à manipuler d'une autre manière. En utilisant GROUP BY plusieurs colonnes, vous pouvez exploiter tout son potentiel pour exposer les vérités d'un ensemble de données, en vous permettant d'en voir différentes facettes. Pour réussir cette opération, il est essentiel que vous compreniez - et sachiez expliquer - ce que représente un ensemble de résultats SQL groupés par colonnes multiples.

Si vous envisagez d'effectuer un travail sérieux d'analyse de données, vous devriez suivre notre cours interactif SQL pour les débutants pour découvrir tous les outils que SQL peut offrir. En outre, suivez ces liens si vous avez besoin de plus d'explications sur GROUP BY ou si vous voulez voir d'autres exemples de GROUP BY en SQL.