Window Functions SQL Data Analyst : 5 Usages Essentiels (avec DuckDB)

Si vous pouvez écrire un GROUP BY, vous êtes data analyst débutant. Si vous maîtrisez les window functions, vous êtes data analyst senior.

C'est aussi simple que ça.

Les window functions sont la fonctionnalité SQL la plus souvent citée dans les entretiens data analyst, la plus utilisée dans les analyses réelles, et paradoxalement la moins enseignée dans les formations généralistes.

Dans cet article, je vous montre les 5 usages concrets que vous rencontrerez chaque semaine en poste.

Qu'est-ce qu'une window function ?

Une window function calcule une valeur pour chaque ligne en se basant sur un groupe de lignes liées, sans pour autant les fusionner comme le ferait un GROUP BY.

-- GROUP BY : une ligne par groupe
SELECT region, SUM(ca) FROM ventes GROUP BY region;

-- Window function : toutes les lignes, avec le total en context
SELECT
    region,
    ca,
    SUM(ca) OVER (PARTITION BY region) as ca_total_region
FROM ventes;

La différence clé : avec GROUP BY, vous perdez le détail. Avec OVER(), vous gardez chaque ligne et vous accédez au contexte agrégé.

La syntaxe générale :

FONCTION() OVER (
    PARTITION BY colonne_de_groupe  -- facultatif : définit les "fenêtres"
    ORDER BY colonne_de_tri         -- facultatif : ordre dans la fenêtre
    ROWS/RANGE BETWEEN ...          -- facultatif : taille du cadre
)

Usage 1 : Récupérer la dernière ligne par groupe (ROW_NUMBER)

Cas concret : Vous avez un log d'événements et voulez la dernière action de chaque utilisateur.

WITH ranked AS (
    SELECT
        user_id,
        action,
        event_date,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY event_date DESC
        ) as rn
    FROM events
)
SELECT user_id, action, event_date
FROM ranked
WHERE rn = 1;

Ce pattern ROW_NUMBER() + WHERE rn = 1 est probablement le pattern SQL le plus utilisé en data analytics. Il apparaît dans :

Les rapports "dernière commande client"
L'extraction du prix le plus récent d'un produit
La session la plus récente d'un utilisateur

Avec DuckDB sur un fichier Parquet directement :

WITH ranked AS (
    SELECT
        customer_id,
        order_amount,
        order_date,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) as rn
    FROM read_parquet('orders.parquet')
)
SELECT customer_id, order_amount, order_date
FROM ranked
WHERE rn = 1;

Aucun chargement en mémoire, DuckDB exécute la fenêtre directement sur le fichier.

Usage 2 : Classement avec gestion des ex-aequo (RANK vs DENSE_RANK)

Cas concret : Classer les commerciaux par CA mensuel, en gérant les égalités correctement.

SELECT
    commercial,
    ca_mensuel,
    RANK() OVER (ORDER BY ca_mensuel DESC)       as rang_avec_saut,
    DENSE_RANK() OVER (ORDER BY ca_mensuel DESC) as rang_sans_saut
FROM performance_commerciale;

Résultat :

commercial	ca_mensuel	rang_avec_saut	rang_sans_saut
Alice	52 000€	1	1
Bob	48 000€	2	2
Claire	48 000€	2	2
David	41 000€	4	3

RANK saute le rang 3 (il y avait deux 2ème). DENSE_RANK ne saute jamais.

Règle pratique :

RANK → quand le rang reflète la position réelle dans un classement (concours, podiums)
DENSE_RANK → quand vous voulez compter les niveaux distincts (catégories, paliers)

NTILE : variante utile pour découper en quartiles/déciles

SELECT
    client_id,
    ca_annuel,
    NTILE(4) OVER (ORDER BY ca_annuel DESC) as quartile
FROM clients;
-- quartile 1 = top 25%, quartile 4 = bottom 25%

