---
jupytext:
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.16.0
kernelspec:
  name: python3
  display_name: Python 3
---

# Fonctions fenêtrées

Les fonctions fenêtrées (*window functions*) sont l'une des fonctionnalités les plus puissantes de SQL. Contrairement aux fonctions d'agrégat classiques, elles **ne réduisent pas** le nombre de lignes : chaque ligne conserve son identité et reçoit une valeur calculée sur un sous-ensemble de lignes appelé **fenêtre**.

```{code-cell} python
:tags: [hide-input]

import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import seaborn as sns

sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1)
```

```{code-cell} python
conn = sqlite3.connect(":memory:")

conn.executescript("""
CREATE TABLE employes (
    id          INTEGER PRIMARY KEY,
    nom         TEXT NOT NULL,
    region      TEXT NOT NULL,
    departement TEXT NOT NULL,
    salaire     REAL NOT NULL
);

CREATE TABLE ventes_mensuelles (
    id          INTEGER PRIMARY KEY,
    employe_id  INTEGER REFERENCES employes(id),
    mois        TEXT NOT NULL,   -- 'YYYY-MM'
    montant     REAL NOT NULL
);

INSERT INTO employes VALUES
    (1,'Alice','Nord','Informatique',62000),
    (2,'Bob','Nord','Informatique',58000),
    (3,'Carla','Sud','Marketing',52000),
    (4,'David','Sud','Marketing',49000),
    (5,'Eva','Nord','Finance',71000),
    (6,'Fabien','Nord','Finance',68000),
    (7,'Gina','Sud','Informatique',55000),
    (8,'Hugo','Sud','Finance',60000),
    (9,'Iris','Nord','Marketing',47000),
    (10,'Jules','Sud','Marketing',51000);

INSERT INTO ventes_mensuelles VALUES
    (1,1,'2024-01',12400),(2,1,'2024-02',15200),(3,1,'2024-03',11000),
    (4,1,'2024-04',14500),(5,1,'2024-05',13800),(6,1,'2024-06',16200),
    (7,2,'2024-01', 9800),(8,2,'2024-02',10500),(9,2,'2024-03', 8900),
    (10,2,'2024-04',11200),(11,2,'2024-05',10800),(12,2,'2024-06',12000),
    (13,3,'2024-01',22000),(14,3,'2024-02',19500),(15,3,'2024-03',21000),
    (16,3,'2024-04',23500),(17,3,'2024-05',20800),(18,3,'2024-06',25000),
    (19,4,'2024-01',17000),(20,4,'2024-02',14000),(21,4,'2024-03',15500),
    (22,4,'2024-04',16800),(23,4,'2024-05',15200),(24,4,'2024-06',18000),
    (25,5,'2024-01',31000),(26,5,'2024-02',28500),(27,5,'2024-03',33000),
    (28,5,'2024-04',35000),(29,5,'2024-05',29500),(30,5,'2024-06',37000),
    (31,6,'2024-01',26000),(32,6,'2024-02',29000),(33,6,'2024-03',24000),
    (34,6,'2024-04',27500),(35,6,'2024-05',31000),(36,6,'2024-06',28000);
""")

pd.read_sql("SELECT * FROM employes ORDER BY region, departement", conn)
```

## La clause OVER — principe

```{prf:definition}
:label: ch08-def-over
La clause **`OVER`** transforme une fonction d'agrégat ou de classement en **fonction fenêtrée**. Elle définit la **fenêtre** : l'ensemble de lignes sur lesquelles la fonction opère pour calculer la valeur de chaque ligne.

Syntaxe générale :
`fonction() OVER ([PARTITION BY ...] [ORDER BY ...] [frame_clause])`
```

```{prf:theorem}
:label: ch08-thm-groupby-vs-over
Différence fondamentale entre `GROUP BY` et `OVER` :
- `GROUP BY` réduit N lignes à 1 ligne par groupe — les lignes individuelles disparaissent.
- `OVER` laisse toutes les lignes intactes et ajoute une colonne calculée sur la fenêtre définie pour chaque ligne.
```

