WINDOW FUNCTION VISUALIZER

Les fonctions de fenêtre (window functions) sont parmi les fonctionnalités SQL les plus puissantes et les plus mal comprises, précisément parce que leur comportement dépend d’une notion invisible : la fenêtre de calcul, c’est-à-dire l’ensemble des lignes que le moteur considère pour chaque résultat. L’outil rend cette fenêtre visible en colorant en vert la colonne calculée pour chaque ligne, en surlignant en bleu les changements de partition, et en affichant NULL en italique gris là où aucune valeur n’existe (première ligne de LAG, dernière ligne de LEAD). Les 12 fonctions sont regroupées en trois catégories — classement, agrégation en fenêtre, navigation — et chacune est accompagnée de la requête SQL exacte et d’une explication du comportement attendu. L’apprenant passe de la théorie à l’observation en un clic, sans avoir à installer un SGBD.

La différence fondamentale entre window functions et GROUP BY

La confusion la plus fréquente avec les fonctions de fenêtre est de les assimiler à GROUP BY. GROUP BY réduit le nombre de lignes : huit employés agrégés par département donnent trois lignes de résultat, une par département. Les fonctions de fenêtre ne réduisent rien : elles ajoutent une colonne calculée sur chaque ligne individuelle tout en conservant l’intégralité des données. Dans l’outil, SUM() OVER(PARTITION BY dept) retourne toujours 8 lignes — une par employé — avec la somme des salaires du département répétée sur chaque ligne de ce département.

La clause OVER() est ce qui transforme une fonction d’agrégation ordinaire en fonction de fenêtre. Elle accepte trois sous-clauses optionnelles : PARTITION BY (découpe les lignes en groupes indépendants), ORDER BY (définit l’ordre de traitement au sein de chaque groupe), et la définition de cadre ROWS BETWEEN ou RANGE BETWEEN (précise les bornes physiques de la fenêtre autour de la ligne courante).

-- Syntaxe générale d'une window function
SELECT
  nom,
  dept,
  salaire,
  NOM_FONCTION() OVER (
    PARTITION BY dept          -- découpe en partitions
    ORDER BY salaire DESC      -- ordonne dans chaque partition
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- cadre
  ) AS resultat
FROM employes;

Les fonctions de classement

ROW_NUMBER — numérotation unique et séquentielle

ROW_NUMBER() attribue un entier unique à chaque ligne au sein de sa partition, en commençant à 1. La numérotation repart à 1 pour chaque nouveau département. Même si deux lignes ont le même salaire, elles reçoivent des numéros différents — le moteur choisit un ordre arbitraire entre elles si ORDER BY ne les distingue pas complètement. Dans l’outil, les trois employés du département Tech reçoivent les numéros 1, 2 et 3.

ROW_NUMBER() est la fonction de classement la plus utilisée pour récupérer la première ligne d’un groupe. Le pattern classique consiste à l’encapsuler dans une sous-requête ou un CTE et à filtrer sur row_num = 1 pour obtenir, par exemple, l’employé le mieux payé de chaque département.

-- Obtenir le meilleur salaire par département
WITH classement AS (
  SELECT nom, dept, salaire,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salaire DESC) AS rn
  FROM employes
)
SELECT nom, dept, salaire
FROM   classement
WHERE  rn = 1;

RANK et DENSE_RANK — gestion des ex-æquo

RANK() et DENSE_RANK() gèrent les lignes à valeur identique différemment de ROW_NUMBER(). Dans l’outil, Bob et Claire ont tous les deux un salaire de 5 100 dans le département Tech : ils reçoivent tous les deux le rang 1 avec RANK(), et le troisième employé reçoit le rang 3 (pas 2) — deux numéros sont sautés car deux lignes occupent la première position. DENSE_RANK() élimine ce saut : le troisième employé reçoit le rang 2.

NTILE — segmentation en n groupes égaux

NTILE(n) divise les lignes en n groupes de taille aussi égale que possible, numérotés de 1 à n. L’outil permet de changer la valeur de n via un champ numérique et d’appliquer immédiatement le recalcul. Avec n = 4, on obtient des quartiles ; avec n = 10, des déciles. Si le nombre de lignes n’est pas divisible par n, les premiers groupes reçoivent une ligne supplémentaire.

-- Quartiles de salaires
SELECT nom, salaire,
  NTILE(4) OVER (ORDER BY salaire DESC) AS quartile
