🪟 Fonctions de fenêtrage PostgreSQL — Cheatsheet

Syntaxe de base

SELECT <fonction_fenetre>(arguments)
  OVER (
    [PARTITION BY colonne(s)]   -- découpe en partitions indépendantes
    [ORDER BY colonne(s)]       -- ordre logique DANS chaque partition
    [frame_clause]              -- sous-ensemble de lignes de la partition
  )
FROM ...

Forme factorisée avec une fenêtre nommée :

SELECT
  sum(montant) OVER w AS total,
  avg(montant) OVER w AS moyenne,
  rank()       OVER (w ORDER BY montant DESC) AS rang
FROM ventes
WINDOW w AS (PARTITION BY region);

💡 Contrairement à GROUP BY, une fonction de fenêtrage ne réduit pas le nombre de lignes : chaque ligne conserve ses valeurs d'origine, enrichies des calculs agrégés.

📊 Fonctions de classement

⚠️ Toutes nécessitent ORDER BY dans OVER, sauf row_number() (déconseillé sans).

🧮 Agrégats utilisables comme fonctions de fenêtrage

Tout agrégat classique accepte OVER :

• sum(), avg(), min(), max(), count()
• bool_and(), bool_or(), every()
• string_agg(), array_agg(), json_agg()
• Agrégats statistiques : stddev(), variance(), corr(), percentile_cont() (⚠️ non supportés avec OVER — passer par un sous-SELECT ou FILTER)

count(*) OVER () → nombre total de lignes du résultat (utile pour calculer des pourcentages).

🖼️ Frames (sous-ensembles de lignes)

Uniquement avec ORDER BY. Sans frame explicite :

• Avec ORDER BY → frame par défaut : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
• Sans ORDER BY → frame par défaut : toute la partition

OVER (ORDER BY date
      ROWS BETWEEN <début> AND <fin>)

ROWS vs RANGE :

• ROWS : bornes en nombre de lignes physiques
• RANGE : bornes en valeurs logiques — inclut les lignes ex æquo de la ligne courante (important pour sommes cumulées sur dates avec doublons)

EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES : retire des lignes de la frame (ex : total sans la ligne courante).

🔎 Filtrer dans ou autour de la fenêtre

Pas de WHERE dans le OVER — quatre mécanismes selon le besoin.

1. FILTER (WHERE ...) — sous-total calculé sur un sous-ensemble de lignes

Les agrégats fenêtrés acceptent FILTER, qui agit comme un WHERE interne à la fonction : seules les lignes vérifiant la condition alimentent le calcul, mais toutes les lignes reçoivent le résultat.

SELECT vendeur, mois, region, montant,
       sum(montant) FILTER (WHERE region = 'Nord') OVER () AS total_nord,
       sum(montant) FILTER (WHERE region = 'Sud')  OVER () AS total_sud
FROM ventes;
-- chaque ligne affiche le total Nord, même les lignes du Sud

⚠️ FILTER n'accepte que les agrégats (sum, count, avg, string_agg…). Les fonctions de classement (row_number, rank, lag, first_value…) le refusent. Ordre d'écriture : fonction(args) FILTER (WHERE ...) OVER (...).

2. CASE WHEN — l'alternative universelle

Fonctionne partout, y compris pour les filtres que FILTER refuse :

SELECT vendeur, mois, montant,
       sum(CASE WHEN region = 'Nord' THEN montant END) OVER () AS total_nord,
       count(CASE WHEN montant > 100 THEN 1 END) OVER () AS nb_grosses_ventes
FROM ventes;

3. WHERE de la requête — la fenêtre ne voit que certaines lignes

Le WHERE s'exécute avant le calcul des fenêtres : les lignes exclues n'entrent dans aucun calcul (sommes, rangs, count(*) OVER ()…).

-- la fenêtre ne voit que les ventes du Nord
SELECT vendeur, mois, montant,
       row_number() OVER (ORDER BY montant DESC) AS rn
FROM ventes
WHERE region = 'Nord';

Pour conserver toutes les lignes dans la partition mais exclure certaines lignes du calcul, utiliser FILTER ou CASE (options 1 et 2).

4. Filtrer sur le résultat de la fenêtre

Impossible dans la même requête (pas de QUALIFY en PostgreSQL) → imbriquer :

SELECT * FROM (
  SELECT v.*,
         rank() OVER (PARTITION BY region ORDER BY montant DESC) AS rk
  FROM ventes v
) t
WHERE rk <= 3;   -- top 3 par région

