Agrégats et index

Ce que tu vas apprendre

• Résumer plusieurs lignes en une valeur avec COUNT, SUM, AVG, MIN et MAX.
• Regrouper avec GROUP BY et filtrer les groupes avec HAVING.
• Savoir ce qu'est un index et quand en créer un.

Les fonctions d'agrégat

Une fonction d'agrégat prend plusieurs lignes et renvoie une seule valeur.

SELECT COUNT(*) AS nb_recettes,
       AVG(temps_minutes) AS duree_moyenne,
       MAX(temps_minutes) AS plus_longue
FROM recettes;

Résultat : une seule ligne, avec le nombre de recettes, la durée moyenne et la durée maximale.

Un WHERE s'applique avant le calcul : SELECT COUNT(*) FROM recettes WHERE temps_minutes < 60; compte seulement les recettes rapides.

GROUP BY : un résultat par groupe

Combien de recettes par catégorie ?

SELECT c.nom, COUNT(r.id) AS nb_recettes
FROM categories AS c
LEFT JOIN recettes AS r ON r.categorie_id = c.id
GROUP BY c.nom
ORDER BY nb_recettes DESC;

GROUP BY forme un groupe de lignes par valeur de c.nom, et l'agrégat se calcule dans chaque groupe. Le LEFT JOIN garde la catégorie « Boissons », avec un compte de 0.

Remarque le COUNT(r.id) plutôt que COUNT(*). Pour une catégorie vide, la ligne existe grâce au LEFT JOIN, mais r.id vaut NULL et n'est pas compté.

HAVING : filtrer les groupes

WHERE filtre les lignes avant le regroupement. Pour filtrer sur le résultat d'un agrégat, il faut HAVING :

SELECT c.nom, AVG(r.temps_minutes) AS duree_moyenne
FROM categories AS c
JOIN recettes AS r ON r.categorie_id = c.id
GROUP BY c.nom
HAVING AVG(r.temps_minutes) > 60;

Seules les catégories dont la durée moyenne dépasse 60 minutes restent.

L'ordre d'une requête

Les clauses s'écrivent toujours dans cet ordre :

1. SELECT : les colonnes et calculs ;
2. FROM et JOIN : les tables ;
3. WHERE : le filtre sur les lignes ;
4. GROUP BY : le regroupement ;
5. HAVING : le filtre sur les groupes ;
6. ORDER BY puis LIMIT : le tri et la coupe.

Les index

Pour trouver une recette par son titre, PostgreSQL lit par défaut toute la table, ligne après ligne. Avec 20 recettes, c'est instantané. Avec des millions, c'est lent.

Un index est une structure annexe, comme l'index à la fin d'un livre. Elle permet de retrouver les lignes sans tout lire.

CREATE INDEX idx_recettes_titre ON recettes (titre);

Cette commande crée un index sur la colonne titre. Les requêtes avec WHERE titre = '...' peuvent alors l'utiliser. C'est PostgreSQL qui décide, au cas par cas, s'il l'emploie.

Pour le vérifier, place EXPLAIN devant la requête :

EXPLAIN SELECT * FROM recettes WHERE titre = 'Gratin dauphinois';

EXPLAIN affiche le plan d'exécution choisi, sans lancer la requête.

Quand créer un index ?

Une clé primaire ou une contrainte UNIQUE crée déjà un index automatiquement. En revanche, une clé étrangère comme recettes.categorie_id n'en reçoit pas : si tu joins souvent sur cette colonne, ajoute-le toi-même.

Un index a un prix : il occupe de la place, et chaque INSERT, UPDATE ou DELETE doit aussi le mettre à jour.

À retenir

• COUNT, SUM, AVG, MIN et MAX résument plusieurs lignes en une valeur.
• GROUP BY calcule un agrégat par groupe ; chaque colonne non agrégée doit y figurer.
• WHERE filtre les lignes, HAVING filtre les groupes.
• Un index accélère les recherches sur une colonne, au prix d'un peu de place et d'écritures plus lentes.
• Vérifie l'usage d'un index avec EXPLAIN avant de multiplier les index.