FROM employes;
-- Quartile 1 = 25% des salaires les plus élevés
-- Quartile 4 = 25% des salaires les plus bas

Les fonctions d’agrégation en fenêtre

SUM, AVG, COUNT avec PARTITION BY

Ces trois fonctions appliquées avec OVER(PARTITION BY dept) calculent respectivement la somme, la moyenne et le nombre de lignes du département, répétés sur chaque ligne individuelle. L’outil montre concrètement que COUNT() OVER(PARTITION BY dept) affiche 3 sur chaque ligne du département Tech (trois employés) et 3 sur chaque ligne de Marketing, sans regrouper les lignes.

La combinaison salaire / SUM(salaire) OVER(PARTITION BY dept) est un usage particulièrement fréquent : elle calcule la part du salaire individuel dans le total du département sur une seule requête, sans jointure ni sous-requête.

-- Part du salaire individuel dans le total du département
SELECT
  nom, dept, salaire,
  SUM(salaire)  OVER (PARTITION BY dept)          AS total_dept,
  COUNT(*)      OVER (PARTITION BY dept)          AS nb_employes,
  ROUND(AVG(salaire) OVER (PARTITION BY dept))    AS moy_dept,
  ROUND(salaire * 100.0 / SUM(salaire) OVER (PARTITION BY dept), 1) AS pct_dept
FROM employes;

SUM cumulatif — totaux glissants

SUM() OVER(ORDER BY mois ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) est le pattern du total cumulatif : chaque ligne affiche la somme de toutes les lignes depuis la première jusqu’à la ligne courante. Dans l’outil, trié par mois, chaque ligne montre le cumul des salaires de tous les mois précédents plus le mois courant. La clause ROWS BETWEEN définit le cadre physique de la fenêtre et est indispensable pour les calculs glissants.

-- Total cumulatif des salaires par mois
SELECT nom, mois, salaire,
  SUM(salaire) OVER (
    ORDER BY mois
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumul
FROM employes;

-- Moyenne glissante sur 3 mois
SELECT nom, mois, salaire,
  AVG(salaire) OVER (
    ORDER BY mois
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moy_3mois
FROM employes;

Les fonctions de navigation

LAG et LEAD — accès aux lignes voisines

LAG(col, n) retourne la valeur de col pour la ligne située n positions avant la ligne courante dans l’ordre défini par ORDER BY. LEAD(col, n) fait l’inverse : n positions après. L’outil affiche NULL en italique gris sur la première ligne de LAG (pas de ligne précédente) et sur la dernière ligne de LEAD (pas de ligne suivante). Ces deux fonctions sont incontournables pour calculer des variations entre périodes consécutives.

-- Variation de salaire par rapport au mois précédent
SELECT nom, mois, salaire,
  LAG(salaire, 1)  OVER (ORDER BY mois) AS salaire_precedent,
  salaire - LAG(salaire, 1) OVER (ORDER BY mois) AS variation
FROM employes;

FIRST_VALUE et LAST_VALUE — valeurs de début et de fin de fenêtre

FIRST_VALUE(col) retourne la valeur de col à la première ligne de la fenêtre pour chaque ligne de la partition. Avec ORDER BY salaire DESC, cela donne le salaire maximum du département répété sur chaque ligne. LAST_VALUE(col) retourne la dernière valeur, mais avec un comportement par défaut qui piège la majorité des débutants : sans clause ROWS BETWEEN, la fenêtre par défaut s’arrête à la ligne courante (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), ce qui fait que LAST_VALUE retourne la valeur de la ligne courante elle-même, pas la dernière de la partition. L’outil l’indique explicitement dans l’explication et inclut la clause complète dans la requête SQL.

-- LAST_VALUE correct — clause ROWS obligatoire
SELECT nom, dept, salaire,
  LAST_VALUE(salaire) OVER (
    PARTITION BY dept
    ORDER BY salaire DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS salaire_min_dept  -- dernier = le plus bas avec ORDER BY DESC
FROM employes;

Compatibilité selon les SGBD

Les window functions sont standardisées dans SQL:2003 et supportées par PostgreSQL, SQL Server, Oracle et MySQL (depuis la version 8.0). Sur MySQL 5.x, toutes les fonctions de fenêtre sont absentes et doivent être simulées avec des sous-requêtes corrélées ou des variables utilisateur, au prix d’une complexité et d’une performance significativement inférieures.