Idéal pour segmenter votre base client en high/mid/low value.

Usage 3 : Comparaison avec la ligne précédente (LAG/LEAD)

Cas concret : Calculer la croissance mensuelle du CA par rapport au mois précédent.

SELECT
    mois,
    ca,
    LAG(ca, 1) OVER (ORDER BY mois) as ca_mois_precedent,
    ROUND(
        (ca - LAG(ca, 1) OVER (ORDER BY mois)) /
        LAG(ca, 1) OVER (ORDER BY mois) * 100,
        1
    ) as croissance_pct
FROM ca_mensuel
ORDER BY mois;

Résultat :

mois	ca	ca_mois_precedent	croissance_pct
2025-01	85 000€	NULL	NULL
2025-02	91 000€	85 000€	+7.1%
2025-03	87 500€	91 000€	-3.8%
2025-04	98 200€	87 500€	+12.2%

LAG(col, n) retourne la valeur n lignes en arrière. LEAD(col, n) retourne n lignes en avant.

Cas d'usage avancé : comparaison N vs N-1

SELECT
    mois,
    ca,
    LAG(ca, 12) OVER (ORDER BY mois) as ca_annee_precedente,
    ROUND(
        (ca - LAG(ca, 12) OVER (ORDER BY mois)) /
        LAG(ca, 12) OVER (ORDER BY mois) * 100,
        1
    ) as croissance_yoy
FROM ca_mensuel
ORDER BY mois;

En changeant LAG(ca, 1) en LAG(ca, 12), vous calculez la croissance Year-over-Year en une seule requête.

Usage 4 : Cumul progressif (SUM/AVG sur cadre glissant)

Cas concret : Calculer le CA cumulé depuis le début de l'année et la moyenne mobile sur 3 mois.

CA cumulé (Running Total)

