Comment utiliser GROUP BY et ORDER BY en SQL

Introduction

Les bases de données Structured Query Language (SQL) peuvent stocker et gérer une grande quantité de données à travers de nombreuses tables. Avec de grands ensembles de données, il est important de comprendre comment trier les données, notamment pour analyser les ensembles de résultats ou organiser les données pour des rapports ou des communications externes.

Deux instructions courantes en SQL qui aident à trier vos données sont GROUP BY et ORDER BY. Une instruction GROUP BY trie les données en les regroupant en fonction des colonnes que vous spécifiez dans la requête et est utilisée avec des fonctions d’agrégation. Un ORDER BY vous permet d’organiser les ensembles de résultats par ordre alphabétique ou numérique et dans un ordre croissant ou décroissant.

Dans ce tutoriel, vous trierez les résultats des requêtes en SQL en utilisant les instructions GROUP BY et ORDER BY. Vous pratiquerez également la mise en œuvre des fonctions d’agrégation et de la clause WHERE dans vos requêtes pour trier les résultats encore davantage.

Prérequis

Pour suivre ce guide, vous aurez besoin d’un ordinateur exécutant un système de gestion de base de données relationnelle (SGBDR) qui utilise SQL. Les instructions et exemples de ce tutoriel ont été validés en utilisant l’environnement suivant :

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL installé et sécurisé sur le serveur. Suivez notre guide Comment installer MySQL sur Ubuntu 20.04 pour le configurer. Ce guide suppose que vous avez également configuré un utilisateur MySQL non root, comme décrit dans Étape 3 de ce guide.

Pour pratiquer le tri des résultats de données dans ce tutoriel, vous aurez besoin d’une base de données et d’une table chargées avec des données d’exemple. Si vous n’en avez pas déjà une prête à insérer, vous pouvez lire la section suivante Connexion à MySQL et configuration d’une base de données d’exemple pour apprendre comment créer une base de données et une table. Ce tutoriel fera référence à cette base de données d’exemple et à cette table tout au long.

Connexion à MySQL et configuration d’une base de données d’exemple

Si votre base de données SQL s’exécute sur un serveur distant, connectez-vous en SSH à votre serveur depuis votre machine locale :

ssh sammy@your_server_ip

Ensuite, ouvrez l’invite MySQL, en remplaçant sammy par les informations de votre compte utilisateur MySQL :

mysql -u sammy -p

Créez une base de données nommée movieDB :

CREATE DATABASE movieDB;

Si la base de données a été créée avec succès, vous recevrez la sortie suivante :

Output
Query OK, 1 row affected (0.01 sec)

Pour sélectionner la base de données movieDB, exécutez la déclaration USE suivante :

USE movieDB;

Output
Database changed

Après avoir sélectionné la base de données, créez une table à l’intérieur. Pour l’exemple de ce tutoriel, nous allons créer une table qui stocke des informations sur les projections d’un cinéma local. Cette table contiendra les sept colonnes suivantes :

• theater_id : stocke les valeurs du type de données int pour chaque salle de projection de cinéma, et servira de clé primaire de la table, ce qui signifie que chaque valeur de cette colonne fonctionnera comme un identifiant unique pour sa ligne respective.
• date : utilise le type de données DATE pour stocker la date spécifique par année, mois et jour où un film a été projeté. Ce type de données respecte les paramètres suivants : quatre chiffres pour l’année, et un maximum de deux chiffres pour le mois et le jour (AAAA-MM-JJ).
• time : représente la projection programmée du film avec le type de données TIME par heures, minutes et secondes (HH:MM:SS).
• movie_name : stocke le nom du film en utilisant le type de données varchar avec un maximum de 40 caractères.
• movie_genre: utilise le type de données varchar avec un maximum de 30 caractères, pour contenir des informations sur le genre respectif de chaque film.
• guest_total: montre le nombre total d’invités ayant assisté à une projection de film avec le type de données int.
• ticket_cost: utilise le type de données decimal, avec une précision de quatre et une échelle de un, ce qui signifie que les valeurs de cette colonne peuvent comporter quatre chiffres, et deux chiffres à droite du point décimal. Cette colonne représente le coût du billet pour la projection de film spécifique.

