5 exemples de GROUP BY

Lorsque vous commencez à apprendre SQL, vous rencontrez rapidement la clause GROUP BY. Le regroupement de données - ou agrégation de données - est un concept important dans le monde des bases de données. Dans cet article, nous allons vous montrer comment vous pouvez utiliser la clause GROUP BY dans la pratique. Nous avons rassemblé cinq exemples de GROUP BY, des plus simples aux plus complexes, afin que vous puissiez voir le regroupement de données dans un scénario réel. En prime, vous en saurez un peu plus sur les fonctions d'agrégation et la clause HAVING.

SQL est un langage universel pour parler aux bases de données qui existe depuis près de 50 ans. Si vous êtes un débutant complet, envisagez de suivre notre coursSQL pour les débutants avant de lire cet article.

L'un des concepts fondamentaux de SQL est le regroupement de données, ou agrégation de données. Si vous lisez cet article, vous avez probablement déjà entendu parler de la clause GROUP BY. Pour vous aider à mieux la comprendre, nous avons présenté cinq problèmes d'entreprise et montré comment ils peuvent être résolus dans nos exemples GROUP BY.

Si vous avez besoin d'une introduction rapide à GROUP BY, regardez notre vidéo YouTube de cinq minutes.

Dans cet article, nous allons aider un musée imaginaire à analyser ses visiteurs. Nous utiliserons l'historique des visites du musée pour obtenir des informations significatives à l'aide de la clause GROUP BY. Mettons-nous au travail !

Données d'entrée

Nous allons travailler avec une seule table nommée visit. Chaque ligne représente une seule visite au musée. Ci-dessous, vous pouvez voir quelques exemples de lignes de cette table :

visit

Comme vous pouvez le constater, le tableau n'est pas très compliqué. Il ne contient que trois colonnes :

• date - Ladate de la visite au musée.
• prix - Leprix payé pour le billet en dollars (vous verrez une variété de prix parce que le musée utilise différentes options de prix pour différents jours ainsi que de nombreux types de billets réduits).
• durée - Ladurée de la visite du musée en minutes.

À propos, si vous connaissez déjà les instructions SQL de base et que vous souhaitez plutôt créer des tableaux, jetez un coup d'œil à ce cours sur les bases des tableaux à l'adresseLearnSQL.fr .

Pourquoi regrouper les lignes ?

Nous savons que nous pouvons agréger (regrouper) des lignes en SQL, mais pourquoi le faisons-nous ? La clause GROUP BY est généralement utilisée avec les fonctions d'agrégation, qui calculent diverses statistiques sur les groupes de lignes. Les cinq fonctions d'agrégation les plus courantes en SQL sont les suivantes :

• COUNT()-Utilisées pour compter le nombre de lignes.
• AVG()-Utilisées pour trouver la valeur moyenne.
• MIN() et MAX()- Utilisées pour trouver la valeur minimale et maximale, respectivement.
• SUM()-Utilisé pour trouver la somme de toutes les valeurs.

En bref, nous regroupons les lignes pour calculer diverses statistiques.

Exemples de GROUP BY

Bien. Maintenant que nous connaissons un peu les fonctions d'agrégation, examinons cinq exemples de GROUP BY.

Exemple 1 : GROUP BY avec une seule colonne

Nous allons commencer par un exemple simple. Nous voulons savoir combien de personnes ont visité le musée chaque jour. En d'autres termes, pour chaque date, nous allons afficher le nombre de visites au musée. La requête dont nous avons besoin ressemblera à ceci :

SELECT date, COUNT(*)
FROM visit
GROUP BY date;

Nous n'avons que deux colonnes : date et count. COUNT(*) signifie "tout compter". Comme nous utilisons également la colonne date dans la clause GROUP BY, nous verrons un compte séparé pour chaque date. Lorsque nous exécutons la requête dans notre base de données, nous devrions voir quelque chose comme ceci :

Excellent. Nous savons maintenant combien de personnes ont visité le musée chaque jour.