```{code-cell} python
# Illustration : même calcul avec GROUP BY (réduit) vs OVER (conserve les lignes)
print("=== Avec GROUP BY (résumé) ===")
display(pd.read_sql("""
SELECT departement, ROUND(AVG(salaire),2) AS moy_dept
FROM employes
GROUP BY departement
""", conn))

print("=== Avec OVER (toutes les lignes conservées) ===")
display(pd.read_sql("""
SELECT
    nom,
    departement,
    salaire,
    ROUND(AVG(salaire) OVER (PARTITION BY departement), 2) AS moy_dept
FROM employes
ORDER BY departement, salaire DESC
""", conn))
```

```{prf:remark}
:label: ch08-rem-over-sans-partition
`OVER ()` sans `PARTITION BY` ni `ORDER BY` définit une fenêtre couvrant **toutes** les lignes du résultat. C'est utile pour calculer un total global ou une moyenne globale tout en conservant le détail ligne par ligne.
```

## PARTITION BY et ORDER BY dans la fenêtre

```{prf:definition}
:label: ch08-def-partition-by
**`PARTITION BY`** divise les lignes en partitions indépendantes. La fonction fenêtrée est appliquée séparément dans chaque partition, comme un `GROUP BY` qui ne réduirait pas les lignes.
```

```{prf:definition}
:label: ch08-def-order-by-over
**`ORDER BY`** dans la clause `OVER` définit l'ordre de traitement au sein de chaque partition. Il est indispensable pour les fonctions de classement (`RANK`, `ROW_NUMBER`) et les fonctions de décalage (`LAG`, `LEAD`). Il définit aussi le **cadre de la fenêtre** par défaut (de la première ligne au rang courant).
```

```{code-cell} python
# PARTITION BY region + ORDER BY salaire : rang de chaque employé dans sa région
pd.read_sql("""
SELECT
    nom,
    region,
    departement,
    salaire,
    RANK() OVER (PARTITION BY region ORDER BY salaire DESC) AS rang_region,
    RANK() OVER (ORDER BY salaire DESC)                     AS rang_global
FROM employes
ORDER BY region, rang_region
""", conn)
```

```{prf:example}
:label: ch08-ex-partition-multi
On peut appliquer plusieurs fonctions fenêtrées avec des partitions différentes dans la même requête. Par exemple, calculer simultanément le rang dans la région et le rang global, en utilisant deux clauses `OVER` distinctes.
```

## Fonctions de classement

```{prf:definition}
:label: ch08-def-row-number
**`ROW_NUMBER()`** attribue un numéro séquentiel unique à chaque ligne dans la partition, sans ex-aequo. En cas d'égalité de valeur, l'ordre d'attribution est arbitraire (dépend de l'implémentation).
```

```{prf:definition}
:label: ch08-def-rank-dense-rank
- **`RANK()`** attribue le même rang aux lignes ex-aequo, puis saute les rangs suivants (1, 2, 2, 4…).
- **`DENSE_RANK()`** attribue le même rang aux ex-aequo sans sauter de rang (1, 2, 2, 3…).
- **`NTILE(n)`** divise les lignes en n groupes de taille approximativement égale et retourne le numéro du groupe (1 à n).
```

```{code-cell} python
# Comparaison ROW_NUMBER / RANK / DENSE_RANK / NTILE sur les salaires
pd.read_sql("""
SELECT
    nom,
    salaire,
    ROW_NUMBER()  OVER (ORDER BY salaire DESC) AS row_num,
    RANK()        OVER (ORDER BY salaire DESC) AS rank_,
    DENSE_RANK()  OVER (ORDER BY salaire DESC) AS dense_rank_,
    NTILE(3)      OVER (ORDER BY salaire DESC) AS quartile_3
FROM employes
ORDER BY salaire DESC
""", conn)
```

```{prf:example}
:label: ch08-ex-top-n-par-groupe
`ROW_NUMBER()` avec `PARTITION BY` permet d'obtenir les N meilleurs enregistrements **par groupe**, un besoin fréquent en analyse.
```

```{code-cell} python
# Top 2 vendeurs par région (total sur la période)
pd.read_sql("""
WITH totaux AS (
    SELECT
        e.nom,
        e.region,
        SUM(v.montant) AS total_ventes
    FROM ventes_mensuelles v
    JOIN employes e ON v.employe_id = e.id
    GROUP BY e.id, e.nom, e.region
),
classement AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_ventes DESC) AS rang
    FROM totaux
)
SELECT nom, region, ROUND(total_ventes,2) AS total_ventes, rang
FROM classement
WHERE rang <= 2
ORDER BY region, rang
""", conn)
```