Créez une table nommée movie_theater qui contient chacune de ces colonnes en exécutant la commande CREATE TABLE suivante:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

Ensuite, insérez des données d’exemple dans la table vide:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Une fois que vous avez inséré les données, vous êtes prêt à commencer à trier les résultats de la requête en SQL.

En utilisant GROUP BY

La fonction d’une instruction GROUP BY est de regrouper des enregistrements avec des valeurs partagées. Une instruction GROUP BY est toujours utilisée avec une fonction d’agrégation dans une requête. Comme vous vous en souviendrez peut-être, une fonction d’agrégation résume les informations et renvoie un seul résultat. Par exemple, vous pouvez interroger le nombre total ou la somme d’une colonne et cela produira une seule valeur dans votre résultat. Avec une clause GROUP BY, vous pouvez implémenter la fonction d’agrégation pour obtenir une valeur de résultat pour chaque groupe souhaité.

GROUP BY est utile pour renvoyer plusieurs résultats souhaités triés par vos groupes spécifiés, plutôt que seulement une colonne. De plus, GROUP BY doit toujours venir après l’instruction FROM et la clause WHERE, si vous choisissez d’en utiliser une. Voici un exemple de structure de requête avec un GROUP BY et une fonction d’agrégation :

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

Pour illustrer comment vous pouvez utiliser les instructions GROUP BY, disons que vous dirigez la campagne pour plusieurs sorties de films et que vous voulez évaluer le succès de vos efforts de marketing. Vous demandez à un cinéma local de partager les données qu’ils ont collectées auprès des invités le vendredi et le samedi. Commencez par examiner les données en exécutant SELECT et le symbole * pour sélectionner « chaque colonne » de la table movie_theater :

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

Bien que ces données soient utiles, vous souhaitez effectuer une évaluation plus approfondie et trier les résultats pour certaines colonnes spécifiques.

Étant donné que vous avez travaillé sur des films de différents genres, vous êtes intéressé à savoir dans quelle mesure ils ont été bien accueillis par les spectateurs. Plus précisément, vous voulez connaître le nombre moyen de personnes ayant regardé chaque genre de film. Utilisez SELECT pour récupérer les différents types de films de la colonne movie_genre. Ensuite, appliquez la fonction d’agrégation AVG sur la colonne guest_total, utilisez AS pour créer un alias pour une colonne appelée average, et incluez l’instruction GROUP BY pour regrouper les résultats par movie_genre. En les regroupant de cette manière, vous obtiendrez les résultats moyens pour chaque genre de film :

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

Cet output fournit les quatre moyennes pour chaque genre au sein du groupe movie_genre. Selon ces informations, les films Action ont attiré le plus grand nombre moyen de spectateurs par séance.

Ensuite, supposons que vous vouliez mesurer les revenus du théâtre sur deux jours séparés. La requête suivante renvoie les valeurs de la colonne date, ainsi que les valeurs renvoyées par la fonction d’agrégation SUM. Plus précisément, la fonction d’agrégation SUM enveloppera une équation mathématique entre parenthèses pour multiplier (en utilisant l’opérateur *) le nombre total de spectateurs par le coût d’un billet, représenté comme suit : SUM(guest_total * ticket_cost). Cette requête inclut la clause AS pour fournir l’alias total_revenue pour la colonne renvoyée par la fonction d’agrégation. Ensuite, complétez la requête avec l’instruction GROUP BY pour regrouper les résultats de la requête par la colonne date:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Étant donné que vous avez utilisé GROUP BY pour regrouper la colonne date, votre sortie fournit les résultats pour le revenu total des ventes de billets pour chaque jour, dans ce cas, 7 272 $ pour le vendredi 27 mai et 9 646 $ pour le samedi 28 mai.

