🎯 Situation

Un analyste BI m'a demandé comment classer les clients par revenu dans chaque région en SQL — en montrant le classement de chaque client à côté de son revenu individuel et du total. Il avait essayé avec une sous-requête et un GROUP BY et s'en était presque sorti, mais ne parvenait pas à obtenir le classement par région sans regrouper les lignes de détail. Une fonction fenêtre a remplacé toute l'approche sous-requête et tournait 3x plus vite.

👉 Les fonctions fenêtres effectuent des calculs sur un ensemble de lignes liées à la ligne courante — sans regrouper ces lignes en une seule. C'est la distinction clé avec GROUP BY. GROUP BY retourne une ligne par groupe. Les fonctions fenêtres retournent une ligne par ligne originale, avec une colonne calculée supplémentaire.

⚠️ Challenge

📈 Les 4 familles de fonctions fenêtres

  • Classement : ROW_NUMBER(), RANK(), DENSE_RANK() — attribuer un numéro séquentiel aux lignes dans une partition
  • Agrégation : SUM() OVER(), AVG() OVER(), COUNT() OVER() — calculer un total ou une moyenne sans GROUP BY
  • Navigation : LAG(), LEAD() — accéder aux valeurs de la ligne précédente ou suivante (comparaisons mois sur mois)
  • Basé sur un cadre : SUM() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — fenêtres glissantes

🔨 La clause OVER() — comment fonctionnent les fonctions fenêtres

  • PARTITION BY — définit les groupes (comme GROUP BY, mais ne regroupe pas les lignes)
  • ORDER BY — définit l'ordre des lignes dans chaque partition
  • ROWS/RANGE BETWEEN — définit le cadre (combien de lignes précédentes/suivantes inclure)
  • Exemple : SUM(revenu) OVER (PARTITION BY région ORDER BY date) calcule le total cumulatif par région
  • Si PARTITION BY est omis, la fenêtre couvre l'intégralité du jeu de résultats

🔍 Analyse

Les 4 patterns les plus utiles avec du vrai SQL :

-- 1. Rank customers by revenue within each region
SELECT customer_name, region, revenue,
    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS region_rank
FROM customer_revenue;

-- 2. Running total by month
SELECT month, revenue,
    SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_revenue;

-- 3. Month-over-month change (replaces self-join)
SELECT month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

-- 4. 3-month moving average
SELECT month, revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM monthly_revenue;
Ces 4 patterns couvrent la majorité des cas d'utilisation des fonctions fenêtres en BI. Le pattern de classement seul remplace le pattern de sous-requête 'top N par groupe' le plus courant qui ralentit de nombreux rapports en production.

✓️ Bonne pratique

Quand utiliser les fonctions fenêtres vs. d'autres approches :

  • Totaux cumulatifs et moyennes mobiles → toujours utiliser les fonctions fenêtres, pas les sous-requêtes
  • Rankings dans des groupes → RANK() OVER (PARTITION BY) remplace les sous-requêtes corrélées
  • Mois sur mois → LAG() remplace les auto-jointures sur la même table
  • Pourcentage du total → SUM(revenu) OVER () au dénominateur (sans PARTITION BY = somme de toute la table)
  • Top N par catégorie → ROW_NUMBER() dans un CTE, puis filtrer WHERE row_num <= N

💡 Synthèse

Les fonctions fenêtres sont le plus grand gain de productivité SQL pour les analystes BI qui connaissent déjà GROUP BY et les JOINs. Elles éliminent les patterns de sous-requêtes les plus alambiqués et rendent des calculs comme les totaux cumulatifs, les moyennes mobiles et les rankings par groupe une seule ligne. La syntaxe de la clause OVER() semble étrange la première fois — elle a tout son sens après la deuxième.

👉 Si vous connaissez GROUP BY et JOIN, vous êtes à 90 % du chemin vers les fonctions fenêtres.

La clause OVER() est les 10 % qui rendent les autres 90 % deux fois plus puissants.