Agrégation et Groupement - Transformer les Données en Connaissances
1. Le Problème : Passer des Données Brutes aux Indicateurs Clés
Jusqu'à présent, nos requêtes (SELECT, JOIN) nous ont permis d'extraire et de combiner des lignes de données brutes. C'est utile, mais les décideurs et les analystes ont rarement besoin de voir des milliers de lignes. Ils posent des questions de plus haut niveau :
"Combien d'utilisateurs avons-nous au total ?"
"Quel est le prix moyen de nos produits ?"
"Quel est le chiffre d'affaires total par catégorie de produits ?"
Pour répondre à ces questions, nous devons résumer ou agréger nos données. C'est le rôle des fonctions d'agrégation et de la clause GROUP BY.
2. Les Fonctions d'Agrégation : La Calculatrice de SQL
Les fonctions d'agrégation prennent un ensemble de lignes en entrée et retournent une valeur unique en sortie.
Analogie
C'est comme utiliser les fonctions SOMME, MOYENNE ou NB sur une colonne dans un tableur Excel.
Fonction
Description
Question type
COUNT(colonne)
Compte le nombre de lignes où la colonne n'est pas NULL. COUNT(*) compte toutes les lignes.
"Combien de commandes ont été passées ?"
SUM(colonne)
Calcule la somme des valeurs d'une colonne numérique.
"Quel est le chiffre d'affaires total ?"
AVG(colonne)
Calcule la moyenne des valeurs d'une colonne numérique.
"Quel est le prix moyen d'un article ?"
MAX(colonne)
Trouve la valeur maximale dans une colonne.
"Quelle est la commande la plus chère ?"
MIN(colonne)
Trouve la valeur minimale dans une colonne.
"Quelle est la commande la moins chère ?"
Exemple : Obtenir des statistiques globales sur une table produits
SELECT
COUNT(*) AS nombre_total_produits,
AVG(prix) AS prix_moyen,
SUM(stock) AS stock_total,
MAX(prix) AS produit_le_plus_cher,
MIN(prix) AS produit_le_moins_cher
FROM produits;
AS est utilisé pour donner un alias (un nom plus lisible) à la colonne de résultat.
3. GROUP BY : Agréger par Catégories
Les fonctions d'agrégation seules sont utiles, mais leur véritable puissance se révèle lorsqu'on les combine avec la clause GROUP BY. GROUP BY permet de diviser les lignes en groupes et d'appliquer une fonction d'agrégation à chaque groupe séparément.
Analogie
Vous avez un panier de fruits mélangés.
COUNT(*) vous dit combien de fruits vous avez au total.
GROUP BY type_fruit vous permet de faire des tas (un tas de pommes, un tas de bananes...), puis COUNT(*) vous dira combien de fruits vous avez dans chaque tas.
Ordre d'Exécution Logique d'une Requête
Pour bien comprendre GROUP BY, il faut connaître l'ordre logique dans lequel la base de données traite une requête :
FROM (et JOIN) : Rassemble les données des tables.
WHERE : Filtre les lignes individuelles.
GROUP BY : Regroupe les lignes restantes.
HAVING : Filtre les groupes entiers.
SELECT : Sélectionne les colonnes finales et applique les fonctions d'agrégation.
ORDER BY : Trie le résultat final.
LIMIT : Limite le nombre de lignes retournées.
Exemple : Calculer le nombre de commandes et le chiffre d'affaires par utilisateur
SELECT
utilisateur_id,
COUNT(id) AS nombre_de_commandes,
SUM(montant) AS chiffre_affaires_total
FROM commandes
GROUP BY utilisateur_id
ORDER BY chiffre_affaires_total DESC;
Cette requête va :
Prendre la table commandes.
Créer des groupes de lignes, un pour chaque utilisateur_id distinct.
Pour chaque groupe, calculer COUNT(id) et SUM(montant).
Afficher le résultat, trié par le chiffre d'affaires.
"Montre-moi uniquement les utilisateurs qui ont dépensé plus de 100€ au total."
SELECT
utilisateur_id,
SUM(montant) AS chiffre_affaires_total
FROM commandes
-- WHERE SUM(montant) > 100 -- ❌ CECI EST ILLÉGAL ! WHERE ne connaît pas les agrégats.
GROUP BY utilisateur_id
HAVING SUM(montant) > 100; -- ✅ On filtre les groupes dont la somme est supérieure à 100.
SELECT
cl.ville,
COUNT(c.id) AS nombre_commandes,
SUM(c.montant_total) AS chiffre_affaires
FROM Clients cl
INNER JOIN Commandes c ON cl.id = c.client_id
GROUP BY cl.ville
ORDER BY chiffre_affaires DESC;
Exemple 2 : Produits les plus vendus
SELECT
p.nom_produit,
COUNT(dc.commande_id) AS nombre_ventes,
SUM(dc.quantite) AS quantite_totale
FROM Produits p
INNER JOIN Details_Commande dc ON p.id = dc.produit_id
GROUP BY p.id, p.nom_produit
ORDER BY quantite_totale DESC
LIMIT 5;
Exemple 3 : Clients VIP (plus de 2 commandes)
SELECT
cl.nom,
cl.email,
COUNT(c.id) AS nombre_commandes,
SUM(c.montant_total) AS total_depense
FROM Clients cl
INNER JOIN Commandes c ON cl.id = c.client_id
GROUP BY cl.id, cl.nom, cl.email
HAVING COUNT(c.id) > 2
ORDER BY total_depense DESC;
6. Fonctions de Fenêtre (Window Functions) : Un Aperçu
Les fonctions de fenêtre sont un concept avancé mais extrêmement puissant. Contrairement aux fonctions d'agrégation qui réduisent plusieurs lignes en une seule, les fonctions de fenêtre effectuent un calcul sur un ensemble de lignes (une "fenêtre") mais retournent une valeur pour chaque ligne d'origine.
Exemple : Classer les produits par prix au sein de chaque catégorie
SELECT
nom_produit,
categorie,
prix,
RANK() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang_par_categorie
FROM produits;
Cette requête va :
Créer des "fenêtres" de lignes, une pour chaque categorie.
À l'intérieur de chaque fenêtre, trier les produits par prix décroissant.
Attribuer un rang à chaque produit, mais ce rang est réinitialisé pour chaque nouvelle catégorie.
Principales fonctions de fenêtre
ROW_NUMBER() : Numéro de ligne unique
RANK() : Rang avec égalités (peut sauter des rangs)
DENSE_RANK() : Rang sans sauter de valeurs
LAG() / LEAD() : Accéder aux lignes précédentes/suivantes
7. Erreurs Courantes et Bonnes Pratiques
⚠️ Colonnes non agrégées dans SELECT
Lorsque vous utilisez GROUP BY, toutes les colonnes dans le SELECT doivent être soit dans le GROUP BY, soit dans une fonction d'agrégation.
-- ❌ ERREUR
SELECT utilisateur_id, nom, COUNT(*)
FROM commandes
GROUP BY utilisateur_id;
-- ✅ CORRECT
SELECT utilisateur_id, COUNT(*)
FROM commandes
GROUP BY utilisateur_id;
⚠️ Confusion WHERE vs HAVING
Utilisez WHERE pour filtrer avant l'agrégation, HAVING pour filtrer après.
-- Filtrer les commandes de 2024, puis grouper
SELECT utilisateur_id, COUNT(*) as nb_commandes
FROM commandes
WHERE YEAR(date_commande) = 2024
GROUP BY utilisateur_id
HAVING COUNT(*) > 5;
Bonnes pratiques
Donnez toujours des alias clairs à vos agrégations
Utilisez COUNT(*) pour compter les lignes, COUNT(colonne) pour compter les valeurs non NULL
Combinez GROUP BY avec ORDER BY pour obtenir des résultats triés
Pensez performance : les agrégations sur de grandes tables peuvent être lentes sans index appropriés
8. À Vous de Jouer : Exercices Pratiques
📝 Exercice 1
Calculez le prix moyen des produits pour chaque catégorie de la table Produits.
Indice : Utilisez AVG() et GROUP BY.
📝 Exercice 2
Trouvez les 3 clients qui ont dépensé le plus d'argent au total.
Indice : Joignez Clients et Commandes, utilisez SUM(), GROUP BY, ORDER BY DESC et LIMIT 3.
📝 Exercice 3
Listez toutes les catégories qui ont plus de 2 produits.