Agrégats par groupe sans GROUP BY
Utiliser une sous-requête corrélée pour calculer le maximum d’un groupe à côté des lignes détaillées
Agrégats par groupe sans GROUP BY est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 2 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.
Le problème du détail et de l’agrégat
Une question classique en entretien : « Affichez chaque ligne avec un agrégat de son groupe. » Par exemple, listez chaque employé avec le salaire maximal de son département sur la même ligne.
Un GROUP BY simple regroupe les lignes, il ne peut donc pas conserver le détail de chaque employé. Vous avez besoin des lignes de détail et d’un nombre au niveau du groupe réunis.
Une sous-requête corrélée résout élégamment ce problème : elle calcule l’agrégat du groupe pour chaque ligne de détail sans rien regrouper.
Pourquoi un simple GROUP BY échoue ici
Si vous écrivez SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, vous obtenez une ligne par département et perdez les noms individuels.
Ajouter name à la liste SELECT sans l’ajouter à GROUP BY provoque l’erreur classique « la colonne doit apparaître dans GROUP BY ».
La personne qui mène l’entretien vérifie que vous comprenez que GROUP BY réduit la cardinalité. Pour conserver les lignes de détail, calculez l’agrégat autrement.
La sous-requête corrélée à la rescousse
Placez l’agrégat du groupe dans la liste SELECT sous la forme d’une sous-requête corrélée. Chaque ligne d’employé déclenche un MAX interne limité au département de cet employé.
La corrélation e2.dept_id = e1.dept_id relie l’agrégat au bon groupe, tandis que la requête externe renvoie toujours une ligne par employé.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;Comparer chaque ligne à son groupe
Une fois l’agrégat du groupe intégré à la requête, vous pouvez comparer chaque ligne à celui-ci. Une question fréquente est la suivante : « Trouvez les employés qui gagnent plus que la moyenne de leur département. »
Ici, l’AVG corrélé se trouve dans WHERE, de sorte que chaque employé est comparé à la moyenne de son propre département.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Calculer l’écart par rapport au groupe
Vous pouvez aussi montrer l’écart entre chaque ligne et l’agrégat de son groupe. Soustraire la moyenne corrélée donne un écart pour chaque ligne.
Remarquez que la même sous-requête corrélée peut être réutilisée dans plusieurs expressions SELECT ; le moteur l’évalue pour chaque ligne chaque fois qu’elle apparaît.
SELECT e1.name,
e1.salary,
e1.salary - (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;Trouver le plus gros salaire de chaque groupe
Pour ne renvoyer que la personne la mieux payée de chaque département, comparez chaque salaire au MAX corrélé et conservez les correspondances.
Ce modèle conserve les égalités : si deux employés partagent le salaire maximal du département, ils apparaissent tous les deux. La gestion de ces égalités est souvent la question complémentaire posée en entretien.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);L’alternative des fonctions de fenêtre
Le SQL moderne offre un outil plus clair : les fonctions de fenêtre. MAX(salary) OVER (PARTITION BY dept_id) calcule l’agrégat du groupe sans regrouper les lignes et sans reparcourir la table à l’aide d’une sous-requête corrélée.
Les personnes qui mènent l’entretien apprécient que vous puissiez présenter les deux solutions et expliquer que la version avec fenêtre est généralement plus performante, car elle ne parcourt la table qu’une seule fois.
SELECT name,
dept_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;Compromis entre sous-requête corrélée et fonction de fenêtre
Les deux approches renvoient la même structure de résultat, mais elles diffèrent :
- Sous-requête corrélée : portable, fonctionne sur des moteurs très anciens, mais est réévaluée pour chaque ligne.
- Fonction de fenêtre : parcours unique, bien plus rapide sur les grandes tables, mais nécessite la prise en charge des fenêtres SQL.
Indiquez laquelle vous choisiriez et pourquoi. Pour un besoin ponctuel sur une petite table, les deux conviennent ; pour des analyses à grande échelle, préférez la fonction de fenêtre.
Exemple détaillé : commandes supérieures à la moyenne du client
Appliquez le modèle aux commandes. Affichez les commandes dont le montant dépasse la valeur moyenne des commandes passées par leur client.
L’AVG corrélé est limité par o2.customer_id = o1.customer_id, ce qui donne à chaque commande la référence personnelle de son client.
SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);Attention aux cas NULL et aux groupes à une seule ligne
Si un groupe ne comporte qu’une seule ligne, sa moyenne est égale à la valeur de cette ligne ; salary > avg est donc faux et la ligne est exclue. Mentionnez spontanément ce cas limite.
Par ailleurs, AVG et MAX ignorent les salaires NULL, conformément à la sémantique des agrégats SQL. Si toutes les valeurs d’un groupe sont NULL, l’agrégat vaut NULL et les comparaisons deviennent UNKNOWN, ce qui exclut la ligne. Anticiper ces cas est ce qui distingue une réponse approfondie.
Calculer le rang au sein d’un groupe
Vous pouvez exprimer le rang d’une ligne dans son groupe au moyen d’un COUNT corrélé. Pour trouver le rang salarial de chaque employé dans son département, comptez combien de collègues gagnent davantage.
Le rang 1 correspond au salaire le plus élevé. Ajouter 1 transforme le nombre de personnes mieux payées en une position commençant à 1, et la corrélation limite le calcul au département.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;Vérification rapide
Choisissez la raison pour laquelle une sous-requête corrélée est préférable à un simple GROUP BY pour cette tâche.
Récapitulatif : agrégats par groupe sans GROUP BY
Points essentiels :
- Une sous-requête corrélée place un agrégat au niveau du groupe sur chaque ligne de détail sans les regrouper.
- Utilisez-la dans SELECT pour afficher l’agrégat, ou dans WHERE pour comparer chaque ligne à son groupe.
- Le modèle
= MAX(...)renvoie toutes les lignes maximales ex æquo. - Une fonction de fenêtre avec
PARTITION BYfait la même chose en un seul parcours et passe généralement mieux à l’échelle.
Proposez les deux solutions et justifiez votre choix en entretien.
Questions Fréquemment Posées
La leçon « Agrégats par groupe sans GROUP BY » est-elle gratuite ?
Oui — le texte complet de « Agrégats par groupe sans GROUP BY » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.
Qu'est-ce que j'apprendrai dans « Agrégats par groupe sans GROUP BY » ?
Utiliser une sous-requête corrélée pour calculer le maximum d’un groupe à côté des lignes détaillées Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.
Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?
Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 2 sur 4.
Combien de temps prend la leçon « Agrégats par groupe sans GROUP BY » ?
La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.
Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?
Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.
Toutes les leçons de ce cours
- Anatomie d’une sous-requête corrélée
- Agrégats par groupe sans GROUP BY
- EXISTS et NOT EXISTS corrélés
- Réécrire les sous-requêtes corrélées avec des jointures