Pourquoi les fonctions de fenêtrage SQL ne sont-elles pas autorisées dans GROUP BY ?

C'est vrai - l'ordre des opérations SQL signifie que les fonctions de fenêtrage ne sont pas autorisées dans GROUP BY. Mais vous pouvez quand même les utiliser grâce à cette astuce spéciale.

Les fonctions de fenêtrage SQL sont l'une des fonctionnalités les plus puissantes du langage. Cependant, la syntaxe des fonctions de fenêtrage n'est pas facile à maîtriser. Elle comporte de nombreux détails qui peuvent faire trébucher les débutants. L'un des pièges les plus courants consiste à essayer d'utiliser les fonctions de fenêtrage dans GROUP BY.

"Error : Window Functions Are Not Allowed in GROUP BY"

Imaginez que vous ayez une table, midterm qui stocke les résultats d'un examen de mi-année passé par des étudiants. Les colonnes de la table sont name, nom de l'étudiant, et points, nombre de points que l'étudiant a obtenu à l'examen. Vous souhaitez répartir les étudiants en quatre groupes de taille égale en fonction de leur résultat à l'examen : les 25 % les meilleurs sont dans un groupe, les 25 % suivants dans le deuxième groupe, les 25 % suivants dans le troisième groupe et les 25 % inférieurs dans le dernier groupe. Vous aimeriez ensuite voir l'intervalle des points et le nombre d'étudiants dans chaque groupe.

Voici une requête que vous pourriez écrire :

SELECT
 ntile(4) OVER (ORDER BY points),
 min(points),
 max(points),
 count(*)
FROM midterm
GROUP BY ntile(4) OVER (ORDER BY points);

La fonction NTILE() divise les élèves en groupes et attribue à chaque élève le numéro (1-4) de son groupe. Nous voulons ensuite regrouper les étudiants en fonction du numéro de leur groupe et calculer le minimum, le maximum et le nombre pour chaque groupe. Cependant, lorsque vous exécutez cette requête, vous obtenez une erreur :

ERROR:  window functions are not allowed in GROUP BY
LINE 7: GROUP BY ntile(4) OVER (ORDER BY points);

Pourquoi les fonctions de fenêtrage n'est pas autorisé dans GROUP BY

La raison pour laquelle les fonctions de fenêtrage ne sont pas autorisées dans GROUP BY est l'ordre des opérations en SQL. Les clauses d'une requête SQL sont traitées dans un ordre différent de celui dans lequel elles sont écrites dans la requête. L'ordre complet des opérations en SQL est le suivant :

• FROM, JOIN
• WHERE
• GROUP BY
• Fonctions agrégées
• HAVING
• Fonctions de fenêtrage
• SELECT
• DISTINCT
• UNION/INTERSECT/EXCEPT
• ORDER BY
• OFFSET
• LIMIT/FETCH/TOP

Une requête SQL commence par déterminer les tables à interroger, puis elle applique les filtres WHERE, et enfin elle effectue l'opération GROUP BY. Elle procède ensuite au calcul des fonctions agrégées, des filtres HAVING et enfin des fonctions de fenêtrage. Ainsi, au moment où le site GROUP BY est évalué, les fonctions de fenêtrage ne sont pas encore calculées !

En pratique, vous ne pouvez faire directement référence aux fonctions de fenêtrage SQL que dans les clauses SELECT et ORDER BY.

L'ordre des opérations en SQL est l'une des choses les plus importantes à retenir lorsque vous écrivez une requête avec des fonctions de fenêtrage. Si vous n'utilisez pas souvent les fonctions de fenêtrage, il est facile de l'oublier, c'est pourquoi nous avons mis un rappel de l'ordre des opérations dans notre aide-mémoire SQL Fonctions de fenêtrage . N'oubliez pas de l'ajouter à vos favoris si vous utilisez les fonctions de fenêtrage de temps en temps !

Ainsi, SQL ne vous permet pas de placer des fonctions de fenêtrage dans un GROUP BY. Mais il existe un moyen de contourner ce problème ...

Comment utiliser les fonctions de fenêtrage dans GROUP BY

Pouvons-nous corriger la requête pour qu'elle renvoie le résultat que nous voulons ? Oui. La solution consiste à utiliser une sous-requête pour calculer la fonction de fenêtrage que vous souhaitez utiliser dans la requête principale. Voici notre exemple modifié :

SELECT
  quartile,
  min(points),
  max(points),
  count(*)
FROM
  (SELECT
     ntile(4) OVER (ORDER BY points) AS quartile,
     points
     FROM midterm) groups
GROUP BY quartile;

Dans la sous-requête, nous utilisons la fonction NTILE() pour répartir les élèves dans les groupes. Dans la requête principale, nous calculons les statistiques : le minimum, le maximum et le nombre d'étudiants.

Une autre possibilité consiste à utiliser une expression de table commune (CTE), comme suit :

WITH groups AS (
  SELECT
     ntile(4) OVER (ORDER BY points) AS quartile,
     points
  FROM midterm
)
SELECT
  quartile,
  min(points),
  max(points),
  count(*)
FROM groups
GROUP BY quartile;

La requête est similaire à la version précédente avec la sous-requête. Cependant, une CTE nous permet de définir la requête auxiliaire avant la requête principale, ce qui rend le code plus lisible.

Maîtrisez les fonctions de fenêtrage avec LearnSQL.com

Si vous souhaitez apprendre les fonctions de fenêtrage, nous vous recommandons notre cours interactif Fonctions de fenêtrage. Vous apprendrez à utiliser les fonctions de fenêtrage et à éviter les erreurs de débutant les plus courantes. Si vous n'êtes pas sûr que les fonctions de fenêtrage soient faites pour vous, lisez pourquoi vous devriez les apprendre ou cet article sur notre cours Fonctions de fenêtrage.

Si vous connaissez déjà les fonctions de fenêtrage, n'hésitez pas à télécharger notre fiche d'exercices SQL Fonctions de fenêtrage.