Au lieu de COUNT(*), qui signifie "compter chaque ligne", nous pouvons également utiliser un nom de colonne, par exemple COUNT(duration). La différence est que COUNT(*) compte toutes les lignes pour un groupe donné, même si certaines lignes contiennent des valeurs NULL (inconnues). COUNT(duration) En revanche, duration ne compte que les lignes qui ont une valeur non NULL dans la colonne . Dans notre tableau, cependant, il n'y a pas de valeurs NULL, donc le résultat serait le même.

Exemple 2 : GROUP BY avec deux colonnes

Nous voulons maintenant connaître le prix moyen payé pour un billet au cours d'un mois donné. Pour cela, nous avons besoin d'une requête plus complexe. Regardez :

SELECT 
  EXTRACT(YEAR FROM date) AS year, 
  EXTRACT(MONTH FROM date) AS month, 
  ROUND(AVG(price), 2) AS avg_price
FROM visit
GROUP BY
  EXTRACT(YEAR FROM date),
  EXTRACT(MONTH FROM date);

Nous avons utilisé la fonction EXTRACT(YEAR FROM date) pour obtenir l'année de chaque date, et nous avons utilisé EXTRACT(MONTH FROM date) pour obtenir le mois (sous forme de valeur numérique, où "1" signifie "janvier", "2" signifie "février", etc.) Notez qu'ils sont traités comme des colonnes distinctes, vous verrez donc l'année dans une colonne et le mois dans une autre.

Comme nous voulons voir la moyenne pour chaque mois de chaque année séparément, nous devons également regrouper par ces deux colonnes. Nous devons répéter les mêmes fonctions dans la clause GROUP BY.

La troisième colonne est une combinaison de deux fonctions. À l'intérieur, nous avons AVG(price), qui calcule le prix moyen dans chaque groupe. Nous avons également ROUND(AVG(price), 2)) pour arrondir la valeur moyenne à deux décimales.

Lorsque vous utilisez une clause GROUP BY, essayez de vous souvenir de la règle d'or : Tous les noms de colonnes de la clause SELECT doivent apparaître dans la clause GROUP BY ou être utilisés dans les fonctions d'agrégation. Dans ce cas, EXTRACT(YEAR FROM date) et EXTRACT(MONTH FROM date) doivent apparaître dans la clause GROUP BY. Si vous oubliez l'un d'entre eux, vous obtiendrez probablement une erreur. La troisième colonne utilise une fonction d'agrégation, AVG(price), qui n'est donc pas mentionnée dans la clause GROUP BY.
Il existe quelques exceptions à cette règle, qui peuvent entraîner un comportement inattendu.

Lorsque nous exécutons la requête, nous obtenons quelque chose comme ceci :

Comme vous pouvez le voir, le prix moyen des billets a diminué en juin, par rapport à mai. Cela pourrait se traduire par une baisse de revenus pour le musée.

Exemple 3 : GROUP BY et ORDER BY

Cette fois, nous voulons trouver la valeur de la durée moyenne des visites pour chaque mois. Nous voulons également nous assurer que les lignes sont triées par ordre chronologique. La requête dont nous aurons besoin sera similaire à celle de l'exemple précédent :

SELECT 
  EXTRACT(YEAR FROM date) AS year, 
  EXTRACT(MONTH FROM date) AS month, 
  ROUND(AVG(duration), 2)
FROM visit
GROUP BY 
  EXTRACT(YEAR FROM date), 
  EXTRACT(MONTH FROM date)
ORDER BY 
  EXTRACT(YEAR FROM date), 
  EXTRACT(MONTH FROM date);

La nouvelle pièce ici est la clause ORDER BY. Une clause ORDER BY nous permet de spécifier l'ordre dans lequel nous devons voir les lignes. Dans ce cas, nous voulons voir toutes les lignes triées d'abord par année, puis par mois.

Encore une fois, nous devons répéter les mêmes fonctions de la clause SELECT pour que la clause ORDER BY fonctionne. Par défaut, ORDER BY trie les lignes dans l'ordre croissant.

Si vous souhaitez que les lignes soient triées par ordre décroissant, vous devez ajouter le mot-clé DESC après le nom de la colonne. Par exemple, écrivez ORDER BY EXTRACT(YEAR FROM date) DESC. Vous pouvez en savoir plus sur la différence entre GROUP BY et ORDER BY dans cet article.

Lorsque nous exécutons la requête, nous obtenons quelque chose comme ceci :

