Comment fonctionne la clause GROUP BY de SQL ?

Le regroupement des résultats est une fonction SQL puissante qui vous permet de calculer des statistiques clés pour un groupe d'enregistrements.

GROUP BY est l'une des clauses les plus puissantes de SQL. Elle vous permet de voir les données d'une nouvelle manière et de trouver des mesures clés (comme la moyenne, les valeurs maximales et minimales dans un groupe d'enregistrements).

Sans GROUP BY, tous les résultats que nous obtenons sont orientés vers les enregistrements. Avec GROUP BY, nous pouvons créer des groupes d'enregistrements et calculer des métriques sur chaque groupe. Dans cet article, vous apprendrez comment GROUP BY rend vos requêtes SQL beaucoup plus puissantes et diversifiées.

GROUP BY, 1ère partie : Regroupement des données

Supposons que nous ayons un petit hôtel en Patagonie. Nous disposons également d'une base de données contenant les noms des clients, leurs villes d'origine, leurs âges, leurs dates d'arrivée et de départ, etc. Ces données se trouvent dans deux tables appelées room_guest et guest. Regardez :

room_guest

guest

Nous voulons calculer certaines statistiques afin de pouvoir réserver davantage d'invités. La clause SQL GROUP BY nous permet de regrouper des enregistrements en fonction des données d'une ou plusieurs colonnes données. Nous pouvons regrouper les enregistrements de la table room_guest en fonction de la valeur de la colonne origin_city. Tous les enregistrements des clients de "Genova" appartiennent alors à un groupe, tous les enregistrements des clients de "Dubai" appartiennent à un autre groupe, et ainsi de suite. Le tableau suivant présente chaque groupe d'enregistrements dans une couleur différente.

Supposons maintenant que le propriétaire de l'hôtel souhaite savoir combien de clients viennent de chaque ville. Pour le savoir, nous devons compter le nombre d'enregistrements dans chaque groupe. En d'autres termes, nous avons besoin de la fonction d'agrégation COUNT(*), qui renvoie le nombre d'enregistrements dans un groupe. COUNT() est une fonction très courante ; nous y reviendrons plus tard dans cet article.

Nous avons donc besoin d'une requête pour créer des groupes d'enregistrements ayant la même valeur dans origin_city et compter ensuite le nombre d'enregistrements dans chaque groupe. La requête ressemblerait à ceci :

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

Vous pouvez comparer le nombre d'invités de chaque ville dans le tableau de résultats ci-dessous avec le tableau coloré présenté précédemment :

Remarquez que le nombre de lignes dans les résultats de la requête est le même que la quantité de groupes créés par la clause GROUP BY. Un groupe pour chaque ville, une ligne pour chaque ville.

Pour conclure cette introduction à GROUP BY, je vous suggère de lire l'article Getting the Hang of the GROUP BY Clause. Il comprend une description complète de GROUP BY et plusieurs exemples de ses erreurs les plus courantes.

GROUP BY, partie 2 : fonctions d'agrégation

Si le regroupement par une valeur est pratique, la véritable puissance de GROUP BY réside dans son utilisation avec les fonctions d'agrégation. J'irais même jusqu'à dire que toute requête SQL utilisant une clause GROUP BY devrait comporter au moins une fonction d'agrégation. (Mais ce n'est pas obligatoire).

Dans la section précédente, nous avons mentionné que GROUP BY est utilisé pour créer des groupes et calculer des métriques. Les métriques sont calculées par des fonctions d'agrégation comme COUNT(), SUM(), AVG(), MIN() et MAX(). Les valeurs calculées par chacune de ces fonctions sont explicites. Cependant, elles ont toutes quelque chose en commun : toutes les fonctions d'agrégation renvoient une valeur basée sur tous les enregistrements du groupe.

Prenons un exemple. Le propriétaire d'un hôtel veut connaître la valeur maximale facturée pour chaque chambre. Il souhaite également connaître la valeur minimale et moyenne facturée pour chaque chambre. Voici la requête, suivie des résultats :

SELECT 	room_number,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number

Regroupement d'enregistrements par plusieurs colonnes

Dans certains cas, nous pouvons avoir besoin de regrouper les enregistrements par deux colonnes ou plus. Pouvons-nous le faire avec GROUP BY? Certainement !

Dans la requête précédente, nous avons créé un rapport analysant combien d'argent chaque chambre génère. Cependant, certaines chambres peuvent être configurées à un niveau premium ou standard (voir la chambre numéro 2002) pendant différentes saisons ; ainsi, pour effectuer une analyse correcte, nous devons regrouper les enregistrements en utilisant deux colonnes : room_number et room_level.

Avant de passer à la requête, utilisons des couleurs pour voir comment les enregistrements sont regroupés par la clause GROUP BY room_number, room_level. Rappelez-vous que les enregistrements de chaque groupe doivent avoir exactement les mêmes valeurs dans room_number et room_level. Par exemple, le premier groupe concerne room_number = 1001 et room_level = ‘standard’.

La requête est la suivante :

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest 
GROUP BY	room_number, room_level

Le tableau suivant montre les résultats de cette requête. Vous pouvez comparer ce tableau avec le tableau précédent pour vérifier les résultats.

Regroupement des valeurs NULL

Comme toute autre valeur, les valeurs NULL ont leur propre groupe ; si nous avons un NULL dans l'une des colonnes de GROUP BY, un groupe supplémentaire d'enregistrements est créé pour ces enregistrements. Pour démontrer cela, nous devons insérer une paire d'enregistrements avec des valeurs NULL dans la colonne origin_city:

INSERT INTO into room_guest VALUES ('Kevin C.', NULL, 2001, '2019-07-25', '2019-08-07', NULL, 'standard', 10500);
INSERT INTO into room_guest VALUES  ('Karl J.', NULL, 1002, '2019-11-12', '2019-11-22', NULL, 'premium', 13900);

Ensuite, cette requête ...

SELECT 	origin_city,
COUNT(*) AS quantity_of_guests
FROM   	room_guest 
GROUP BY	origin_city

... donnera le résultat suivant. Remarquez le nouveau groupe pour les valeurs NULL origin_city dans la première ligne :

Utilisation de WHERE avec GROUP BY

La clause WHERE est fréquemment utilisée dans les requêtes SQL, il est donc important de comprendre comment elle fonctionne lorsqu'elle est combinée avec GROUP BY.

La clause WHERE est appliquée avant la clause GROUP BY. Cela signifie que tous les enregistrements sont d'abord filtrés par WHERE; ensuite, les enregistrements qui correspondent à la condition WHERE sont regroupés à l'aide des critères GROUP BY.

À titre d'exemple, utilisons la requête précédente, mais cette fois-ci, nous filtrons les invités provenant des villes de San Francisco et Los Angeles. La requête est la suivante :

SELECT 	room_number,
		room_level,
MAX(amount_invoiced) AS max_amount_invoiced,
MIN(amount_invoiced) AS min_amount_invoiced,
AVG(amount_invoiced) AS average_amount_invoiced
FROM   	room_guest
WHERE		origin_city IN (‘San Francisco’,’Los Angeles’ )
GROUP BY	room_number, room_level

Comme on pouvait s'y attendre, cet ensemble de résultats est plus court que les précédents ; la clause WHERE a filtré de nombreux invités, et seuls les enregistrements des chambres de San Francisco et Los Angeles ont été traités par la clause GROUP BY.

Éviter les problèmes avec GROUP BY

Lorsque vous commencez à utiliser GROUP BY, il est courant de rencontrer les problèmes suivants. Voici comment les éviter.

Problèmes de comptage

Examinons un cas similaire où nous devons ajouter plus d'une colonne supplémentaire dans la clause GROUP BY. Dans la première requête, nous avons regroupé les données par origin_city. Cependant, certaines villes portent le même nom (parce qu'elles se trouvent dans différents États ou pays). Dans notre ensemble de données, nous avons deux villes différentes nommées San Pedro, l'une en Argentine et l'autre en Espagne. Nous ne voulons pas les compter ensemble, car ce sont deux endroits différents.

Pour compter ces villes séparément, nous devons regrouper les enregistrements en utilisant les colonnes city_origin, state et country. Nous allons ensuite répéter la première requête mais en ajoutant les colonnes state et country à la clause GROUP BY. Cependant, si nous ajoutons des colonnes à la clause GROUP BY, nous devons également les ajouter à la clause SELECT.

Comme les colonnes état et pays sont dans la table guest nous devons JOIN les tables room_guest et guest. Voici la requête que nous avons :

SELECT 	origin_city, state, country
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Les résultats montrent deux villes "San Pedro" différentes car nous avons utilisé state et country comme colonnes supplémentaires dans la clause GROUP BY.

Il reste un problème à résoudre dans cette requête : si la même personne a visité l'hôtel deux fois, nous la comptons deux fois. Ce n'est pas nécessairement faux, mais que faire si nous voulons connaître le nombre de visiteurs uniques de l'hôtel ? Nous devrions utiliser COUNT(distinct guest_name). La fonction de regroupement COUNT(distinct column) renvoie la quantité de valeurs uniques pour une colonne donnée dans un groupe d'enregistrements.

Dans la requête ci-dessous, nous ajoutons la fonction COUNT(distinct). Nous conservons également la requête originale COUNT(*) afin que le lecteur puisse comparer les deux résultats :

SELECT 	origin_city, state, country
COUNT(distinct guest_name) AS number_of_unique_guests,
COUNT(*) AS number_of_guests
FROM   	room_guest 
JOIN		guest ON guest.guest_name = room_guest.guest_name
GROUP BY	origin_city, state, country

Maintenant, nous pouvons voir que l'hôtel a reçu un total de trois visites d'un résident de Dubaï, mais que ces trois visites ont été faites par deux personnes distinctes (Peter S. et Olek V) .

Avant de clore cette section, je vous suggère de regarder cette vidéo de 5 minutes sur GROUP BY pour les débutants. C'est une façon super dynamique d'apprendre SQL.

Omettre les colonnes non agrégées de GROUP BY

Une autre erreur très courante de GROUP BY est d'ajouter une colonne non agrégée (c'est-à-dire une colonne qui n'est pas utilisée dans une fonction d'agrégation) dans SELECT que vous n'avez pas dans GROUP BY. Pour éviter cette erreur, suivez une règle très simple : Toutes les colonnes de SELECT doivent apparaître dans la clause GROUP BY ou être utilisées dans une fonction d'agrégation.