```{prf:remark}
:label: ch08-rem-ntile-inegalite
`NTILE(n)` répartit les lignes le plus équitablement possible. Si le nombre de lignes n'est pas divisible par n, les premiers groupes reçoivent une ligne de plus que les derniers. Exemple : 10 lignes, `NTILE(3)` → groupes de 4, 3, 3 lignes.
```

## Fonctions de décalage — LAG et LEAD

```{prf:definition}
:label: ch08-def-lag-lead
- **`LAG(col, n, defaut)`** retourne la valeur de `col` à n lignes **en arrière** dans la partition (par rapport à l'ordre défini).
- **`LEAD(col, n, defaut)`** retourne la valeur de `col` à n lignes **en avant**.
- Ces fonctions sont indispensables pour calculer des variations période sur période.
```

```{code-cell} python
# Évolution mensuelle des ventes d'Alice (employe_id = 1)
pd.read_sql("""
SELECT
    mois,
    montant,
    LAG(montant, 1)  OVER (ORDER BY mois) AS mois_precedent,
    ROUND(montant - LAG(montant, 1) OVER (ORDER BY mois), 2) AS variation,
    ROUND(
        100.0 * (montant - LAG(montant, 1) OVER (ORDER BY mois))
        / LAG(montant, 1) OVER (ORDER BY mois),
    1) AS variation_pct
FROM ventes_mensuelles
WHERE employe_id = 1
ORDER BY mois
""", conn)
```

```{prf:example}
:label: ch08-ex-lag-lead-comparison
`LAG` et `LEAD` acceptent un troisième argument : la valeur par défaut retournée lorsqu'il n'y a pas de ligne précédente/suivante (début ou fin de partition). Sans ce défaut, la valeur retournée est `NULL`.
```

## Agrégats fenêtrés et cadre de fenêtre

```{prf:definition}
:label: ch08-def-frame-clause
Le **cadre de fenêtre** (*frame clause*) précise quelles lignes de la partition sont incluses dans le calcul pour chaque ligne courante. Deux modes existent :
- `ROWS BETWEEN` : basé sur des positions physiques de lignes.
- `RANGE BETWEEN` : basé sur des plages de valeurs.

Exemples courants :
- `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` — de la première ligne jusqu'à la ligne courante (cumul progressif).
- `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` — fenêtre glissante de 3 lignes.
```

```{prf:remark}
:label: ch08-rem-frame-defaut
Quand `ORDER BY` est présent dans `OVER` mais qu'aucun cadre n'est spécifié, le cadre par défaut est `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Sans `ORDER BY`, le cadre est toute la partition.
```

```{code-cell} python
# Cumul progressif et moyenne glissante sur 3 mois pour Alice
pd.read_sql("""
SELECT
    mois,
    montant,
    ROUND(SUM(montant)  OVER (ORDER BY mois
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumul,
    ROUND(AVG(montant)  OVER (ORDER BY mois
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)         AS moy_3_mois
FROM ventes_mensuelles
WHERE employe_id = 1
ORDER BY mois
""", conn)
```

```{code-cell} python
# Ventes globales : running total et moyenne glissante 3 mois, tous employés confondus
df_global = pd.read_sql("""
SELECT
    mois,
    ROUND(SUM(montant), 2)   AS total_mois,
    ROUND(SUM(SUM(montant)) OVER (ORDER BY mois
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumul,
    ROUND(AVG(SUM(montant)) OVER (ORDER BY mois
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)         AS moy_3_mois
FROM ventes_mensuelles
GROUP BY mois
ORDER BY mois
""", conn)
df_global
```

```{prf:remark}
:label: ch08-rem-rows-vs-range
`ROWS BETWEEN` compte des **positions physiques** : `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` inclut exactement la ligne précédente et la suivante, indépendamment des valeurs. `RANGE BETWEEN` compte des **plages de valeurs** : si plusieurs lignes ont la même valeur dans l'`ORDER BY`, elles sont toutes incluses dans le cadre — comportement différent et parfois surprenant.
```

## Cas d'usage avancés

```{prf:example}
:label: ch08-ex-percentile
Le percentile peut être approximé avec `NTILE` ou calculé précisément avec `CUME_DIST()` (disponible dans SQLite >= 3.25).
```

```{code-cell} python
# CUME_DIST : distribution cumulative des salaires
pd.read_sql("""
SELECT
    nom,
    salaire,
    ROUND(CUME_DIST() OVER (ORDER BY salaire), 3) AS cume_dist,
    ROUND(PERCENT_RANK() OVER (ORDER BY salaire), 3) AS percent_rank
FROM employes
ORDER BY salaire
""", conn)
```

```{prf:definition}
:label: ch08-def-running-total
Le **cumul progressif** (*running total*) est la somme de toutes les valeurs depuis le début de la série jusqu'à la ligne courante. En SQL fenêtré : `SUM(col) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`.
```

```{prf:remark}
:label: ch08-rem-median-window
La **médiane mobile** ne dispose pas d'une fonction native simple en SQL standard. On peut l'approximer avec une fenêtre glissante `AVG` ou utiliser des fonctions spécifiques aux SGBD (ex: `PERCENTILE_CONT` en PostgreSQL/SQL Server).
```

## Visualisation — moyenne glissante

```{code-cell} python
:tags: [hide-input]

