SQL avancé : agrégats, sous-requêtes et vues

Ce que ce chapitre ajoute

Le chapitre sur les requêtes SQL a posé les bases : créer des tables, insérer des données, filtrer, trier, joindre. Celui-ci résume et compare : des calculs sur des paquets de lignes, des requêtes qui en appellent d'autres. Tout repose sur l'ordre logique d'évaluation posé là-bas, FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Son tableau sert de référence ici, et ce chapitre détaille les deux étapes qu'il annonçait sans les traiter : le regroupement et le filtre des groupes.

Les exemples portent sur la même base de démonstration : l'école (Etudiant, Classe, Cours, Enseignant, Inscription) et l'entreprise (Employe, Projet, TravailleSur, Client). Les blocs sql s'exécutent sur elle, et les nombres annoncés sont ceux qu'elle rend. Quand un exemple a besoin d'une situation que la base n'a pas, un ex aequo ou un nouvel arrivant, le bloc la crée lui-même avant sa requête : la base repart neuve à chaque exécution.

Le piège des valeurs NULL

Avant de résumer des lignes, il faut savoir ce que deviennent celles dont une valeur manque. La base en a plusieurs : deux étudiants sans adresse, deux inscriptions sans note, deux cours sans enseignant.

Le premier tableau est vide, le deuxième rend Bernard et Marchand. Le troisième ne compte que sept étudiants sur dix : Lea Martin est écartée, à juste titre, mais Bernard et Marchand aussi, parce qu'on ne sait pas si une adresse absente diffère de celle de Lea. Pour les garder, il faut le demander : WHERE email <> 'lea.martin@ecole.fr' OR email IS NULL.

Une condition qui combine plusieurs comparaisons propage l'inconnu selon une règle fixe. Le bloc suivant l'affiche, 1 valant vrai, 0 faux, et NULL inconnu :

Un AND dont un terme est faux est faux, quoi que vaille l'autre ; un OR dont un terme est vrai est vrai. Dans les autres cas, l'inconnu reste inconnu, et sa négation aussi. D'où les trois comptes : 17 et 6 font 23, et non 25. Les deux inscriptions sans note ne sont d'aucun côté, ni au-dessus de 10, ni « pas au-dessus ».

Un tri, lui, doit bien placer les NULL quelque part. MySQL et SQLite les mettent en tête d'un tri croissant, PostgreSQL et Oracle en fin.

Le premier tableau commence par les deux inscriptions sans note (le second critère, idEtudiant, les départage), puis la note de 6. Dans le second, note IS NULL vaut 0 pour une note connue et 1 pour une absente : trier d'abord sur cette expression fait passer les notes connues devant, 6, 7 et 8,5. La norme prévoit plus simple, ORDER BY note NULLS LAST, que SQLite et PostgreSQL connaissent et MySQL non.

À retenir. La clause WHERE filtre les lignes individuelles, avant tout calcul, et ne garde que celles où la condition est vraie.
Une valeur absente échappe aux comparaisons ordinaires : il faut IS NULL, IS NOT NULL, ou l'égalité qui tolère NULL (<=> en MySQL, IS en SQLite).

Fonctions d'agrégation

WHERE choisit des lignes ; il ne sait pas les résumer. Dès que la question porte sur un total, une moyenne ou un compte, il faut un autre outil.

Les fonctions d'agrégation calculent une valeur à partir de plusieurs lignes. Elles remplacent un paquet de lignes par un seul nombre, et c'est cette disparition des lignes individuelles qui explique tout le reste du chapitre.

COUNT(*) ou COUNT(colonne) ? COUNT(*) compte toutes les lignes, y compris celles dont les colonnes sont à NULL. COUNT(colonne) ne compte que les lignes où cette colonne n'est pas à NULL. L'écart entre les deux comptes est donc le nombre de valeurs manquantes : c'est le moyen le plus rapide de savoir si une colonne est bien remplie.

Résumer toute une table

AS donne un alias lisible à la colonne résultante. C'est particulièrement utile avec les agrégats, qui n'ont pas de nom par défaut.