Maintenant, imaginez que vous souhaitez vous concentrer sur l’analyse d’un seul film : The Bad Guys. Dans ce scénario, vous voulez comprendre comment le moment et les prix influencent le choix d’une famille de regarder un film d’animation. Pour cette requête, utilisez la fonction d’agrégation MAX pour récupérer le prix_du_billet maximum, en veillant à inclure AS pour créer l’alias de la colonne donnees_de_prix. Ensuite, utilisez la clause WHERE pour restreindre les résultats par nom_du_film uniquement à « The Bad Guys », et utilisez AND pour déterminer également les heures de film les plus populaires en fonction du nombre de total_des_clients supérieur à 100 avec l’opérateur de comparaison >. Enfin, complétez la requête avec l’instruction GROUP BY et regroupez-la par heure:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

Selon cette sortie, plus de clients ont assisté au film The Bad Guys à la séance matinale de 9h00, qui avait un prix de billet plus abordable de 8,00 $. Cependant, ces résultats montrent également que les clients ont payé le prix du billet le plus élevé de 13,00 $ à 17h00, ce qui suggère que les familles préfèrent les séances qui ne sont pas trop tardives dans la journée et sont prêtes à payer un peu plus pour un billet. Ceci semble être une évaluation juste par rapport à l’heure de 22h00 où le film The Bad Guys n’a attiré que 83 clients et le prix par billet était de 18,00 $. Ces informations peuvent être utiles pour fournir au gestionnaire du cinéma des preuves que l’ouverture de plus de créneaux horaires pour les séances matinales et en début de soirée peut augmenter la fréquentation des familles qui font un choix en fonction d’une heure et d’un prix préférés.

Veuillez noter que même si GROUP BY est presque toujours utilisé avec une fonction d’agrégation, il peut y avoir des exceptions, bien que cela soit peu probable. Cependant, si vous souhaitez grouper vos résultats sans fonction d’agrégation, vous pouvez utiliser l’instruction DISTINCT pour obtenir le même résultat. Une clause DISTINCT supprime les doublons dans un ensemble de résultats en renvoyant les valeurs uniques de la colonne, et elle ne peut être utilisée qu’avec une instruction SELECT. Par exemple, si vous souhaitez regrouper tous les films par nom, vous pouvez le faire avec la requête suivante :

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

Comme vous vous en souvenez en ayant consulté toutes les données dans la table, il y avait des doublons des noms de film car il y avait plusieurs projections. Par conséquent, DISTINCT a supprimé ces doublons et a regroupé efficacement les valeurs uniques sous la colonne unique movie_name. Cela est effectivement identique à la requête suivante, qui inclut une instruction GROUP BY :

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Maintenant que vous avez pratiqué l’utilisation de GROUP BY avec des fonctions d’agrégation, vous apprendrez ensuite comment trier les résultats de votre requête avec l’instruction ORDER BY.

UTILISATION DE ORDER BY

La fonction de l’instruction ORDER BY est de trier les résultats par ordre croissant ou décroissant en fonction de la ou des colonnes que vous spécifiez dans la requête. En fonction du type de données stocké par la colonne que vous spécifiez après celle-ci, ORDER BY les organisera par ordre alphabétique ou numérique. Par défaut, ORDER BY triera les résultats par ordre croissant; si vous préférez l’ordre décroissant, cependant, vous devez inclure le mot-clé DESC dans votre requête. Vous pouvez également utiliser l’instruction ORDER BY avec GROUP BY, mais elle doit venir après pour fonctionner correctement. Tout comme GROUP BY, ORDER BY doit également venir après l’instruction FROM et la clause WHERE. La syntaxe générale pour utiliser ORDER BY est la suivante :

SELECT column_1, column_2 FROM table ORDER BY column_1;

