Fonctions de fenêtrage : ROW_NUMBER, RANK, SUM OVER

Calculez rangs, cumuls et comparaisons intra-groupe sans écraser les lignes grâce à OVER et PARTITION BY.

10 min · 3 questions

GROUP BY écrase les lignes en groupes ; une fonction de fenêtrage calcule un agrégat ou un rang en conservant chaque ligne. C'est l'outil des top N par groupe, des cumuls et des comparaisons à la moyenne de son équipe. Disponible dans SQLite depuis la version 3.25 (2018). Chargez le jeu de données.

SQL

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (name, dept, salary)
VALUES
  ('alice', 'tech', 5200),
  ('bruno', 'tech', 4800),
  ('chloe', 'tech', 4800),
  ('david', 'tech', 4100),
  ('emma', 'sales', 3900),
  ('fanny', 'sales', 3600),
  ('gilles', 'sales', 3600),
  ('hugo', 'sales', 3200);

Jeu de données de la leçon.

Trois fonctions de rang, trois comportements face aux ex æquo : ROW_NUMBER numérote sans doublon (départage arbitraire), RANK donne le même rang aux ex æquo puis saute (1, 2, 2, 4), DENSE_RANK ne saute pas (1, 2, 2, 3). La clause WINDOW évite de répéter la définition de fenêtre (supportée par SQLite et PostgreSQL, pas par SQL Server).

SQL

SELECT name, salary,
  ROW_NUMBER() OVER w AS row_num,
  RANK() OVER w AS rnk,
  DENSE_RANK() OVER w AS dense
FROM employees
WHERE dept = 'tech'
WINDOW w AS (ORDER BY salary DESC);

bruno et chloe (4800) : rangs 2 et 2, puis RANK saute à 4.

PARTITION BY redémarre le calcul pour chaque sous-ensemble : une fenêtre par département, sans rien écraser. Combiné à une CTE, cela donne le pattern d'entretien d'embauche : les N mieux payés de chaque département. La CTE est obligatoire car une fonction de fenêtrage est évaluée après le WHERE : on ne peut pas filtrer sur rn directement.

SQL

WITH ranked AS (
  SELECT name, dept, salary,
    ROW_NUMBER() OVER (
      PARTITION BY dept
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rn <= 2;

Top 2 par département : le pattern à connaître par cœur.

Les agrégats classiques deviennent des fenêtres avec OVER : SUM(salary) OVER (ORDER BY ...) produit un cumul ligne à ligne. Précisez le cadre avec ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW pour un cumul strictement par ligne.

SQL

SELECT name, dept, salary,
  SUM(salary) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_total
FROM employees;

Cumul des salaires, remis à zéro à chaque département.

Deux pièges du métier. 1) Sans clause ROWS, le cadre par défaut est RANGE : les ex æquo de l'ORDER BY reçoivent le même cumul (bruno et chloe afficheraient tous deux 14 800). 2) OVER est interdit dans WHERE, GROUP BY et HAVING : pour filtrer sur un rang ou un cumul, passez par une CTE ou une sous-requête.

Règle de choix rapide : dédoublonner ou paginer → ROW_NUMBER ; podium avec ex æquo → RANK ou DENSE_RANK ; cumuls et moyennes glissantes → agrégat + OVER avec cadre ROWS. Comparez toujours le résultat sur un petit jeu de données avant de lancer sur la volumétrie réelle.

Quiz de validation

Vérifiez que vous avez bien retenu les points clés de cette leçon.

Avec les salaires 5200, 4800, 4800, 4100, quel rang RANK() attribue-t-il à 4100 ?
- 3
- 4
- 2
Pourquoi faut-il une CTE pour filtrer sur ROW_NUMBER() ?
- OVER est évalué après le WHERE, donc interdit dedans
- Une CTE est toujours plus performante
- ROW_NUMBER ne fonctionne qu'à l'intérieur d'un WITH
Que fait PARTITION BY dept dans une clause OVER ?
- Il trie le résultat final par département
- Il redémarre le calcul de la fenêtre pour chaque département
- Il fusionne les lignes en une seule par département