SELECT
    mois,
    ca,
    SUM(ca) OVER (
        ORDER BY mois
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as ca_cumule_ytd
FROM ca_mensuel
WHERE mois >= '2025-01-01';

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = "de la première ligne jusqu'à la ligne courante". C'est le cadre par défaut quand vous spécifiez ORDER BY sans ROWS, mais l'expliciter est une bonne pratique.

Moyenne mobile sur 3 mois

SELECT
    mois,
    ca,
    AVG(ca) OVER (
        ORDER BY mois
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as ca_moyenne_3mois
FROM ca_mensuel;

2 PRECEDING AND CURRENT ROW = les 2 lignes précédentes + la ligne courante = fenêtre de 3.

Par partition (réinitialisation par groupe)

SELECT
    region,
    mois,
    ca,
    SUM(ca) OVER (
        PARTITION BY region      -- réinitialise pour chaque région
        ORDER BY mois
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as ca_cumule_region
FROM ca_mensuel;

Le PARTITION BY fait redémarrer le cumul à zéro pour chaque région.

Usage 5 : Détecter les anomalies et gaps (pattern avancé)

Cas concret : Identifier les jours sans vente dans une série temporelle.

WITH jours_consecutifs AS (
    SELECT
        date_vente,
        LAG(date_vente, 1) OVER (ORDER BY date_vente) as date_precedente,
        date_vente - LAG(date_vente, 1) OVER (ORDER BY date_vente) as gap_jours
    FROM ventes_journalieres
)
SELECT date_precedente, date_vente, gap_jours
FROM jours_consecutifs
WHERE gap_jours > 1
ORDER BY date_precedente;

Ce pattern détecte les interruptions de série — utile pour les alertes de monitoring, l'audit de qualité des données, ou la vérification de SLAs.

Détecter les outliers par écart à la moyenne mobile :

SELECT
    date_vente,
    ca_journalier,
    AVG(ca_journalier) OVER (
        ORDER BY date_vente
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moyenne_7j,
    ca_journalier / AVG(ca_journalier) OVER (
        ORDER BY date_vente
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as ratio_vs_moyenne
FROM ventes_journalieres
HAVING ratio_vs_moyenne > 2 OR ratio_vs_moyenne < 0.5;
-- Lignes avec CA 2x supérieur ou 2x inférieur à la moyenne sur 7 jours

Les 3 erreurs classiques avec les window functions

1. Filtrer sur le résultat de la window function sans CTE

-- ❌ Impossible : WHERE ne peut pas référencer une window function
SELECT *, ROW_NUMBER() OVER (ORDER BY ca) as rn
FROM ventes
WHERE rn = 1;

-- ✅ Encapsuler dans une CTE ou sous-requête
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ca) as rn
    FROM ventes
)
SELECT * FROM ranked WHERE rn = 1;

2. Oublier ORDER BY dans RANK/LAG/SUM cumulatif

-- ❌ RANK sans ORDER BY = tous les rangs valent 1
SELECT RANK() OVER () as rang FROM ventes;

-- ✅ ORDER BY obligatoire pour les fonctions de classement et de décalage
SELECT RANK() OVER (ORDER BY ca DESC) as rang FROM ventes;

3. Confondre ROWS et RANGE

-- ROWS : compte les lignes physiques
SUM(ca) OVER (ORDER BY mois ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- → exactement 3 lignes (current + 2 avant)

-- RANGE : inclut toutes les lignes avec la même valeur d'ORDER BY
SUM(ca) OVER (ORDER BY mois RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
-- → comportement imprévisible si plusieurs lignes ont le même mois

Préférez ROWS dans la majorité des cas — comportement prévisible et plus rapide.

Tester vos window functions avec DuckDB

DuckDB est idéal pour pratiquer les window functions sur vos propres données sans installation serveur :

import duckdb

# Créer des données de test en une ligne
duckdb.sql("""
    WITH ventes AS (
        SELECT * FROM (VALUES
            ('Alice', 'Jan', 12000),
            ('Bob',   'Jan', 9500),
            ('Alice', 'Fév', 14000),
            ('Bob',   'Fév', 11000),
            ('Alice', 'Mar', 13500)
        ) t(commercial, mois, ca)
    )
    SELECT
        commercial,
        mois,
        ca,
        SUM(ca) OVER (PARTITION BY commercial ORDER BY mois) as ca_cumule,
        RANK() OVER (PARTITION BY mois ORDER BY ca DESC) as rang_mensuel,
        LAG(ca, 1) OVER (PARTITION BY commercial ORDER BY mois) as ca_precedent
    FROM ventes
""")

Aucune connexion à une base, aucun fichier. DuckDB exécute tout en mémoire avec la syntaxe SQL standard.

Récapitulatif : quelle window function pour quel besoin ?

Besoin	Fonction	Exemple
Dernière ligne par groupe	`ROW_NUMBER()` + `WHERE rn = 1`	Dernière commande par client
Classement avec ex-aequo	`RANK()` / `DENSE_RANK()`	Top commerciaux
Découper en N groupes égaux	`NTILE(N)`	Quartiles clients
Comparer avec ligne précédente	`LAG(col, n)`	Croissance MoM
Comparer avec ligne suivante	`LEAD(col, n)`	Jours avant prochain événement
Cumul progressif	`SUM() OVER (ORDER BY ...)`	CA Year-to-Date
Moyenne glissante	`AVG() OVER (ROWS BETWEEN N PRECEDING...)`	Tendance sur 7 jours
Détecter gaps / anomalies	`LAG()` + filtre sur écart	Ruptures de série

Les window functions sont au programme de SQL Mastery : 3 modules dédiés couvrent ROW_NUMBER, LAG/LEAD, SUM cumulatif, RANK vs DENSE_RANK et les cadres de fenêtre (ROWS BETWEEN) — tous les patterns de cet article, avec exercices sur jeux de données réels.

Découvrir SQL Mastery →

Window Functions SQL : les 5 usages essentiels du data analyst