Formations — 1 037 leçons en français

Accueil › SQL & bases de données › OVER et PARTITION BY : agréger sans regrouper

OVER et PARTITION BY : agréger sans regrouper

Fonctions de fenêtrageavance

Une fonction de fenêtrage calcule une valeur sur un groupe de lignes sans réduire le résultat.

SELECT reference,
       client_id,
       total,
       sum(total)  OVER (PARTITION BY client_id) AS total_client,
       round(100 * total / sum(total) OVER (PARTITION BY client_id), 1) AS part_pct
FROM commande;

--  reference | client_id |  total | total_client | part_pct
-- -----------+-----------+--------+--------------+----------
--  C-001     |        42 | 100.00 |       450.00 |     22.2
--  C-002     |        42 | 350.00 |       450.00 |     77.8
--  C-003     |        17 |  80.00 |        80.00 |    100.0

Chaque commande reste visible, annotée du total de son client. Avec GROUP BY, il n'y aurait qu'une ligne par client.

Deux règles à connaître dès maintenant :

On ne peut pas filtrer une fenêtre dans le WHERE. Elle est évaluée après. Il faut envelopper :

WITH annotes AS (
    SELECT *, sum(total) OVER (PARTITION BY client_id) AS total_client FROM commande
)
SELECT * FROM annotes WHERE total_client > 1000;

Un ORDER BY dans OVER change le sens du calcul. Sans lui, sum() porte sur toute la partition ; avec lui, il devient cumulatif — du début de la partition jusqu'à la ligne courante.

Les mêmes données en entrée client · total A · 100 A · 300 B · 50 B · 80 GROUP BY : 4 lignes → 2 sum() … GROUP BY client A · 400 B · 130 le détail des commandes a disparu PARTITION BY : 4 lignes → 4 sum() OVER (PARTITION BY client) A · 100 · 400 A · 300 · 400 B · 50 · 130 B · 80 · 130 Sans ORDER BY dans OVER la somme porte sur TOUTE la partition : la même valeur sur chaque ligne du client Avec ORDER BY dans OVER la somme devient CUMULATIVE : du début de la partition jusqu'à la ligne courante Une fenêtre ne se filtre pas dans le WHERE : calculée après, elle s'enveloppe dans une CTE. La bonne question : « ai-je besoin de garder le détail de chaque ligne ? » Si oui, c'est une fenêtre.
Le fenêtrage ne regroupe pas, il annote : chaque ligne reste, avec la valeur calculée sur son groupe à côté d'elle.

Les termes à connaître

Fonction de fenêtrage

Une fonction qui calcule une valeur sur un ensemble de lignes liées à la ligne courante, sans réduire le nombre de lignes. Contrairement à GROUP BY, chaque ligne reste dans le résultat.

OVER ()

La clause qui transforme une fonction en fonction de fenêtrage. sum(total) OVER () calcule la somme sur toutes les lignes du résultat, répétée sur chaque ligne.

PARTITION BY

Découpe le résultat en groupes indépendants : sum(total) OVER (PARTITION BY client_id) donne le total du client sur chaque ligne de ce client.

ORDER BY dans OVER

Ordonne les lignes à l'intérieur de la fenêtre. Il est indispensable aux fonctions de rang et de décalage, et change le sens des agrégats (qui deviennent cumulatifs).

WINDOW

Clause permettant de nommer une fenêtre réutilisée : WINDOW w AS (PARTITION BY client_id ORDER BY creee_le), puis sum(…) OVER w. Évite les répétitions.

Ordre d'évaluation

Les fonctions de fenêtrage s'évaluent après le WHERE, le GROUP BY et le HAVING, mais avant le ORDER BY final. On ne peut donc pas filtrer dessus dans le WHERE : il faut une sous-requête ou un CTE.

Lire l'explication détaillée et faire le quiz →

Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.