Qu'est-ce que les fonctions de fenêtre (window functions) ?

Question

Accepted Answer

Les **fonctions de fenêtre** effectuent des calculs sur un ensemble de lignes (une « fenêtre ») **relatives à la ligne actuelle**, *sans les réduire à une seule ligne de sortie* (contrairement aux agrégats avec `GROUP BY`). Elles permettent des analyses puissantes — totaux cumulés, classements, comparaisons avec d'autres lignes — tout en conservant chaque ligne dans le résultat.

## La différence clé avec GROUP BY

```text
GROUP BY      → collapses groups into ONE row each (you lose individual rows)
Window function → computes across related rows but KEEPS every row
→ You get per-row detail AND aggregate/ranking info together.
```

## Fonctions de classement

```sql
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,     -- unique sequential rank
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,  -- rank within dept
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
```

La clause `OVER (...)` définit la fenêtre. **`PARTITION BY`** divise les lignes en groupes (comme GROUP BY, mais par ligne), et **`ORDER BY`** ordonne au sein de chaque partition. `ROW_NUMBER`/`RANK`/`DENSE_RANK` attribuent des classements.

## Fonctions agrégat de fenêtre (totaux cumulés, agrégats par groupe)

```sql
SELECT
  date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,            -- cumulative sum
  AVG(amount) OVER (PARTITION BY category) AS category_avg,      -- avg per category, kept per row
  amount - AVG(amount) OVER (PARTITION BY category) AS diff_from_avg
FROM transactions;
```

Les agrégats comme fonctions de fenêtre calculent des totaux cumulés, des moyennes mobiles ou des agrégats par partition tout en conservant chaque ligne — impossible avec GROUP BY seul.

## LAG/LEAD — comparer avec d'autres lignes

```sql
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,             -- the previous row's value
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth,        -- month-over-month change
  LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;
```

**`LAG`/`LEAD`** accèdent aux valeurs des lignes précédentes/suivantes — parfaits pour les comparaisons d'une période à l'autre (croissance mois après mois, etc.).

## Pourquoi c'est important

Les fonctions de fenêtre sont une fonctionnalité SQL puissante et avancée, essentielle pour les **requêtes analytiques**, et leur compréhension est une connaissance précieuse au niveau senior pour l'analyse de données, les rapports et les requêtes complexes.

Leur capacité distinctive — effectuer des calculs sur des lignes liées **tout en conservant chaque ligne** (contrairement à GROUP BY, qui réduit les lignes) — permet toute une classe d'analyses difficiles ou impossibles autrement : **classements** (`ROW_NUMBER`/`RANK`/`DENSE_RANK` — trouver les N meilleurs par groupe, attribuer des rangs), **totaux cumulés et moyennes mobiles** (sommes cumulées, agrégats par partition affichés aux côtés de chaque ligne), et **comparaisons ligne par ligne** (`LAG`/`LEAD` pour l'analyse d'une période à l'autre, comme la croissance mois après mois).

Ce sont des besoins analytiques courants et précieux (tableaux de classement, métriques cumulées, analyse des tendances, comparaison de chaque ligne avec la moyenne de son groupe) que les fonctions de fenêtre gèrent élégamment en une seule requête, où le SQL pré-fenêtre aurait nécessité des auto-jointures complexes ou des sous-requêtes.

Comprendre la syntaxe `OVER (PARTITION BY ... ORDER BY ...)` (définir la fenêtre), les fonctions de classement, les fonctions agrégat de fenêtre (totaux cumulés, agrégats par partition), et `LAG`/`LEAD` (accéder à d'autres lignes) couvre la boîte à outils analytique puissante.

Comme l'analyse de données et les rapports sophistiqués sont des utilisations importantes de SQL, et comme les fonctions de fenêtre sont le moyen moderne et puissant d'exprimer les classements, les calculs exécutés et les comparaisons de lignes (nettement plus puissants et élégants que les alternatives), maîtriser les fonctions de fenêtre est une connaissance précieuse et fréquemment appliquée pour le SQL analytique qui distingue les développeurs capables d'écrire des requêtes de données sophistiquées — une marque de compétence SQL avancée et un sujet courant dans les rôles axés sur les données et les entretiens.