Le résultat contient une seule ligne et deux colonnes : 25 inscriptions, et une moyenne de 12,91. Deux pièges tiennent dans ces deux nombres. Le compte porte sur les lignes de Inscription, donc sur les inscriptions et non sur les étudiants, qui sont dix ; nommer cette colonne total_etudiants aurait fait mentir le résultat. Et la moyenne ne porte que sur 23 notes, parce que deux inscriptions n'en ont pas et que AVG ignore les NULL au lieu de les compter pour zéro.

Cinq inscrits en mathématiques, 14,4 de moyenne. Le WHERE s'applique aux lignes avant la moyenne : c'est la moyenne du cours 3, et non la moyenne générale restreinte ensuite. Comme la précédente, la requête rend une seule ligne, quel que soit le nombre de lignes résumées.

Regrouper avec GROUP BY

GROUP BY divise les lignes en groupes selon une colonne, et les fonctions d'agrégation s'appliquent à chaque groupe séparément.

Ce que fait GROUP BY, sur cinq lignes

Table Employe, les cinq lignes de la base, réduites à trois colonnes :

Résultat :

Cinq lignes au départ, trois à l'arrivée : chaque groupe produit une seule ligne. Ce qu'il faut regarder, c'est ce qui a disparu. Aucun nom ne figure plus dans le résultat, et la ligne Etudes ne permet plus de retrouver le salaire de Barbier : 2 775 est la moyenne de 2 450 et de 3 100, et ce n'est le salaire de personne. Après un GROUP BY, les lignes individuelles n'existent plus : seules subsistent la colonne de regroupement et les valeurs calculées sur le groupe.

Le parcours Python fait la même opération avec pandas : employes.groupby("service")["salaire"].mean(), vu dans le chapitre pandas, est ce GROUP BY écrit en Python. Grouper, choisir la colonne, résumer : les deux langages disent la même phrase. Le chapitre Big data sur l'interrogation d'un entrepôt la dit sur des milliards de lignes, autour d'une table de faits.

Sur les inscriptions, le regroupement par cours donne un résumé par matière :

Le nombre d'inscrits et la moyenne sont maintenant calculés pour chaque cours. Le résultat contient sept lignes, une par cours de la base, et trois colonnes. GROUP BY ne filtre rien : il découpe les lignes en paquets, et chaque fonction d'agrégation travaille ensuite à l'intérieur d'un paquet.

Le résultat ne montre que les nombres. Le tableau croisé dynamique d'un tableur, celui du chapitre Excel sur les graphiques, fait le même découpage, et montre en plus les lignes de chaque paquet. Le voici sur les vingt-cinq inscriptions.

Une colonne affichée doit être regroupée ou agrégée

La ligne Etudes affiche Girard à côté de 2 775, une moyenne qui n'est pas son salaire, et la ligne Support affiche Lemoine. Rien ne relie ces noms aux moyennes. Avec MAX ou MIN, SQLite suit une règle à lui : quand la requête contient un seul MIN ou un seul MAX, les colonnes nues viennent de la ligne qui porte ce minimum ou ce maximum. Remplacer AVG par MAX affiche donc Barbier pour les Etudes, ce qui paraît juste ; la requête reste refusée par MySQL, et en cas d'ex aequo un seul nom s'afficherait. Pour obtenir à coup sûr le nom de la personne qui porte le maximum, il faut une sous-requête corrélée, vue plus bas.

À retenir. Les fonctions d'agrégation s'écrivent dans la clause SELECT.
Elles calculent une valeur unique par groupe, ou sur l'ensemble des lignes en l'absence de GROUP BY.
Une colonne affichée à côté d'un agrégat doit figurer dans le GROUP BY.
Pour filtrer sur un agrégat, utiliser HAVING, jamais WHERE.

Filtrer après regroupement : HAVING

HAVING s'applique après GROUP BY pour filtrer les groupes selon une condition sur les agrégats.

Le résultat contient cinq lignes au lieu de sept : les cours 4 et 7 sortent, leur moyenne ne dépassant pas 12 (11,5 et 10,75). Ce sont bien des groupes qui ont été écartés, pas des lignes.

