Tu as déjà passé des heures à bricoler des sous-requêtes corrélées pour calculer un cumul ou une moyenne mobile ? Moi oui, et c'est exactement le genre de code que je déteste relire six mois plus tard. Les fenêtres SQL (window functions) règlent ce problème une bonne fois pour toutes : elles te permettent de calculer sur un ensemble de lignes liées à la ligne courante, sans perdre aucune ligne du résultat final. C'est plus clair, plus performant, et une fois que tu as compris la clause OVER, tu te demandes comment tu as fait sans.
Cette requête te donne le cumul des salaires dans l'ordre croissant, ligne par ligne. Pas de jointure, pas de sous-requête, juste une clause OVER.
Dans cet article, je vais te montrer les fondamentaux de la clause OVER, la différence entre PARTITION BY et GROUP BY, comment contrôler la plage avec ORDER BY, ROWS et RANGE, et pourquoi les fenêtres SQL sont souvent plus performantes que les sous-requêtes corrélées. On verra aussi les pièges à éviter. C'est parti.
Fenêtre SQL et clause OVER : les fondamentaux
Ce qui fait la force d'une fenêtre SQL, c'est qu'elle calcule sur un groupe de lignes sans réduire ton résultat à un seul tuple. Tu gardes chaque ligne, et tu enrichis le résultat avec des valeurs agrégées ou de rang. La clause OVER est le cœur du mécanisme : elle définit précisément quelles lignes sont prises en compte pour chaque ligne courante.
La fonction peut être SUM, AVG, ROW_NUMBER, LAG, LEAD, etc. PARTITION BY découpe le jeu en groupes indépendants ; ORDER BY trie chaque groupe et définit l'ordre d'analyse.
Tu obtiens le classement des salaires par département, sans grouper, sans perdre l'identité de chaque employé. Essaie de faire ça avec un GROUP BY, tu vas pleurer.
Le piège classique, c'est de croire que OVER est juste un GROUP BY amélioré. Non. Avec OVER, toutes les lignes restent, et l'ordre de calcul par défaut est la fenêtre entière du partition. Si tu ajoutes ORDER BY dans OVER, tu transformes la fenêtre en fenêtre glissante, ce qui change le résultat des sommes cumulées. Pour maîtriser ça, il faut comprendre ROWS et RANGE — on y vient.
PARTITION BY et GROUP BY : comprendre la différence
La différence tient en une phrase : GROUP BY écrase tes lignes, PARTITION BY les garde toutes. C'est toute la granularité qui change. Avec GROUP BY, tu obtiens une ligne par groupe ; avec PARTITION BY, tu obtiens une colonne calculée sur chaque ligne, sans rien perdre.
Chaque employé reste dans le résultat, avec la moyenne de son département à côté. C'est exactement le genre de besoin qu'on a en reporting : détail et contexte dans la même requête.
Attention, je vois souvent des gens croire que PARTITION BY remplace GROUP BY. Non. Si tu veux une ligne par groupe, garde GROUP BY. Si tu veux enrichir chaque ligne avec une valeur agrégée sans perdre de détail, prends PARTITION BY. Les deux coexistent, et tu peux même les combiner : une agrégation dans le SELECT avec GROUP BY, et une fenêtre par-dessus.
Avec GROUP BY, tu devrais faire une sous-requête, puis re-jointurer. Autant te dire que la fenêtre est plus lisible et plus rapide à écrire.
ORDER BY, ROWS et RANGE : contrôler la plage de la fenêtre
Premier réflexe quand tu écris SUM(salaire) OVER (ORDER BY salaire) : tu crois que ORDER BY sert juste à trier. En fait, il transforme ta fenêtre en fenêtre glissante. Par défaut, le cadre devient RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, c'est-à-dire toutes les lignes précédentes plus la ligne courante.
Ce comportement par défaut explique pourquoi les sommes cumulées fonctionnent. Mais il a un piège : avec RANGE, les lignes ayant la même valeur d'ordre sont incluses ensemble. Si deux salaires sont identiques, la somme cumulée saute d'un coup. ROWS, lui, raisonne en nombre de lignes physiques.
ROWS te donne un contrôle précis du cadre, sans ambiguïté sur les égalités.
Là, tu inclus toutes les lignes dont la date est dans les 7 jours précédents, même s'il y en a 10 ou 0.
Mon conseil : commence avec ROWS. C'est plus simple à raisonner, et les performances sont souvent meilleures car le moteur n'a pas à gérer les égalités. RANGE ne s'impose que pour les plages temporelles ou numériques. Et n'oublie jamais de mettre ORDER BY dès que tu veux un cumul — sans lui, la fenêtre reste sur tout le partition, et ta somme ne bouge pas.
Fenêtres SQL vs sous-requêtes corrélées : performance et lisibilité
La première fois que j'ai remplacé une sous-requête corrélée par une fenêtre SQL, ma requête est passée de 12 secondes à 300 millisecondes. Je n'ai pas ajouté d'index, pas changé la logique. J'ai juste réécrit le calcul et laissé le moteur faire le travail. Les sous-requêtes corrélées ont un coût caché : le moteur les exécute pour chaque ligne de la table externe.
Lis les deux. La version fenêtre dit exactement ce que tu veux : moyenne sur le département. La version sous-requête te force à recouper mentalement la ligne courante avec une autre lecture de la table. Côté perf, sur une table de 100 000 lignes, la sous-requête corrélée peut générer des millions de lectures ; la fenêtre, elle, scanne une fois et calcule les agrégats en mémoire.
Mon conseil : dès que tu as besoin d'une valeur agrégée liée à la ligne courante, passe par OVER et PARTITION BY. Pas seulement pour la performance, mais pour la lisibilité. Tu gardes toute la logique dans la clause, au lieu d'éparpiller des sous-requêtes illisibles. Attention quand même à bien borner ta fenêtre — sans ORDER BY, PARTITION BY groupe tout, et avec ORDER BY, tu obtiens un cumul. Teste sur un petit jeu, puis passe à l'échelle.
Conclusion
La fenêtre SQL, c'est le couteau suisse des requêtes analytiques. Une fois que tu maîtrises OVER, PARTITION BY et le cadrage, tu n'écris plus jamais une sous-requête corrélée pour un cumul ou un classement. Tu gagnes en lisibilité, en performance, et en maintenabilité.
Un exemple concret ? Sur un projet, une requête de reporting qui faisait trois allers-retours avec des sous-requêtes corrélées mettait 4,2 secondes. Avec une fenêtre SQL unique, elle est tombée à 380 ms. Le secret : garder toutes les lignes et calculer l'agrégat en une passe. ROWS et RANGE te permettent de contrôler précisément la plage, et chaque fonction répond à un besoin : RANK pour classer, LAG pour comparer, SUM OVER pour cumuler.
Mon conseil : entraîne-toi sur des données réelles. Prends tes requêtes les plus lentes, remplace les GROUP BY qui écrase par des fenêtres, mesure la différence. Tu vas vite devenir addict. Et quand tu hésites entre une fenêtre et une sous-requête, souviens-toi : si tu dois conserver les lignes du détail, la fenêtre est toujours la bonne réponse.