SQL avancé : les window functions que tout développeur devrait connaître

En 2019, sur un dashboard financier, un client me demande un tableau « croissance du CA jour par jour, par région, avec classement ». J’écris 47 lignes de SQL avec trois CTE imbriquées. Temps d’exécution : 8 secondes. Un collègue senior réécrit tout en 6 lignes avec des window functions. Temps d’exécution : 0,3 seconde. Humiliation, puis révélation. Depuis, les fonctions de fenêtrage sont mon outil le plus utilisé en SQL. Cet article reprend les fonctions que j’emploie au quotidien, avec des exemples concrets, et détaille la mécanique souvent mal comprise qui se cache derrière la clause OVER().

Le principe : calculer sans écraser les lignes

Une window function effectue un calcul sur un ensemble de lignes liées à la ligne courante, sans regrouper les résultats. C’est la différence fondamentale avec GROUP BY : là où l’agrégation classique réduit plusieurs lignes en une seule, la fonction de fenêtrage ajoute une colonne tout en conservant chaque ligne individuelle. Vous gardez le détail et vous obtenez en même temps une valeur calculée sur le groupe. Cette double propriété change radicalement la façon d’aborder les rapports analytiques, les classements et les comparaisons temporelles.

L’exemple suivant illustre cette opposition. Le premier requête renvoie une ligne par catégorie ; la seconde renvoie chaque produit accompagné de la moyenne de prix de sa catégorie. Aucune jointure, aucune sous-requête : la moyenne est calculée à la volée pour chaque ligne. Cette syntaxe est standard SQL et fonctionne à l’identique sur PostgreSQL, MySQL 8+, SQL Server et SQLite récent.

-- GROUP BY : 1 ligne par catégorie
SELECT category, AVG(price)
FROM products
GROUP BY category;

-- Window function : chaque produit + la moyenne de SA catégorie
SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category) AS avg_by_category
FROM products;

La clause OVER() : PARTITION BY et ORDER BY

Tout repose sur la clause OVER(), qui définit la « fenêtre » sur laquelle la fonction opère. Deux paramètres en gouvernent le comportement. PARTITION BY découpe le jeu de résultats en groupes indépendants : la fonction redémarre son calcul à chaque nouvelle partition, exactement comme un GROUP BY qui ne fusionnerait pas les lignes. Sans PARTITION BY, la fenêtre couvre l’ensemble du résultat. ORDER BY, lui, impose un ordre à l’intérieur de chaque partition ; il est indispensable dès qu’une notion de séquence intervient, comme le rang, le décalage ou le cumul.

La distinction entre les deux clauses est subtile mais décisive. PARTITION BY répond à la question « par rapport à quel groupe ? », tandis que ORDER BY répond à « dans quel ordre à l’intérieur du groupe ? ». Une erreur fréquente consiste à confondre l’ORDER BY de la fenêtre avec celui de la requête : ce sont deux mécanismes distincts. Le premier détermine le calcul, le second l’affichage final. On peut tout à fait trier la fenêtre par date et afficher le résultat trié par chiffre d’affaires.

-- CA cumulé par région, ordonné par date à l'intérieur de chaque région
SELECT region, sale_date, revenue,
       SUM(revenue) OVER (
           PARTITION BY region      -- un cumul indépendant par région
           ORDER BY sale_date       -- ordre chronologique du cumul
       ) AS running_total
FROM regional_sales
ORDER BY region, sale_date;          -- tri de l'affichage (distinct de la fenêtre)

ROW_NUMBER, RANK et DENSE_RANK : numéroter et classer

Ces trois fonctions de classement représentent à elles seules une large part de mes usages. ROW_NUMBER() attribue un numéro strictement séquentiel — 1, 2, 3, 4 — sans jamais d’ex æquo, ce qui en fait le couteau suisse du dédoublonnage, de la pagination et du top-N par groupe. RANK() et DENSE_RANK() gèrent en revanche les égalités. Sur des valeurs identiques, RANK() saute des rangs (1, 1, 3) tandis que DENSE_RANK() ne les saute pas (1, 1, 2). On choisit RANK() pour un podium où le nombre de places est limité, et DENSE_RANK() quand on veut compter combien de valeurs distinctes précèdent.