HAVING répète l'agrégat, AVG(note), au lieu de citer l'alias moyenne. Selon la norme, un alias de SELECT est inconnu de HAVING, qui s'évalue avant lui. MySQL et SQLite l'y acceptent pourtant, par tolérance, et SQLite l'accepte même dans WHERE. Ce chapitre répète donc l'agrégat dans HAVING : c'est l'écriture qui passe partout.

Reste le cas où l'on voudrait écrire cette condition dans WHERE. Exécuter le bloc suivant : la requête est refusée.

Le moteur de la page répond misuse of aggregate function AVG(), et MySQL Invalid use of group function : tous deux disent que l'agrégat est mal placé, sans dire où le mettre. La figure suivante montre où exactement la requête est refusée : à la deuxième étape, avant que le moindre regroupement n'ait eu lieu.

SELECT idCoursFROM InscriptionWHERE AVG(note) > 12GROUP BY idCours

La même question, écrite avec HAVING, dans le pipeline. Le regroupement a lieu à l'étape 3, HAVING à l'étape 4 : la moyenne existe.

SELECT idCours, AVG(note) AS moyenneFROM InscriptionGROUP BY idCoursHAVING AVG(note) > 12

Combiner WHERE et HAVING

Les deux clauses peuvent coexister : elles n'agissent pas au même moment.

Deux lignes : le cours 1, avec trois notes de 12 ou plus, et le cours 3, avec quatre. Les deux clauses ne font pas le même travail. WHERE a écarté des lignes, les notes sous 12 et les deux notes absentes ; HAVING a écarté des groupes, les cours à qui il restait moins de trois notes après ce premier tri.

Le même seuil, dans WHERE ou dans HAVING

Le tableau se retient mal tant qu'on n'a pas vu les deux clauses donner des résultats différents. Voici le cas, sur la même base et autour du même seuil, une note de 15.

Pour le cours 1, la première rend 1 | 2, la seconde 1 | 5. Même base, même cours, même seuil, et deux nombres.

En A, le filtre passe d'abord : les cinq inscriptions du cours 1 sont réduites aux deux notes au-dessus de 15, et le compte porte sur ces deux-là. En B, les cinq inscriptions sont comptées, et le seuil sert seulement à décider si le groupe du cours 1 mérite d'apparaître. Les cours 5, 6 et 7 disparaissent dans les deux cas, ce qui masque la différence si l'on ne regarde que la liste des cours.

Les deux se disent de la même façon en français : « les cours où une note dépasse 15 ». La question à se poser avant d'écrire est donc toujours la même : le seuil décrit-il les lignes à conserver, ou les groupes à conserver ?

Toutes les étapes, dans l'ordre

La figure suivante fait tourner une requête qui emploie presque toutes les clauses : une jointure, un filtre de lignes, un regroupement, un filtre de groupes et un tri. Cliquer sur chaque clause montre la relation à ce moment précis.

SELECT nomClasse, COUNT(*) AS effectifFROM EtudiantJOIN Classe ON Etudiant.idClasse = Classe.idClasseWHERE actif = 1GROUP BY nomClasseHAVING COUNT(*) >= 3ORDER BY effectif DESC

À retenir. Utiliser WHERE pour filtrer les lignes individuelles, avant regroupement.
Utiliser HAVING pour filtrer les groupes formés par GROUP BY.
Les deux se combinent dans une même requête : ils s'appliquent à deux moments différents, et sur deux objets différents.
Le test : une condition sur un agrégat ne va que dans HAVING ; une condition sur une colonne du GROUP BY donne le même résultat dans les deux clauses, et se met dans WHERE, qui écarte les lignes plus tôt ; une condition sur une autre colonne ne va que dans WHERE.

L'exercice suivant fait écrire un filtre sur des groupes. Il est jugé dans la page, sur la base du cours et sur des données modifiées.

Compter après une jointure externe

Une jointure externe garde les lignes sans correspondance en fabriquant des NULL pour l'autre côté. Compter ensuite demande de choisir ce qu'on compte.

Roy a 1 dans faux_compte et 0 dans nb_cours. La jointure externe lui a fabriqué une ligne de NULL, et COUNT(*) compte les lignes, fabriquées ou non ; COUNT(c.idCours) ne compte que les cours réels. Les trois autres enseignants ont le même nombre dans les deux colonnes, ce qui rend l'erreur invisible tant que personne n'est sans cours. Après un LEFT JOIN, on compte une colonne de la table de droite, jamais l'étoile.