Continuons avec les données d’exemple pour le cinéma et pratiquons le tri des résultats avec ORDER BY. Commencez par la requête suivante qui récupère les valeurs de la colonne guest_total et organise ces valeurs numériques avec une instruction ORDER BY:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

Étant donné que votre requête a spécifié une colonne avec des valeurs numériques, l’instruction ORDER BY a organisé les résultats par ordre numérique et croissant, en commençant par 25 sous la colonne guest_total.

Si vous préférez ordonner la colonne dans l’ordre décroissant, vous ajouteriez le mot-clé DESC à la fin de la requête. De plus, si vous souhaitiez ordonner les données par les valeurs de caractères sous movie_name, vous le spécifieriez dans votre requête. Effectuons ce type de requête en utilisant ORDER BY pour ordonner la colonne movie_name avec les valeurs de caractères dans l’ordre décroissant. Triez les résultats encore plus en incluant une clause WHERE pour récupérer les données sur les films diffusés à 22h00 dans la colonne time :

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

Cet ensemble de résultats répertorie les quatre différentes séances de films à 22h00 dans l’ordre alphabétique décroissant, en commençant par Top Gun Maverick jusqu’à Downtown Abbey A New Era.

Pour cette prochaine requête, combinez les déclarations ORDER BY et GROUP BY avec la fonction d’agrégat SUM pour générer des résultats sur le revenu total reçu pour chaque film. Cependant, disons que le cinéma a mal compté le nombre total de clients et a oublié d’inclure des soirées spéciales qui avaient acheté à l’avance et réservé des billets pour un groupe de 12 personnes à chaque séance.

Dans cette requête, utilisez SUM et incluez les 12 invités supplémentaires à chaque projection de film en implémentant l’opérateur d’addition +, puis ajoutez 12 au total des invités guest_total. Assurez-vous d’encadrer cela entre parenthèses. Ensuite, multipliez ce total par le ticket_cost avec l’opérateur *, et complétez l’équation mathématique en fermant la parenthèse à la fin. Ajoutez la clause AS pour créer l’alias de la nouvelle colonne intitulée total_revenue. Ensuite, utilisez GROUP BY pour regrouper les résultats de total_revenue pour chaque film en fonction des données récupérées dans la colonne movie_name. Enfin, utilisez ORDER BY pour organiser les résultats sous la nouvelle colonne total_revenue par ordre croissant :

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

Cet ensemble de résultats nous indique le chiffre d’affaires total pour chaque film avec les ventes de billets supplémentaires pour 12 invités et organise les ventes de billets totales dans l’ordre croissant, du plus bas au plus élevé. À partir de cela, nous apprenons que Top Gun Maverick a reçu le plus de ventes de billets, tandis que Men en a reçu le moins. Pendant ce temps, les films The Bad Guys et Downton Abbey A New Era étaient très proches en termes de ventes de billets totales.

Dans cette section, vous avez pratiqué diverses façons d’implémenter l’instruction ORDER BY et comment spécifier l’ordre que vous préférez, comme les ordres croissants et décroissants pour les valeurs de données caractère et numériques. Vous avez également appris comment inclure la clause WHERE pour affiner vos résultats, et effectué une requête en utilisant à la fois les instructions GROUP BY et ORDER BY avec une fonction d’agrégat et une équation mathématique.

Conclusion

Comprendre comment utiliser les instructions GROUP BY et ORDER BY est important pour trier vos résultats et vos données. Que vous souhaitiez organiser plusieurs résultats sous un même groupe, organiser l’une de vos colonnes par ordre alphabétique et décroissant, ou faire les deux simultanément ; c’est à vous et à vos résultats souhaités. Vous avez également appris d’autres façons de trier vos résultats encore plus loin avec la clause WHERE. Si vous souhaitez en savoir plus, consultez notre tutoriel sur Comment Utiliser les Jokers en SQL pour pratiquer le filtrage des résultats avec la clause LIKE.