Le cas d’usage le plus courant est le top-N par groupe. On numérote chaque ligne à l’intérieur de sa partition, puis on filtre sur ce numéro dans une requête englobante — car une window function ne peut pas figurer directement dans une clause WHERE, son calcul intervenant après le filtrage. La CTE ou la sous-requête est donc obligatoire. La requête ci-dessous extrait les trois produits les plus chers de chaque catégorie, un classique des catalogues e-commerce.

-- Top 3 des produits les plus chers par catégorie (PostgreSQL / MySQL 8+)
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY category
               ORDER BY price DESC
           ) AS rn
    FROM products
)
SELECT * FROM ranked
WHERE rn <= 3;

LAG et LEAD : comparer une ligne à ses voisines

Comparer une période à la précédente — N contre N-1 — est l’un des besoins analytiques les plus répandus, et c’est là que LAG() et LEAD() excellent. LAG() récupère la valeur d’une ligne antérieure dans la fenêtre, LEAD() celle d’une ligne postérieure. Avant les window functions, ce type de comparaison imposait une auto-jointure coûteuse et fastidieuse. Désormais, une seule ligne de code suffit pour ramener la valeur d’hier à côté de celle d’aujourd’hui, ce qui simplifie spectaculairement les calculs de variation.

Les deux fonctions acceptent un second argument optionnel (le décalage, 1 par défaut) et un troisième (la valeur de remplacement quand la ligne voisine n’existe pas, utile pour éviter une division par NULL sur la première ligne). L’exemple calcule la croissance du chiffre d’affaires d’un jour sur l’autre. Notez qu’on répète l’expression LAG(revenue) ; certains moteurs permettent de la factoriser via une fenêtre nommée avec la clause WINDOW, plus lisible sur les requêtes complexes.

-- Croissance du CA jour par jour (N vs N-1)
SELECT sale_date,
       revenue,
       LAG(revenue) OVER (ORDER BY sale_date) AS yesterday,
       ROUND(
           (revenue - LAG(revenue) OVER (ORDER BY sale_date))
           / LAG(revenue) OVER (ORDER BY sale_date) * 100
       , 2) AS growth_pct
FROM daily_revenue
ORDER BY sale_date;

Agrégats fenêtrés et cadre de fenêtre : ROWS / RANGE

Les fonctions d’agrégation classiques — SUM, AVG, COUNT, MIN, MAX — deviennent des window functions dès qu’on leur ajoute une clause OVER(). C’est ici qu’intervient la notion la plus puissante et la plus mal connue : le cadre de fenêtre, défini par ROWS BETWEEN ... AND ... ou RANGE BETWEEN ... AND .... Ce cadre précise quelles lignes, autour de la ligne courante, entrent dans le calcul. Sans cadre explicite mais avec un ORDER BY, la plupart des moteurs appliquent par défaut RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, soit un total cumulé.

La différence entre ROWS et RANGE est essentielle. ROWS compte un nombre physique de lignes (les 6 précédentes, par exemple), ce qui convient parfaitement aux moyennes glissantes. RANGE raisonne en valeurs logiques selon la colonne d’ORDER BY : toutes les lignes partageant la même valeur sont regroupées dans le même cadre, ce qui peut surprendre en présence de doublons de dates. Pour une moyenne mobile fiable, préférez systématiquement ROWS.

-- Moyenne mobile sur 7 jours + total cumulé (standard SQL)
SELECT sale_date,
       amount,
       AVG(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- 7 lignes physiques
       ) AS rolling_7d_avg,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- cumul depuis le début
       ) AS running_total
FROM transactions
ORDER BY sale_date;

NTILE : segmenter une population

