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 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), puissum(…) OVER w. Évite les répétitions.- Ordre d'évaluation
Les fonctions de fenêtrage s'évaluent après le
WHERE, leGROUP BYet leHAVING, mais avant leORDER BYfinal. On ne peut donc pas filtrer dessus dans leWHERE: il faut une sous-requête ou un CTE.
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.