Le regroupement porte sur e.idEnseignant et e.nomEns : l'identifiant sépare deux homonymes, le nom sert à l'affichage.

Les quatre questions suivantes font prévoir ce que rend un regroupement, sur des cas que la page n'affiche pas.

Jointure, regroupement, filtre et tri dans une même requête

Cette requête enchaîne quatre gestes :

• Assembler Cours et Inscription sur l'identifiant de cours ;
• Regrouper par cours, pour obtenir un paquet de lignes par cours ;
• Écarter les groupes dont la moyenne est inférieure à 10 ;
• Trier ce qui reste par moyenne décroissante.

Le résultat contient sept lignes et trois colonnes : sur cette base, les sept cours atteignent tous la moyenne de 10, et le HAVING n'écarte donc personne. Remplacer 10 par 13 et relancer : il reste quatre cours, Mathematiques en tête avec 14,4. L'alias moyenne sert dans ORDER BY, qui s'évalue après SELECT.

Le regroupement porte sur c.idCours, c.intitule, et non sur l'intitulé seul. Deux cours qui porteraient le même nom (deux « Physique », un par niveau) seraient sinon fusionnés en un seul groupe, et leurs notes mêlées. L'identifiant sépare les cours, l'intitulé sert à l'affichage.

Trier sur un agrégat, puis couper

L'étudiant 3 vient en tête avec quatre cours, puis deux étudiants à trois cours. Mais ils sont quatre à en suivre trois : LIMIT a coupé entre des ex aequo, et rien ne dit lesquels il garde. ORDER BY s'évalue avant LIMIT, et c'est ce qui rend un « top 3 » possible ; il reste à décider, avant d'écrire, ce que la question veut faire des égalités.

Requêtes imbriquées (sous-requêtes)

Filtrer sur un nombre connu d'avance ne demande pas de sous-requête. Il en faut une dès que le seuil doit lui-même être calculé sur la base : « plus long que la moyenne » n'est pas un nombre, c'est une requête.

Une sous-requête est un SELECT placé à l'intérieur d'une autre requête. Une sous-requête non corrélée est évaluée d'abord, et son résultat sert à la requête externe.

La première question à se poser devant une sous-requête n'est pas ce qu'elle calcule, mais ce qu'elle rend. C'est cela qui décide de l'opérateur utilisable. Une erreur à ce niveau produit un refus du moteur ou, pire, un résultat plausible et faux.

Une sous-requête corrélée est à part : elle cite une colonne de la requête externe, et se réévalue donc pour chaque ligne examinée. Elle rend en général une valeur, mais une valeur différente à chaque fois.

Sous-requêtes scalaires

Les trois sous-requêtes rendent une valeur, et c'est pour cela que > et = sont utilisables. La première rend 26,57, la moyenne des sept volumes horaires, et la requête externe garde quatre cours au-dessus. La deuxième rend 36 et désigne Mathematiques. La troisième rend Paris, et le résultat compte les quatre Parisiens, Lea Martin comprise.

La troisième est la plus fragile des trois : elle ne rend une valeur que parce qu'un seul étudiant s'appelle Martin. Le jour où un second Martin entre dans la base, la sous-requête rend deux lignes. MySQL refuse alors la requête (Subquery returns more than 1 row) ; SQLite, le moteur de cette page, ne dit rien et garde l'une des deux, la première qu'il rencontre, ce qui donne un résultat plausible et faux :

Le bloc rend les quatre mêmes Parisiens, et rien d'autre : la ville de Paul Martin a été écartée sans avertissement. Une sous-requête n'est scalaire que si quelque chose le garantit, un agrégat ou une contrainte d'unicité ; ici, il aurait fallu désigner Lea Martin par son identifiant.

Sous-requêtes avec IN et NOT IN

Dès que la sous-requête rend plusieurs lignes, la comparaison devient un test d'appartenance.