NTILE(n) répartit les lignes ordonnées en n groupes de taille la plus égale possible et renvoie le numéro de groupe de chaque ligne. C’est l’outil de la segmentation analytique : quartiles de dépense, déciles de performance, tranches de score. Là où une découpe manuelle par seuils fixes (« moins de 100 €, de 100 à 500 € ») exige de connaître la distribution à l’avance, NTILE s’adapte automatiquement aux données réelles et garantit des groupes équilibrés, ce qui le rend précieux pour le scoring RFM ou la priorisation commerciale.

Quand le nombre de lignes n’est pas divisible par n, le moteur place les lignes excédentaires dans les premiers groupes, qui comptent alors un élément de plus. Ce comportement est déterministe mais dépend strictement de l’ORDER BY : deux exécutions avec un ordre ambigu peuvent produire des affectations différentes pour les lignes à la frontière. La requête suivante classe les clients en quatre quartiles de dépense, du plus gros au plus petit contributeur.

-- Clients répartis en 4 quartiles de dépense (1 = top dépensiers)
SELECT customer_id,
       total_spent,
       NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customers
ORDER BY total_spent DESC;

FIRST_VALUE et LAST_VALUE : premières et dernières lignes

FIRST_VALUE() et LAST_VALUE() renvoient respectivement la première et la dernière valeur du cadre de fenêtre. Elles sont idéales pour ramener, sur chaque ligne, une valeur de référence du groupe : le premier prix observé, le dernier statut connu, la valeur d’ouverture face à celle de clôture. FIRST_VALUE est simple d’emploi, mais LAST_VALUE réserve un piège majeur qui piège presque tous les débutants et mérite qu’on s’y arrête sérieusement.

Avec un ORDER BY, le cadre par défaut s’arrête à la ligne courante (CURRENT ROW). LAST_VALUE renvoie donc… la ligne courante elle-même, et non la vraie dernière ligne de la partition. Pour obtenir le dernier élément réel, il faut élargir explicitement le cadre jusqu’à UNBOUNDED FOLLOWING. Cette subtilité, source de bugs silencieux dans les rapports, illustre à quel point la maîtrise du cadre de fenêtre est indissociable de celle des fonctions elles-mêmes.

-- Premier et dernier prix par produit sur toute sa partition
SELECT product_id, sale_date, price,
       FIRST_VALUE(price) OVER w AS first_price,
       LAST_VALUE(price)  OVER w AS last_price
FROM price_history
WINDOW w AS (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- indispensable pour LAST_VALUE
);

Cas concrets et pièges à éviter

Récapitulons les usages réels qui justifient l’investissement. La déduplication s’obtient en gardant les lignes où ROW_NUMBER() vaut 1 pour une partition correspondant à la clé métier — la méthode la plus propre pour purger des doublons. Le top-N par groupe combine ROW_NUMBER ou RANK et un filtre. Le total cumulé et la moyenne mobile reposent sur SUM/AVG avec un cadre ROWS. La comparaison N vs N-1 mobilise LAG/LEAD. La segmentation revient à NTILE. Ces cinq motifs couvrent l’immense majorité des besoins de reporting analytique.

Côté pièges, retenez trois règles. Premièrement, un PARTITION BY sur une colonne unique crée des partitions d’une seule ligne : la fonction ne sert alors à rien. Deuxièmement, l’absence d’ORDER BY rend non déterministes toutes les fonctions sensibles à l’ordre ; mettez-en toujours un dès qu’il y a un rang, un décalage ou un cumul. Troisièmement, la performance : sur 10 millions de lignes et plus, créez un index composite alignant les colonnes de PARTITION BY puis d’ORDER BY, afin que le moteur évite un tri coûteux. Pour approfondir l’impact des index sur ces requêtes, la référence de Markus Winand reste incontournable.

Sources

• PostgreSQL — Tutorial : Window Functions
• PostgreSQL — Window Functions (référence des fonctions)
• MySQL 8.0 — Window Functions Reference
• Microsoft Learn — SQL Server : clause OVER (Transact-SQL)
• SQLite — Window Functions
• Use The Index, Luke! — indexation et performance SQL