Window functions — classer sans réduire
Les window functions (fonctions de fenêtrage) calculent sur un ensemble de lignes sans en supprimer aucune. Contrairement à GROUP BY qui compresse les données (une ligne par groupe), elles conservent chaque ligne du résultat.
Le problème : GROUP BY compresse
Avec GROUP BY, chaque groupe produit une seule ligne :
SELECT region, SUM(montant) AS chiffre_affaires
FROM ventes
GROUP BY region
Résultat : une ligne par région. Impossible de voir le détail des ventes individuelles et le total par région dans le même résultat.
Les window functions résolvent ce problème : chaque ligne garde ses données originales et reçoit un calcul supplémentaire basé sur un ensemble de lignes (la « fenêtre »).
La clause OVER
Toute window function utilise la clause OVER() pour définir sa fenêtre de calcul :
fonction() OVER (
PARTITION BY colonne -- découpage en groupes (optionnel)
ORDER BY colonne -- tri dans chaque fenêtre (requis pour RANK, LAG)
)
PARTITION BY vs GROUP BY
PARTITION BY ressemble à GROUP BY mais ne réduit pas le nombre de lignes. Il définit les groupes sur lesquels la window function opère. Sans PARTITION BY, la fenêtre couvre l'ensemble du résultat.
Exemple simple — ajouter le total régional à chaque ligne de vente :
SELECT
region,
produit,
montant,
SUM(montant) OVER (PARTITION BY region) AS total_region
FROM ventes
Chaque ligne conserve son produit et son montant, mais reçoit en plus le total_region. Pas de GROUP BY, pas de perte de détail.
Quelle est la différence fondamentale entre GROUP BY et une window function avec PARTITION BY ?
RANK(), ROW_NUMBER() et DENSE_RANK()
Ces trois fonctions attribuent un numéro de classement à chaque ligne, mais gèrent les égalités différemment.
Prenons un exemple avec quatre régions et leurs chiffres d'affaires :
| Région | CA | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Île-de-France | 500 000 | 1 | 1 | 1 |
| Auvergne | 300 000 | 2 | 2 | 2 |
| Bretagne | 300 000 | 3 | 2 | 2 |
| Occitanie | 200 000 | 4 | 4 | 3 |
Les différences en cas d'égalité :
- ROW_NUMBER() : jamais de doublons — 1, 2, 3, 4. L'ordre entre les ex æquo est arbitraire.
- RANK() : doublons possibles, avec saut — 1, 2, 2, 4 (le rang 3 est sauté).
- DENSE_RANK() : doublons possibles, sans saut — 1, 2, 2, 3.
SELECT
region,
chiffre_affaires,
ROW_NUMBER() OVER (ORDER BY chiffre_affaires DESC) AS row_num,
RANK() OVER (ORDER BY chiffre_affaires DESC) AS rang,
DENSE_RANK() OVER (ORDER BY chiffre_affaires DESC) AS rang_dense
FROM ca_par_region
Quelle fonction choisir ?
- ROW_NUMBER : quand vous avez besoin d'un numéro unique par ligne (pagination, dédoublonnage)
- RANK : quand les ex æquo doivent avoir le même rang et que les sauts sont acceptables (« 2 régions ex æquo en 2e position, la suivante est 4e »)
- DENSE_RANK : quand les ex æquo doivent avoir le même rang sans saut (« top 3 » = les 3 premières valeurs distinctes)
Avec RANK(), si deux lignes sont à égalité en position 2, quel sera le rang de la ligne suivante ?
Le pattern Top N par groupe
Cas d'usage fréquent : « les 3 meilleurs produits par région ». Ce pattern combine une CTE, une window function et un filtre :
WITH ventes_par_produit AS (
SELECT
region,
produit,
SUM(montant) AS chiffre_affaires
FROM ventes
GROUP BY region, produit
),
avec_rang AS (
SELECT
region,
produit,
chiffre_affaires,
RANK() OVER (
PARTITION BY region
ORDER BY chiffre_affaires DESC
) AS rang
FROM ventes_par_produit
)
SELECT region, produit, chiffre_affaires, rang
FROM avec_rang
WHERE rang <= 3
ORDER BY region, rang
Pourquoi pas un WHERE directement dans la CTE ?
On ne peut pas filtrer sur une window function dans le même SELECT où elle est calculée. La window function n'existe pas encore au moment du WHERE. Il faut donc l'encapsuler dans une CTE ou sous-requête, puis filtrer dans la requête extérieure.
Décomposons le flux :
- CTE 1 : agrégation par région et produit
- CTE 2 : ajout du rang au sein de chaque région (PARTITION BY region)
- SELECT final : filtrage sur le rang
Intégration repository
def get_top_produits_par_region(self, annee=None, top_n=3):
"""Retourne les top N produits par région."""
filtre = f"WHERE annee = {annee}" if annee else ""
query = f"""
WITH ventes_par_produit AS (
SELECT region, produit, SUM(montant) AS chiffre_affaires
FROM ventes
{filtre}
GROUP BY region, produit
),
avec_rang AS (
SELECT
region, produit, chiffre_affaires,
RANK() OVER (
PARTITION BY region
ORDER BY chiffre_affaires DESC
) AS rang
FROM ventes_par_produit
)
SELECT region, produit, chiffre_affaires, rang
FROM avec_rang
WHERE rang <= {top_n}
ORDER BY region, rang
"""
rows = self._execute(query)
return [
{
"region": row[0],
"produit": row[1],
"chiffre_affaires": row[2],
"rang": row[3]
}
for row in rows
]
Visualisation avec facet_col
Pour afficher un sous-graphique par région, Plotly Express propose le paramètre facet_col :
import plotly.express as px
data = repo.get_top_produits_par_region(annee=2024, top_n=3)
df = pd.DataFrame(data)
fig = px.bar(
df,
x="chiffre_affaires",
y="produit",
orientation="h",
facet_col="region",
color="produit",
labels={"chiffre_affaires": "CA (€)", "produit": "Produit"},
title="Top 3 produits par région"
)
fig.update_layout(showlegend=False)
st.plotly_chart(fig, use_container_width=True)
facet_col crée des sous-graphiques
facet_col="region" crée automatiquement un graphique distinct pour chaque valeur unique de la colonne region. Chaque sous-graphique partage les mêmes axes, ce qui facilite la comparaison visuelle entre les groupes.
Pourquoi ne peut-on pas écrire WHERE RANK() OVER (...) <= 3 directement dans la même requête ?
À retenir
Points clés
- Les window functions calculent sur un ensemble de lignes sans réduire le nombre de lignes
- La clause
OVER(PARTITION BY ... ORDER BY ...)définit la fenêtre de calcul - ROW_NUMBER = numéro unique, RANK = sauts en cas d'égalité, DENSE_RANK = pas de sauts
- Le pattern Top N par groupe = CTE d'agrégation → CTE avec RANK → filtre
WHERE rang <= N facet_coldans Plotly Express crée un sous-graphique par valeur unique