La sous-requête rend une colonne de cinq identifiants, ceux des inscrits en mathématiques, et la requête externe garde les cinq étudiants correspondants : Martin, Diallo, Dupond, Bernard et Nguyen. Avec un seul identifiant dans la liste, = (...) aurait suffi ; avec cinq, seul IN est juste : = serait refusé par MySQL, et comparerait ici au premier identifiant seulement. La sous-requête contient elle-même une jointure : elle peut être aussi riche qu'une requête ordinaire, du moment qu'elle rend une colonne.

Cinq étudiants sur dix : Durand, Bernard, Moreau, Nguyen et Petit. NOT IN garde ce qui n'appartient pas à la colonne rendue.

Les deux requêtes suivantes ne diffèrent que par une valeur ajoutée à la liste.

La première rend trois étudiants, deux de Nantes et un de Bordeaux. La seconde n'en rend aucun.

L'explication tient en une ligne : NOT IN demande au moteur d'affirmer que la ville diffère de chacune des valeurs de la liste. Pour Paris et Lyon, il sait répondre. Pour NULL, il ne sait pas : la valeur est inconnue, et la comparaison l'est aussi. Une conjonction dont un terme est inconnu n'est jamais vraie, et aucune ligne ne passe.

Le défaut est redoutable parce qu'il ne se déclenche pas tout de suite. Une requête NOT IN juste pendant des mois devient silencieusement vide le jour où une seule valeur manquante (NULL, et non une chaîne vide) apparaît dans la colonne que rend la sous-requête. IN n'a pas ce problème : il lui suffit d'une correspondance vraie, et le NULL y est simplement ignoré.

Reste à savoir de quel côté chercher ce NULL. Le raisonnement suivant se trompe d'une seule étape : la trouver, puis donner une valeur qui la met en défaut.

La base de démonstration est dans le cas que le collègue croyait écarté : deux cours, Physique et Reseaux, n'ont pas d'enseignant, et leur idEnseignant vaut NULL.

La première ne rend rien, la seconde rend Roy. NOT EXISTS cherche, pour chaque enseignant, un cours qui le désigne ; les deux cours sans enseignant n'en désignent aucun, et ils sont simplement ignorés. Le SELECT 1 n'a rien de mystérieux : seule compte l'existence d'une ligne, pas ce qu'elle contient. Cette sous-requête cite e.idEnseignant, une colonne de la requête externe : elle est corrélée, une notion détaillée juste après. Elle n'a besoin d'aucune précaution, là où NOT IN ne se répare qu'à condition d'y penser.

Sous-requête corrélée : le meilleur de chaque cours

La sous-requête est corrélée : i2.idCours = i1.idCours cite la ligne en cours de traitement, ce qui oblige à la réévaluer pour chacune des vingt-cinq inscriptions.

Le résultat compte sept lignes, une par cours : Lefebvre en bases de données avec 18, Dupond en algorithmique et en mathématiques, et ainsi de suite. Les deux inscriptions sans note ne sont jamais retenues, puisqu'une valeur absente n'est égale à rien.

Ce que la corrélation change : en retirant la clause WHERE de la sous-requête, elle rendrait le maximum de toute la base, 19, et la requête ne garderait plus que Dupond en mathématiques. C'est cette seule ligne qui transforme « la meilleure note » en « la meilleure note de son cours ». C'est aussi la bonne réponse à la question que la colonne nue posait mal : en cas d'ex aequo, les deux étudiants sortent, et MySQL l'accepte.

La même corrélation compare une ligne à la moyenne de son groupe, et non plus à son maximum. C'est l'exercice suivant.

Une sous-requête dans le FROM

L'alias AS par_etudiant n'est pas décoratif : une table placée dans un FROM doit porter un nom, et MySQL refuse la requête sans lui (Every derived table must have its own alias). SQLite, le moteur de cette page, s'en passe : retirer l'alias ici ne provoque aucune erreur, mais la requête ne passerait plus sous MySQL.

Opérateurs ALL et ANY

