data / sql

Window functions

Leçon 121 exercice

Explication

Ce que vous allez apprendre

Comprendre la différence fondamentale entre GROUP BY (fusionne) et une window function (garde chaque ligne)
Utiliser PARTITION BY et ORDER BY pour définir une fenêtre de calcul
Classer des lignes avec ROW_NUMBER, RANK et DENSE_RANK
Accéder à la ligne précédente/suivante avec LAG/LEAD
Calculer une somme cumulative ou une moyenne mobile avec SUM/AVG en fenêtre

Dans quel contexte ?

Un data analyst doit produire un classement du top 3 des produits les plus chers de chaque catégorie dans la table produits, tout en gardant le prix de chaque produit visible individuellement dans le résultat. GROUP BY seul ferait disparaître le détail ligne par ligne : c'est exactement le problème que résolvent les window functions présentées ici, un grand classique des entretiens techniques SQL.

Prérequis

Cette leçon suppose que GROUP BY/HAVING (leçon 4) et les CTE (leçon 11) sont acquis, car les deux se combinent très souvent avec les window functions.

D'abord, une limite frustrante de GROUP BY

GROUP BY résume les lignes en groupes, mais fait disparaître le détail ligne par ligne. Si on veut connaître "le total des ventes de la catégorie" tout en gardant chaque produit visible individuellement, GROUP BY seul ne le permet pas.

Il faut un outil qui calcule sans fusionner

Les window functions (fonctions de fenêtrage) résolvent exactement ce problème : elles calculent une valeur agrégée ou un rang, sans réduire le nombre de lignes du résultat. Chaque ligne garde son identité tout en "voyant" un calcul fait sur son groupe.

Une analogie pour bien fixer l'idée

Pense à un classement sportif par catégorie d'âge : chaque athlète garde sa propre ligne dans le tableau des résultats, mais reçoit en plus son rang au sein de sa catégorie.

Les deux réglages qui pilotent une window function

PARTITION BY découpe les lignes en fenêtres, comme GROUP BY découpe en groupes, mais sans fusionner les lignes. ORDER BY, à l'intérieur de la fenêtre, définit l'ordre de traitement, indispensable pour classer ou accéder à la ligne voisine.

Une fois ces deux réglages compris, un piège attend

RANK et DENSE_RANK gèrent différemment les ex-aequo : RANK saute des positions après une égalité (1, 2, 2, 4), DENSE_RANK ne saute rien (1, 2, 2, 3). Le choix dépend du sens métier voulu.

Fonction	Comportement	Sur des ex-aequo (deux 2e places)
`ROW_NUMBER()`	Numérote sans jamais d'égalité	1, 2, 3, 4
`RANK()`	Égalité autorisée, saute des positions ensuite	1, 2, 2, 4
`DENSE_RANK()`	Égalité autorisée, ne saute rien	1, 2, 2, 3
`LAG`/`LEAD`	Valeur de la ligne précédente/suivante	utile pour calculer une variation

Bonne pratique

Pour un classement "top N par groupe" (très demandé en entretien), utilise ROW_NUMBER() OVER (PARTITION BY categorie ORDER BY prix DESC) dans une CTE ou une sous-requête, puis filtre WHERE rang <= 3 à l'extérieur : WHERE ne peut jamais référencer directement une window function dans la même requête.

Et la suite

Cette leçon combine naturellement les acquis du tri, des agrégations et des CTE vus précédemment, pour des analyses bien plus riches que ce que permettait GROUP BY seul.

Commandes & code

Window functions (fonctions de fenêtrage)

-- Contrairement à GROUP BY, une window function NE réduit PAS le nombre de lignes
-- Elle calcule une valeur "par fenêtre" tout en gardant le détail ligne par ligne

-- ROW_NUMBER : numérote les lignes selon un tri, par partition
SELECT
    nom, categorie, prix,
    ROW_NUMBER() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang
FROM produits;

-- RANK vs DENSE_RANK : gestion des ex-aequo
SELECT
    nom, prix,
    RANK()       OVER (ORDER BY prix DESC) AS rang,        -- saute des rangs après un ex-aequo (1,2,2,4)
    DENSE_RANK() OVER (ORDER BY prix DESC) AS rang_dense    -- ne saute pas (1,2,2,3)
FROM produits;

-- Top 3 par catégorie (pattern très courant en entretien technique)
SELECT * FROM (
    SELECT
        nom, categorie, prix,
        ROW_NUMBER() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang
    FROM produits
) t
WHERE rang <= 3;

-- Agrégats en fenêtre : total et moyenne SANS perdre le détail
SELECT
    nom, categorie, prix,
    SUM(prix)  OVER (PARTITION BY categorie) AS total_categorie,
    AVG(prix)  OVER (PARTITION BY categorie) AS moyenne_categorie,
    prix - AVG(prix) OVER (PARTITION BY categorie) AS ecart_a_la_moyenne
FROM produits;

-- LAG / LEAD : accéder à la ligne précédente / suivante
SELECT
    client_id, cree_le, montant,
    LAG(montant)  OVER (PARTITION BY client_id ORDER BY cree_le) AS montant_precedent,
    LEAD(montant) OVER (PARTITION BY client_id ORDER BY cree_le) AS montant_suivant,
    montant - LAG(montant) OVER (PARTITION BY client_id ORDER BY cree_le) AS variation
FROM commandes;

-- Somme cumulative (running total)
SELECT
    cree_le, montant,
    SUM(montant) OVER (ORDER BY cree_le) AS cumul
FROM commandes
ORDER BY cree_le;

-- Moyenne mobile sur 7 jours (fenêtre glissante explicite)
SELECT
    jour, ventes_du_jour,
    AVG(ventes_du_jour) OVER (
        ORDER BY jour
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moyenne_mobile_7j
FROM ventes_journalieres;

-- NTILE : répartir en N groupes de taille égale (quartiles, déciles)
SELECT
    nom, prix,
    NTILE(4) OVER (ORDER BY prix) AS quartile
FROM produits;

-- FIRST_VALUE / LAST_VALUE
SELECT
    nom, categorie, prix,
    FIRST_VALUE(nom) OVER (PARTITION BY categorie ORDER BY prix DESC) AS produit_plus_cher
FROM produits;

Résumé

Window function = agrégat/rang calculé sans réduire le nombre de lignes (contrairement à GROUP BY).
PARTITION BY découpe en groupes, ORDER BY définit l'ordre au sein de chaque partition.
ROW_NUMBER/RANK/DENSE_RANK pour classer, LAG/LEAD pour comparer à la ligne voisine.
ROWS BETWEEN ... AND ... définit une fenêtre glissante (moyenne mobile, cumul).
Pattern classique : ROW_NUMBER() OVER (PARTITION BY ...) en sous-requête + filtre externe pour un "top N par groupe".

Exercices pratiques

1 disponible

Mission : construire le top 3 des produits les plus chers par catégorie

Objectif : Utiliser une window function pour classer sans fusionner les lignes, et diagnostiquer une erreur classique de filtrage.

Contexte

Un data analyst veut un tableau de bord qui montre le top 3 des produits les plus chers de chaque catégorie de la table produits, tout en gardant chaque produit visible individuellement (pas de résumé par catégorie). Il a déjà essayé GROUP BY categorie et s'est rendu compte que ça ne donne pas du tout ce qu'il cherche.

Résoudre l’exercice →