Tout ce que vous devez savoir sur l’instruction SQL GROUP BY

Une grande partie de la puissance des bases de données relationnelles provient du filtrage des données et de la jonction de tables. C'est pourquoi nous représentons ces relations en premier lieu. Mais les systèmes de bases de données modernes fournissent une autre technique intéressante: le regroupement.

Le regroupement vous permet d'extraire des informations récapitulatives d'une base de données. Il vous permet de combiner les résultats pour créer des données statistiques utiles. Le regroupement vous évite d'écrire du code pour des cas courants tels que la moyenne des listes de chiffres. Et cela peut rendre les systèmes plus efficaces.

Que fait la clause GROUP BY?

GROUP BY, comme son nom l'indique, regroupe les résultats en un ensemble plus petit. Les résultats se composent d'une ligne pour chaque valeur distincte de la colonne groupée. Nous pouvons montrer son utilisation en examinant quelques exemples de données avec des lignes qui partagent des valeurs communes.

Ce qui suit est une base de données très simple avec deux tables représentant des albums de disques. Vous pouvez configurer une telle base de données en écrivant un schéma de base pour le système de base de données choisi. Le tableau des albums comporte neuf lignes avec une colonne d' identifiant de clé primaire et des colonnes pour le nom, l'artiste, l'année de sortie et les ventes:

 +----+---------------------------+-----------+--------------+-------+
 | id | name | artist_id | release_year | sales |
 +----+---------------------------+-----------+--------------+-------+
 | 1 | Abbey Road | 1 | 1969 | 14 |
 | 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
 | 3 | Rumours | 3 | 1977 | 28 |
 | 4 | Nevermind | 4 | 1991 | 17 |
 | 5 | Animals | 2 | 1977 | 6 |
 | 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
 | 7 | 21 | 6 | 2011 | 25 |
 | 8 | 25 | 6 | 2015 | 22 |
 | 9 | Bat Out of Hell | 7 | 1977 | 28 |
 +----+---------------------------+-----------+--------------+-------+

La table des artistes est encore plus simple. Il comporte sept lignes avec des colonnes d'identifiant et de nom:

 +----+---------------+
 | id | name |
 +----+---------------+
 | 1 | The Beatles |
 | 2 | Pink Floyd |
 | 3 | Fleetwood Mac |
 | 4 | Nirvana |
 | 5 | Elton John |
 | 6 | Adele |
 | 7 | Meat Loaf |
 +----+---------------+

Vous pouvez comprendre divers aspects de GROUP BY avec un simple jeu de données comme celui-ci. Bien sûr, un ensemble de données réelles aurait beaucoup, beaucoup plus de lignes, mais les principes restent les mêmes.

Regroupement par une seule colonne

Disons que nous voulons savoir combien d'albums nous avons pour chaque artiste. Commencez par une requête SELECT classique pour récupérer la colonne artist_id:

 SELECT artist_id FROM albums

Cela renvoie les neuf lignes, comme prévu:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 2 |
 | 5 |
 | 6 |
 | 6 |
 | 7 |
 +-----------+

Pour regrouper ces résultats par artiste, ajoutez la phrase GROUP BY artist_id :

 SELECT artist_id FROM albums GROUP BY artist_id

Ce qui donne les résultats suivants:

 +-----------+
 | artist_id |
 +-----------+
 | 1 |
 | 2 |
 | 3 |
 | 4 |
 | 5 |
 | 6 |
 | 7 |
 +-----------+

Il y a sept lignes dans le jeu de résultats, réduit par rapport au total de neuf dans le tableau des albums . Chaque artist_id unique a une seule ligne. Enfin, pour obtenir les nombres réels, ajoutez COUNT (*) aux colonnes sélectionnées:

 SELECT artist_id, COUNT(*)
 FROM albums
 GROUP BY artist_id
 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 1 | 1 |
 | 2 | 2 |
 | 3 | 1 |
 | 4 | 1 |
 | 5 | 1 |
 | 6 | 2 |
 | 7 | 1 |
 +-----------+----------+

Les résultats regroupent deux paires de lignes pour les artistes avec les identifiants 2 et 6 . Chacun a deux albums dans notre base de données.

En relation: La feuille de triche des commandes SQL essentielles pour les débutants

Comment accéder aux données groupées avec une fonction d'agrégation

Vous avez peut-être déjà utilisé la fonction COUNT , en particulier sous la forme COUNT (*) comme indiqué ci-dessus. Il récupère le nombre de résultats dans un ensemble. Vous pouvez l'utiliser pour obtenir le nombre total d'enregistrements dans une table:

 SELECT COUNT(*) FROM albums
 +----------+
 | COUNT(*) |
 +----------+
 | 9 |
 +----------+

COUNT est une fonction d'agrégation. Ce terme fait référence aux fonctions qui traduisent les valeurs de plusieurs lignes en une seule valeur. Ils sont souvent utilisés en conjonction avec l'instruction GROUP BY.

Plutôt que de simplement compter le nombre de lignes, nous pouvons appliquer une fonction d'agrégation aux valeurs groupées:

 SELECT artist_id, SUM(sales)
 FROM albums
 GROUP BY artist_id
 +-----------+------------+
 | artist_id | SUM(sales) |
 +-----------+------------+
 | 1 | 14 |
 | 2 | 30 |
 | 3 | 28 |
 | 4 | 17 |
 | 5 | 8 |
 | 6 | 47 |
 | 7 | 28 |
 +-----------+------------+