Deux lignes, Barbier et Roche, chacun mieux payé que son unique collègue de service. La requête avec ALL, elle, en rend trois sous MySQL : Vidal, seule au service Commerce, n'a aucun collègue à dépasser, et > ALL d'une liste vide est vrai. Le MAX d'une liste vide vaut NULL, et la comparaison avec NULL écarte Vidal. Les deux requêtes répondent donc à deux questions : « mieux payé que tous ses collègues de service » inclut l'employée seule dans son service, « mieux payé que le mieux payé de ses collègues » l'exclut. La traduction fidèle de ALL passe par NOT EXISTS, tant que la colonne ne contient pas de NULL :

Trois lignes, Barbier, Roche et Vidal, comme la requête ALL sous MySQL. (Si la colonne pouvait contenir des NULL, il faudrait remplacer la dernière condition par (e1.salaire > e2.salaire) IS NOT TRUE, qui traite une comparaison inconnue comme ALL la traite.)

Deux lignes, Barbier et Vidal. La sous-requête rend 2 450, le plus petit salaire des Etudes, celui de Girard : être mieux payé qu'au moins un employé des Etudes revient à être mieux payé que celui-là. Ici la réécriture ne perd rien : sur une liste vide, ANY est faux, et la comparaison avec un minimum qui vaut NULL n'est pas vraie non plus.

ALL ou ANY. ALL : la condition doit être vraie pour tous les éléments de la sous-requête, et elle l'est d'office si la sous-requête ne rend rien ; d'où la réécriture par MAX, qui ne vaut que si la liste n'est pas vide.
ANY (ou SOME) : vraie pour au moins un élément, d'où la réécriture par MIN.
= ANY (...) équivaut à IN (...), et NOT IN (...) à <> ALL (...) : c'est pour cela qu'un seul NULL dans la liste rend NOT IN inutilisable.
Ces deux opérateurs attendent une colonne, jamais une valeur seule.

À retenir. Les requêtes avancées combinent GROUP BY, HAVING, ORDER BY et sous-requêtes, sans rien ajouter aux règles déjà vues.
Une sous-requête corrélée compare chaque ligne à son propre groupe, au prix d'un recalcul par ligne.
ANY se réécrit avec MIN. ALL se réécrit avec MAX seulement si la sous-requête rend au moins une ligne ; avec NOT EXISTS, il se réécrit dans tous les cas, tant que la colonne ne contient pas de NULL.

Donner un nom à une requête : les vues

Une requête qu'on réécrit souvent mérite un nom. Une vue est une requête enregistrée sous un nom, qu'on interroge ensuite comme une table.

Le premier tableau compte quatre cours, le second cinq : Physique y entre, sa moyenne passant de 11,5 à 14,75. La vue n'a pas été modifiée, c'est la table qui l'a été. Une vue ne stocke aucune ligne : chaque lecture réexécute sa requête, et elle suit donc la table d'origine. Elle sert à trois choses : ne plus recopier une requête compliquée, donner à d'autres une table plus simple à interroger, et, sous MySQL, n'accorder à un compte que la vue, sans la table entière (GRANT SELECT ON MoyenneParCours TO ...). Le bloc crée la vue et l'interroge dans la même exécution, puisque la base repart neuve à chaque fois.

Une vue le temps d'une requête : WITH

Quand le nom ne sert qu'à une requête, WITH le déclare juste avant elle, sans rien enregistrer dans la base. On parle de requête nommée, ou de CTE (common table expression).

Deux lignes, Barbier et Roche. Vidal, seule au Commerce, est exactement à la moyenne de son service, et n'est donc pas au-dessus. La même question s'écrirait avec une sous-requête corrélée ; la version WITH se lit de haut en bas, la moyenne d'abord, la comparaison ensuite. MySQL l'accepte depuis sa version 8.0, et la page aussi.

Les fonctions de fenêtre vont plus loin dans la même direction : elles numérotent ou cumulent les lignes à l'intérieur d'un groupe, sans fusionner ces lignes comme le ferait un GROUP BY. Le meilleur de chaque cours s'écrit ainsi sans sous-requête corrélée :

Les sept lignes de la sous-requête corrélée, dans un autre ordre. PARTITION BY découpe les inscriptions par cours, ORDER BY les range de la meilleure note à la moins bonne, et RANK() donne son rang à chacune ; deux ex aequo reçoivent le même rang, et sortent donc tous les deux. MySQL 8 et la page l'exécutent.