En moyenne, un visiteur a passé plus de temps dans le musée en juin qu'en mai. C'est une bonne nouvelle !

Exemple 4 : GROUP BY et HAVING

Nous avons maintenant le problème suivant : nous voulons connaître le prix moyen des billets pour chaque jour. Cependant, il y a une condition supplémentaire : nous ne voulons pas afficher les jours avec 3 visites ou moins. Cette condition se traduit par une nouvelle pièce dans notre requête SQL. Jetez-y un coup d'œil :

SELECT 
  date, 
  ROUND(AVG(price), 2) AS avg_price
FROM visit
GROUP BY date
HAVING COUNT(*) > 3
ORDER BY date;

La nouvelle partie est HAVING COUNT(*) > 3. HAVING est une clause que nous pouvons utiliser pour filtrer les lignes groupées. Dans ce cas, nous regroupons les lignes par la date (GROUP BY date). Lorsque nous faisons cela, nous voulons nous assurer qu'un groupe donné a plus de trois lignes (HAVING COUNT(*) > 3). Si un groupe (dans ce cas, les visites d'un jour donné) ne remplit pas cette condition, nous ne l'affichons pas du tout.

Lorsque nous exécutons la requête, nous obtenons quelque chose comme ceci :

Exemple 5 : GROUP BY, HAVING, et WHERE

Enfin, nous avons le problème suivant à résoudre : nous voulons afficher la durée moyenne des visites pour chaque jour. Encore une fois, nous ne voulons afficher que les jours avec plus de trois visites. Cependant, nous voulons également nous assurer que les visites d'une durée de cinq minutes ou moins ne sont pas incluses dans les calculs. Il s'agit très probablement de tests effectués par les employés du musée, nous voulons donc les ignorer. Voici la requête dont nous aurons besoin :

SELECT 
  date, 
  ROUND(AVG(duration), 2) AS avg_duration
FROM visit
WHERE duration > 5
GROUP BY date
HAVING COUNT(*) > 3
ORDER BY date;

La nouvelle partie ici est la clause WHERE. Elle est utilisée pour n'inclure que les visites qui ont duré plus de cinq minutes.

Les clauses WHERE et HAVING peuvent sembler similaires, mais il existe une différence entre elles : WHERE est utilisé pour filtrer des lignes individuelles avant qu'elles ne soient regroupées (c'est-à-dire des visites individuelles), tandis que HAVING est utilisé pour filtrer des groupes de lignes (c'est-à-dire des visites un jour donné). Pour en savoir plus , lisez cet article.

Lorsque nous exécutons la requête, nous obtenons quelque chose comme ceci :

Notez comment la durée moyenne des visites augmente avec presque tous les jours du mois de juin. Il semble que nous ayons ajouté une exposition intéressante et que nos invités l'aient fait connaître.

Résumé et suivi

Avec ces cinq exemples, nous sommes passés de cas faciles à des cas plus complexes GROUP BY. La polyvalence de SQL nous a permis d'analyser les visites du musée et de répondre à plusieurs questions à leur sujet. Cela montre l'efficacité avec laquelle GROUP BY peut résoudre des problèmes commerciaux réels.

Si vous souhaitez en savoir plus sur la clause GROUP BY, notre rédactrice en chef, Agnieszka, a rédigé un article complet disponible ici.

Si vous avez envie d'apprendre plus de SQL, jetez un coup d'oeil à LearnSQL.fr. LearnSQL.fr L'équipe enseigne SQL à partir de zéro, de manière totalement interactive.

Pour les débutants, nous proposons notre cours le plus vendu, SQL pour les débutants. Nous garantissons une entrée en douceur dans le monde du codage pour les personnes sans expérience préalable en informatique. Vous n'aurez pas à vous soucier de la configuration technique : vous étudierez directement à partir de votre navigateur Web. Nous nous occupons de la base de données pendant que vous vous concentrez sur les concepts clés de SQL.

Si vous souhaitez en savoir plus sur les avantages d'apprendre avec nous avant d'acheter un abonnement, consultez notre article : Pourquoi suivre le cours SQL pour les débutants chez LearnSQL.com. Cet article explique en détail pourquoi nous avons créé ce cours, ce qu'il contient et la philosophie qui le sous-tend.