03_ Opérations entre les enregistrements

[[02_ Requêtes sur une seule table]] # Opérations sur plusieurs enregistrements - Les fonctions d'agrégat ## Introduction Jusqu'à présent, vous avez appris à interroger et manipuler des données enregistrement par enregistrement, en sélectionnant des champs spécifiques, en filtrant des enregistrements avec `WHERE`, et en ordonnant les résultats. Ces compétences sont fondamentales, mais le véritable pouvoir de SQL réside souvent dans sa capacité à **synthétiser et analyser des ensembles de données**. Dans le monde réel, il est rare de vouloir uniquement consulter des enregistrements individuelles. Vous aurez plutôt besoin de savoir : * "Combien d'étudiants y a-t-il dans chaque département ?" * "Quelle est la note moyenne des étudiants inscrits cette année ?" * "Quel est le budget total alloué à tous les projets ?" Pour répondre à ce type de questions, SQL propose des **fonctions d'agrégat** dont la clause `GROUP BY`. Ce chapitre vous guidera à travers ces concepts essentiels, vous permettant de passer d'une vision individuelle à une vision agrégée de vos données. > [!note] Prérequis et Contexte > Ce chapitre suppose que vous maîtrisez les bases de SQL, notamment les clauses `SELECT`, `FROM`, `WHERE`, et `ORDER BY`. Les compétences acquises ici vous prépareront aux requêtes SQL plus complexes, incluant les sous-requêtes et les jointures avancées, qui seront abordées dans les modules de SQL intermédiaire. --- ## 1. Introduction aux Opérations d'Agrégat Les opérations d'agrégat sont des fonctions qui opèrent sur un **ensemble d'enregistrements** (un groupe ou la totalité des enregistrements retournées par le `FROM` d'une requête) et retournent une **unique valeur scalaire**. Elles sont indispensables pour obtenir des statistiques, des totaux, des moyennes, et d'autres indicateurs synthétiques à partir de vos données brutes. > [!definition] Fonction d'agrégat > Une fonction d'agrégat, ou fonction d'agrégation, est une fonction SQL qui prend en entrée une collection de valeurs (souvent toutes les valeurs d'un champ pour un groupe d'enregistrement) et retourne une seule valeur qui résume ou calcule une propriété de cette collection. --- ## 2. La Clause `GROUP BY` : Regrouper les Données La clause `GROUP BY` est la pierre angulaire des opérations d'agrégat. Elle permet de diviser l'ensemble des enregistrements d'une table (ou le résultat d'une clause `FROM`/`WHERE`) en groupes, afin d'appliquer une fonction d'agrégat à chaque groupe indépendamment. ### 2.1. Concept Imaginez que vous avez une table d'étudiants avec leur département et leur note moyenne. Si vous voulez connaître la note moyenne *par département*, vous devez d'abord regrouper tous les étudiants appartenant au même département, puis calculer la moyenne pour chaque groupe. `GROUP BY` va permettre ce regroupement. > [!note] Principe de fonctionnement > 1. Les enregistrements sont sélectionnés (après `FROM` et `WHERE`). > 2. Ces enregistrements sont ensuite regroupés en fonction des valeurs d'un ou plusieurs champs spécifiées dans la clause `GROUP BY`. > 3. Pour chaque groupe ainsi formé, les fonctions d'agrégat sont appliquées. ### 2.2. Syntaxe La clause `GROUP BY` est utilisée conjointement avec `SELECT` et les fonctions d'agrégat. ```sql SELECT champ_groupement_1, [champ_groupement_2, ...], fonction_agregat(expression_1), [fonction_agregat(expression_2), ...] FROM nom_table [WHERE condition_filtre_enregistrements] GROUP BY champ_groupement_1 [, champ_groupement_2, ...]; ``` > [!warning] Règle fondamentale de `GROUP BY` > **Tout champ présent dans la clause `SELECT` qui n'a PAS de fonction d'agrégat associée DOIT impérativement figurer dans la clause `GROUP BY`.** > > Si vous sélectionnez un champ non agrégée sans la spécifier dans `GROUP BY`, le SGBD ne saurait pas quelle valeur afficher pour ce champ, car il y aurait potentiellement plusieurs valeurs pour un même groupe. ### 2.3. Exemple Simple Considérons une table `Etudiants` avec les champs `id_etudiant`, `nom`, `prenom`, `id_departement`, `note_moyenne`. | id_etudiant | nom | prenom | id_departement | note_moyenne | | :---------- | :------ | :----- | :------------- | :----------- | | 1 | Dupont | Jean | 101 | 14.5 | | 2 | Martin | Sophie | 102 | 16.0 | | 3 | Durand | Pierre | 101 | 12.0 | | 4 | Petit | Alice | 103 | 15.5 | | 5 | Leroy | Paul | 102 | 13.0 | | 6 | Bernard | Marie | 101 | 17.0 | | 7 | Robert | Julie | 103 | 11.5 | **Question :** Compter le nombre d'étudiants par département. ```sql SELECT id_departement, COUNT(id_etudiant) AS nombre_etudiants FROM Etudiants GROUP BY id_departement; ``` **Exécution de la requête :** 1- Après le `FROM`, comme celui-ci ne contient que la table `Etudiants`, nous obtenons cette table : | id_etudiant | nom | prenom | id_departement | note_moyenne | | :---------- | :------ | :----- | :------------- | :----------- | | 1 | Dupont | Jean | 101 | 14.5 | | 2 | Martin | Sophie | 102 | 16.0 | | 3 | Durand | Pierre | 101 | 12.0 | | 4 | Petit | Alice | 103 | 15.5 | | 5 | Leroy | Paul | 102 | 13.0 | | 6 | Bernard | Marie | 101 | 17.0 | | 7 | Robert | Julie | 103 | 11.5 | *Ici un enregistrement est un étudiant de la table `Etudiants`* 2- Après le `GROUP BY`, nous avons regroupé les enregistrements ayant le même numéro de département. Pour ne former qu'une seul enregistrement. Cela donne : | id_etudiant | nom | prenom | id_departement | note_moyenne | | :---------- | :-------------------------- | :---------------------- | :------------- | :------------------- | | 1 3 6 | Dupont Durand Bernard | Jean Pierre Marie | 101 | 14.5 12.0 17.0 | | 2 5 | Martin Leroy | Sophie Paul | 102 | 16.0 13.0 | | 4 7 | Petit Robert | Alice Julie | 103 | 15.5 11.5 | Vous remarquez que les champs n'étant pas dans le `GROUP BY` ont toutes les valeurs des enregistrements regroupés. >[!important] >Si nous appelons un de ces champs dans le `SELECT` sans lui associer une opération d'agrégat, la requête retournera, selon le SGBD utilisé, une erreur ne sachant pas quelle valeur retourner ou la première valeur trouvée (ce qui est encore plus dangereux). >Cela explique la règle fondamentale vue au point [[#2.2. Syntaxe]] *IMPORTANT : Avec le regroupement, un enregistrement n'est plus un étudiant de la table `Etudiants` mais un numéro de département et donc, par extension, un département.* 3- Après le `SELECT`, nous obtenons le résultat final : | id_departement | nombre_etudiants | | :------------- | :--------------- | | 101 | 3 | | 102 | 2 | | 103 | 2 | Dans l'opération d'agrégat `COUNT`, nous comptons le nombre de valeur présente dans le champ `id_etudiant`. il n'y a donc plus qu'une seule valeur à afficher. *Un enregistrement correspond toujours à un département pour lequel nous avons le nombre d'étudiants* >[!Important] pourquoi `COUNT(id_etudiant)` donne le nombre d'étudiants ? > >L'erreur serait de se baser sur le nom du champ ! > >Voici le raisonnement à tenir : > 1- L'opération compte le nombre de valeurs trouvées dans le champ `id_etudiant` > 2- ce nombre correspond donc à un nombre d'enregistrements ayant le même numéro de département > 3- Au départ, un enregistrement représente un étudiant de la table `Etudiants` donc au compte des étudiants > 4- comme on utilise l'identifiant de la table, on est certain de compter tous les enregistrements (il y a forcement une valeur). donc on compte tous les étudiants de chaque département > > - Si vous n'avez pas réussi à tout comprendre, pas d'inquiétude, cela est réexpliqué juste après et la pratique d'exercices vous permettra de mieux assimiler ce raisonnement. > - Si vous pensez avoir compris, essayer d'expliquer ce que nous aurions compté si nous avions mis `COUNT(note_moyenne)` à la place. (la réponse en en dessous 😉 ) ### 2.4. Regroupement sur plusieurs champs Vous pouvez regrouper les données sur plusieurs champs. Chaque combinaison unique des valeurs de ces champs formera un groupe distinct. ### 2.5. Gestion des valeurs `NULL` dans `GROUP BY` Les valeurs `NULL` dans le champ de groupement sont traitées comme une catégorie distincte. Tous les enregistrements ayant `NULL` pour le champ de groupement seront regroupées ensemble dans un seul groupe `NULL`. --- ## 3. Les Fonctions d'Agrégat Standard Il existe plusieurs fonctions d'agrégat standard. Elles sont essentielles pour toute analyse de données. ### 3.1. `COUNT()` : Compter les enregistrements La fonction `COUNT()` est utilisée pour ***compter un nombre d'enregistrements***. #### 3.1.1. `COUNT(*)` Compte le nombre total d'enregistrements dans un groupe, y compris les enregistrements contenant des valeurs `NULL`. ```sql SELECT COUNT(*) AS nb_Etudiants FROM Etudiants; ``` Cette requête compte le nombre d'enregistrements présents dans la table `Etudiants`. Par définition un enregistrement de cette table est un étudiant donc nous comptons le nombre d'étudiants. #### 3.1.2. `COUNT(nom_champ)` Compte le nombre de valeurs **non `NULL`** pour le champ spécifiée. Supposons qu'un étudiant puisse ne pas avoir de note_moyenne (valeur NULL) ```sql SELECT COUNT(note_moyenne) AS etudiant_avec_moyenne FROM Etudiants; ``` Cette requête compte le nombre d'enregistrements présents dans la table `Etudiants`**pour lesquels, le champ `note_moyenne` contient une valeur non `NULL`**. Par définition un enregistrement de cette table est un étudiant donc nous comptons le nombre d'étudiants dont on connait la moyenne. > [!example] Exemple avec `COUNT()` et `GROUP BY` > **Question :** Pour chaque département, compter le nombre total d'étudiants et le nombre d'étudiants ayant une note moyenne renseignée. > > ```sql > SELECT > id_departement, > COUNT(*) AS total_etudiants, > COUNT(note_moyenne) AS etudiants_avec_note, > FROM > Etudiants > GROUP BY > id_departement; > ``` > > ### 3.2. `SUM()` : Calculer la somme La fonction `SUM()` calcule la somme de toutes les valeurs (non `NULL`) d'un champ numérique dans un groupe. > [!warning] Type de données > `SUM()` ne peut être appliquée qu'à des champs de type numérique. ```sql -- Supposons un champ 'credits_obtenus' SELECT id_departement, SUM(credits_obtenus) AS total_credits_departement FROM Etudiants GROUP BY id_departement; ``` >[!important] `SUM()` et `NULL` >Si certains champs ont des valeurs `NULL`, ils sont ignorés. >Si **tous** les champs ont des valeurs `NULL`, la fonction `SUM()` retourne une valeur `NULL`. ### 3.3. `AVG()` : Calculer la moyenne La fonction `AVG()` calcule la moyenne arithmétique de toutes les valeurs (non `NULL`) d'un champ numérique dans un groupe. > [!theorem] Formule de la moyenne > La moyenne est calculée comme suit : > $ \bar{x} = \frac{\sum_{i=1}^{n} x_i}{n} $ > où $x_i$ sont les valeurs non `NULL` et $n$ est le nombre de ces valeurs. ```sql SELECT id_departement, AVG(note_moyenne) AS moyenne_notes_departement FROM Etudiants GROUP BY id_departement; ``` ### 3.4. `MIN()` : Trouver la valeur minimale La fonction `MIN()` retourne la valeur minimale (non `NULL`) d'un champ dans un groupe. Elle peut être appliquée à des types numériques, chaînes de caractères (ordre alphabétique), et dates. ```sql SELECT id_departement, MIN(note_moyenne) AS note_min_departement, MIN(date_inscription) AS premiere_inscription_departement FROM Etudiants GROUP BY id_departement; ``` ### 3.5. `MAX()` : Trouver la valeur maximale La fonction `MAX()` retourne la valeur maximale (non `NULL`) d'un champ dans un groupe. Elle peut être appliquée à des types numériques, chaînes de caractères (ordre alphabétique), et dates. ```sql SELECT id_departement, MAX(note_moyenne) AS note_max_departement, MAX(date_inscription) AS derniere_inscription_departement FROM Etudiants GROUP BY id_departement; ``` > [!tip] Comportement des `NULL` > Toutes les fonctions d'agrégat (`COUNT(expression)`, `SUM`, `AVG`, `MIN`, `MAX`) **ignorent les valeurs `NULL`** par défaut. Seul `COUNT(*)` compte les enregistrements, qu'elles contiennent des `NULL` ou non. --- ## 4. La Clause `HAVING` : Filtrer les Groupes Une fois que les enregistrements ont été regroupés et les agrégats calculés, vous pourriez vouloir filtrer ces groupes en fonction des valeurs agrégées. C'est le rôle de la clause `HAVING`. ### 4.1. RAPPEL : `WHERE` vs. `HAVING` : Une Distinction Cruciale C'est l'une des confusions les plus courantes pour les débutants. > [!definition] Différence `WHERE` et `HAVING` > * La clause `WHERE` filtre les **enregistrements ** *avant* qu'ils ne soient regroupés. Elle ne peut pas utiliser de fonctions d'agrégat. > * La clause `HAVING` filtre les **groupes** *après* que les fonctions d'agrégat ont été appliquées. Elle utilise des fonctions d'agrégat. ### 4.2. Syntaxe ```sql SELECT champ_groupement, fonction_agregat(expression) FROM nom_table [WHERE condition_filtre_enregistrements] GROUP BY champ_groupement HAVING condition_filtre_groupes; -- Cette condition inclut des fonctions d'agrégat ``` ### 4.3. Exemple **Question :** Afficher les départements qui ont plus de 2 étudiants. ```sql SELECT id_departement, COUNT(id_etudiant) AS nombre_etudiants FROM Etudiants GROUP BY id_departement HAVING COUNT(id_etudiant) > 2; ``` > [!example] Résultat de l'exemple > | id_departement | nombre_etudiants | > | :------------- | :--------------- | > | 101 | 3 | --- Nous pouvons attaquer les requêtes sur 2 tables [[04_Requêtes sur 2 tables]]