Essayons une requête invalide pour voir l'erreur :

SELECT    room_number,
	    room_level,
	    origin_city, --This column is invalid, is not in the GROUP BY
    COUNT(*) AS quantity_of_visitors,
FROM      room_guest 
GROUP BY  room_number, room_level

Si nous exécutons cette requête, nous obtiendrons l'erreur suivante :

ERROR:  The column «room_guest.origin_city» must be in the GROUP BY clause
LINE 3:   guest_age,

Nous pouvons corriger cette erreur en ajoutant la colonne origin_city à la clause GROUP BY:

	SELECT 	room_number,
			room_level,
			Origin_city,
			COUNT(*) AS quantity_of_visitors
	FROM		room_gest
	GROUP BY	room_number, room_level, origin_city -- origin_city added

Si vous essayez de comprendre la différence entre GROUP BY et ORDER BY, lisez la différence entre GROUP BY et ORDER BY en termes simples. Elle vous aidera à y voir plus clair.

Il y a plus à faire avec GROUP BY

Nous avons donc appris à utiliser GROUP BY pour regrouper des enregistrements par valeurs communes. Nous savons que les fonctions d'agrégation MIN(), MAX(), AVG() et SUM() permettent de calculer diverses statistiques. Et la fonction COUNT() fait beaucoup de choses :

• COUNT(*) compte toutes les lignes.
• COUNT(guest_name) compte toutes les valeurs non NULL dans la colonne guest_name.
• COUNT(distinct guest_name) compte toutes les valeurs différentes non NULL dans la colonne guest_name.

Lors du regroupement, NULL obtient son propre groupe. Et toutes les colonnes non agrégées de SELECT doivent être présentes dans GROUP BY.

En raison de la longueur de l'article, je n'ai pas couvert la clause HAVING, qui est une sorte de clause WHERE utilisée pour filtrer les groupes au lieu des enregistrements. Pour les lecteurs qui souhaitent aller un peu plus loin, je vous laisse un lien vers notre SQL pour les débutants qui couvre de nombreux sujets intéressants. C'est un excellent moyen de renforcer vos compétences en SQL !