🧪 Exemples sur données de démo

CREATE TABLE ventes (region text, vendeur text, mois date, montant int);
INSERT INTO ventes VALUES
  ('Nord', 'Alice', '2026-01-01', 100),
  ('Nord', 'Alice', '2026-02-01', 150),
  ('Nord', 'Bob',   '2026-02-01', 150),
  ('Sud',  'Chloe', '2026-01-01', 200),
  ('Sud',  'Chloe', '2026-02-01', 250);

1. Somme cumulée (running total)

SELECT vendeur, mois, montant,
       sum(montant) OVER (ORDER BY mois) AS cumul
FROM ventes;
-- 2026-01 → 100 ; 2026-02 → 400 (RANGE additionne les ex æquo du 02)

2. Moyenne mobile sur 3 mois

SELECT mois, montant,
       avg(montant) OVER (
         ORDER BY mois
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS mm3
FROM ventes;

3. Comparer au mois précédent (évolution %)

SELECT vendeur, mois, montant,
       lag(montant) OVER (PARTITION BY vendeur ORDER BY mois) AS precedent,
       round(100.0 * montant / lag(montant)
             OVER (PARTITION BY vendeur ORDER BY mois) - 100, 1) AS variation_pct
FROM ventes;

4. Top 1 par groupe (plus grosse vente par région)

SELECT * FROM (
  SELECT v.*,
         row_number() OVER (PARTITION BY region ORDER BY montant DESC) AS rn
  FROM ventes v
) t
WHERE rn = 1;

Variante « tous les ex æquo » : remplacer row_number() par dense_rank() ou rank().

5. Pourcentage du total par région

SELECT vendeur, region, montant,
       round(100.0 * montant
             / sum(montant) OVER (PARTITION BY region), 1) AS pct_region
FROM ventes;

6. Quartiles

SELECT vendeur, montant,
       ntile(4) OVER (ORDER BY montant) AS quartile
FROM ventes;

🍽️ Recettes classiques

⚠️ Pièges fréquents

1. last_value() sans frame explicite s'arrête à la ligne courante → utiliser ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
2. RANGE par défaut avec ORDER BY : les lignes ex æquo sont agrégées ensemble dans les sommes cumulées.
3. Pas d'alias de colonne dans OVER : on ne peut référencer que des colonnes sources (contourner avec un sous-SELECT ou CTE).
4. WHERE ne voit pas les fonctions de fenêtrage : elles s'exécutent après WHERE et GROUP BY. Filtrer sur leur résultat → sous-SELECT, CTE, ou QUALIFY-style avec un wrapper (PostgreSQL n'a pas de QUALIFY).
5. ORDER BY dans OVER ≠ ORDER BY final : l'ordre du résultat doit être fixé par un ORDER BY de requête, pas par la fenêtre.
6. FILTER fonctionne avec les agrégats fenêtrés : sum(x) FILTER (WHERE ...) OVER (...).
7. percentile_cont / percentile_disc refusent OVER (erreur « function is not a window function »).

🏗️ Placement de la clause WINDOW et ordre d'évaluation

Position syntaxique dans la requête

SELECT ...
FROM ...
[JOIN ...]
WHERE ...
GROUP BY ...
HAVING ...
WINDOW ...        -- ← ici, entre HAVING et ORDER BY
ORDER BY ...
LIMIT ...

La clause WINDOW se place après HAVING et avant ORDER BY. C'est une simple définition réutilisable : elle ne calcule rien par elle-même. Les définitions OVER (...) inline restent dans la liste du SELECT.

Ordre d'évaluation logique

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING
→ fonctions de fenêtrage  →  SELECT  →  DISTINCT  →  ORDER BY  →  LIMIT

Les fonctions de fenêtrage sont calculées après WHERE/GROUP BY/HAVING (elles ne voient que les lignes déjà filtrées et groupées) mais avant SELECT, DISTINCT, ORDER BY et LIMIT.

Quelles clauses peuvent utiliser une fonction de fenêtrage ?

C'est pour cela qu'on ne peut ni filtrer ni regrouper sur le résultat d'une fonction de fenêtrage dans la même requête (PostgreSQL n'a pas de QUALIFY contrairement à Snowflake ou DuckDB) : il faut imbriquer. À noter aussi : une ligne éliminée par WHERE n'existe pas pour la fenêtre — elle ne compte ni dans les sommes, ni dans les rangs.

📚 Références officielles

• Window Functions — PostgreSQL Documentation
• Window Function Calls — SQL Reference