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

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 des tables. C'est pourquoi nous représentons ces relations en premier lieu. Mais les systèmes de bases de données modernes offrent une autre technique précieuse : 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 les cas courants tels que la moyenne des listes de chiffres. Et cela peut rendre les systèmes plus efficaces.





À quoi sert la clause GROUP BY ?

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





vous avez un ordinateur de bureau que vous souhaitez mettre à niveau. vous ajoutez plusieurs composants internes

Ce qui suit est une base de données très simple avec deux tables représentant des albums de disques. Vous pouvez créer une telle base de données en écrire un schéma de base pour le système de base de données que vous avez choisi. Les albums la table a neuf lignes avec une clé primaire identifiant colonne et 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 |
+----+---------------------------+-----------+--------------+-------+

Les artistes le tableau est encore plus simple. Il comporte sept lignes avec des colonnes id et name :



+----+---------------+
| 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 ensemble 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 un type SÉLECTIONNER requête 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éduites du total neuf dans le albums table. Chaque unique ID_artiste a une seule rangée. Enfin, pour obtenir les comptes réels, ajoutez COMPTER(*) 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 des identifiants 2 et 6 . Chacun a deux albums dans notre base de données.

En rapport: L'aide-mémoire des commandes SQL essentielles pour les débutants

Comment accéder aux données regroupées avec une fonction d'agrégat

Vous avez peut-être utilisé le COMPTER fonction avant, en particulier dans le COMPTER(*) forme comme vu 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égat. 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 albums multiples 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 sur 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égat 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ées.
  • MIN() renvoie la plus petite valeur dans une colonne donnée.
  • MAX() renvoie la plus grande valeur dans 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 :

windows 10 ne reconnait pas le disque dur externe
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 du 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 filtrez-vous l'ensemble de résultats après qu'un regroupement a eu lieu ? Les AYANT clause 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, il s'agit essentiellement d'un simple remplacement de 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 sorti 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 de synthèse de données, pour une page de sommaire, 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 pourrez utiliser la majeure partie de la puissance d'une base de données relationnelle.

Partager Partager Tweeter E-mail Comment interroger plusieurs tables de base de données à la fois avec des jointures SQL

Apprenez à utiliser les jointures SQL pour rationaliser les requêtes, gagner du temps et vous faire sentir comme un utilisateur expérimenté de SQL.

comment pirater un compte bancaire et transférer de l'argent en ligne
Lire la suite Rubriques connexes
  • La programmation
  • SQL
A propos de l'auteur Bobby Jack(58 articles publiés)

Bobby est un passionné de technologie qui a travaillé comme développeur de logiciels pendant près de deux décennies. Il est passionné par les jeux, travaillant comme rédacteur de critiques au Switch Player Magazine, et est immergé dans tous les aspects de la publication en ligne et du développement Web.

Plus de Bobby Jack

Abonnez-vous à notre newsletter

Rejoignez notre newsletter pour des conseils techniques, des critiques, des ebooks gratuits et des offres exclusives !

Cliquez ici pour vous abonner