fig, axes = plt.subplots(2, 1, figsize=(12, 8), sharex=True)

mois_labels = df_global["mois"].tolist()
x = range(len(mois_labels))

# Graphique 1 : ventes mensuelles vs moyenne glissante 3 mois
axes[0].bar(x, df_global["total_mois"], color=sns.color_palette("muted")[0],
            alpha=0.7, label="Ventes mensuelles")
axes[0].plot(x, df_global["moy_3_mois"], color=sns.color_palette("muted")[1],
             marker="o", linewidth=2, label="Moyenne glissante 3 mois")
axes[0].set_title("Ventes mensuelles et moyenne glissante (3 mois)")
axes[0].set_ylabel("Montant (€)")
axes[0].yaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"{v:,.0f} €"))
axes[0].legend()
axes[0].set_xticks(list(x))
axes[0].set_xticklabels(mois_labels, rotation=30, ha="right")

# Graphique 2 : cumul progressif
axes[1].fill_between(x, df_global["cumul"], alpha=0.4,
                     color=sns.color_palette("muted")[2], label="Cumul progressif")
axes[1].plot(x, df_global["cumul"], color=sns.color_palette("muted")[2],
             marker="s", linewidth=2)
axes[1].set_title("Cumul progressif des ventes (running total)")
axes[1].set_ylabel("Cumul (€)")
axes[1].yaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"{v:,.0f} €"))
axes[1].set_xticks(list(x))
axes[1].set_xticklabels(mois_labels, rotation=30, ha="right")
axes[1].legend()

plt.suptitle("Analyse temporelle avec fonctions fenêtrées", fontsize=13, fontweight="bold")
plt.show()
```

## Résumé

```{prf:definition}
:label: ch08-def-synthese
**Récapitulatif des fonctions fenêtrées :**

- `OVER (...)` active le mode fenêtré : toutes les lignes sont conservées.
- `PARTITION BY` divise les données en sous-ensembles indépendants.
- `ORDER BY` dans `OVER` définit l'ordre intra-partition et le cadre par défaut.
- **Classement** : `ROW_NUMBER` (unique), `RANK` (ex-aequo + saut), `DENSE_RANK` (ex-aequo sans saut), `NTILE(n)` (quantiles).
- **Décalage** : `LAG` (passé), `LEAD` (futur) — calculs de variation période à période.
- **Agrégats fenêtrés** : `SUM`, `AVG` avec cadre `ROWS BETWEEN` ou `RANGE BETWEEN` — cumuls, moyennes glissantes.
- **Distribution** : `CUME_DIST`, `PERCENT_RANK` — position relative dans la distribution.
```

| Fonction | Nécessite ORDER BY | Dépend du cadre |
|---|---|---|
| `ROW_NUMBER` | Oui | Non |
| `RANK` / `DENSE_RANK` | Oui | Non |
| `NTILE(n)` | Oui | Non |
| `LAG` / `LEAD` | Oui | Non |
| `SUM` / `AVG` fenêtré | Optionnel | Oui |
| `CUME_DIST` | Oui | Non |