Les ventes totales indiquées ci-dessus pour les artistes 2 et 6 sont les ventes de leurs multiples albums combinées:

 SELECT artist_id, sales
 FROM albums
 WHERE artist_id IN (2, 6)
 +-----------+-------+
 | artist_id | sales |
 +-----------+-------+
 | 2 | 24 |
 | 2 | 6 |
 | 6 | 25 |
 | 6 | 22 |
 +-----------+-------+

Regroupement par plusieurs colonnes

Vous pouvez regrouper par plusieurs colonnes. Incluez simplement plusieurs colonnes ou expressions, séparées par des virgules. Les résultats seront regroupés en fonction de la combinaison de ces colonnes.

 SELECT release_year, sales, count(*)
 FROM albums
 GROUP BY release_year, sales

Cela produira généralement plus de résultats que le regroupement par une seule colonne:

 +--------------+-------+----------+
 | release_year | sales | count(*) |
 +--------------+-------+----------+
 | 1969 | 14 | 1 |
 | 1973 | 24 | 1 |
 | 1977 | 28 | 2 |
 | 1991 | 17 | 1 |
 | 1977 | 6 | 1 |
 | 1973 | 8 | 1 |
 | 2011 | 25 | 1 |
 | 2015 | 22 | 1 |
 +--------------+-------+----------+

Notez que, dans notre petit exemple, seuls deux albums ont la même année de sortie et le même nombre de ventes (28 en 1977).

Fonctions d'agrégation utiles

Outre COUNT, plusieurs fonctions fonctionnent bien avec GROUP. Chaque fonction renvoie une valeur basée sur les enregistrements appartenant à chaque groupe de résultats.

• COUNT () renvoie le nombre total d'enregistrements correspondants.
• SUM () renvoie le total de toutes les valeurs de la colonne donnée additionnée.
• MIN () renvoie la plus petite valeur d'une colonne donnée.
• MAX () renvoie la plus grande valeur d'une colonne donnée.
• AVG () renvoie la moyenne moyenne. C'est l'équivalent de SUM () / COUNT ().

Vous pouvez également utiliser ces fonctions sans clause GROUP:

 SELECT AVG(sales) FROM albums
 +------------+
 | AVG(sales) |
 +------------+
 | 19.1111 |
 +------------+

Utilisation de GROUP BY avec une clause WHERE

Tout comme avec un SELECT normal, vous pouvez toujours utiliser WHERE pour filtrer le jeu de résultats:

 SELECT artist_id, COUNT(*)
 FROM albums
 WHERE release_year > 1990
 GROUP BY artist_id

 +-----------+----------+
 | artist_id | COUNT(*) |
 +-----------+----------+
 | 4 | 1 |
 | 6 | 2 |
 +-----------+----------+

Maintenant, vous n'avez que les albums sortis après 1990, regroupés par artiste. Vous pouvez également utiliser une jointure avec la clause WHERE, indépendamment de GROUP BY:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id

 +---------+--------+
 | name | albums |
 +---------+--------+
 | Nirvana | 1 |
 | Adele | 2 |
 +---------+--------+

Notez, cependant, que si vous essayez de filtrer en fonction d'une colonne agrégée:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND albums > 2
 GROUP BY artist_id;

Vous obtiendrez une erreur:

 ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Les colonnes basées sur des données agrégées ne sont pas disponibles pour la clause WHERE.

Utilisation de la clause HAVING

Alors, comment filtrer le jeu de résultats une fois le regroupement effectué? La clause HAVING répond à ce besoin:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 GROUP BY artist_id
 HAVING albums > 1;

Notez que la clause HAVING vient après le GROUP BY. Sinon, c'est essentiellement un simple remplacement du WHERE par HAVING. Les résultats sont:

 +------------+--------+
 | name | albums |
 +------------+--------+
 | Pink Floyd | 2 |
 | Adele | 2 |
 +------------+--------+

Vous pouvez toujours utiliser une condition WHERE pour filtrer les résultats avant le regroupement. Il fonctionnera avec une clause HAVING pour le filtrage après le regroupement:

 SELECT r.name, COUNT(*) AS albums
 FROM albums l, artists r
 WHERE artist_id=r.id
 AND release_year > 1990
 GROUP BY artist_id
 HAVING albums > 1;

Un seul artiste de notre base de données a publié plus d'un album après 1990:

 +-------+--------+
 | name | albums |
 +-------+--------+
 | Adele | 2 |
 +-------+--------+

Combiner les résultats avec GROUP BY

L'instruction GROUP BY est une partie incroyablement utile du langage SQL. Il peut fournir des informations récapitulatives de données, pour une page de contenu, par exemple. C'est une excellente alternative à la récupération de grandes quantités de données. La base de données gère bien cette charge de travail supplémentaire car sa conception même la rend optimale pour le travail.

Une fois que vous aurez compris le regroupement et comment joindre plusieurs tables, vous serez en mesure d'utiliser la plus grande partie de la puissance d'une base de données relationnelle.