SQL : les fonctions de fenêtre (OVER, PARTITION BY)

Un GROUP BY réduit les lignes : une ligne par groupe. Mais comment afficher chaque produit avec son rang dans sa catégorie, ou chaque mois avec le chiffre d'affaires cumulé depuis janvier ? Les fonctions de fenêtre font des calculs sur un ensemble de lignes sans les fusionner : chaque ligne garde son identité.

Syntaxe

FONCTION() OVER (
  PARTITION BY colonne_de_groupe
  ORDER BY colonne_de_tri
)

Comment ça marche

La clause OVER (…) définit la « fenêtre », c'est-à-dire les lignes que la fonction regarde :

• PARTITION BY découpe les lignes en groupes, comme un GROUP BY, mais sans les fusionner.
• ORDER BY fixe l'ordre des lignes à l'intérieur de chaque groupe.

Les fonctions de classement

Les agrégations en fenêtre

SUM(…) OVER (ORDER BY …) donne une somme cumulée. AVG(…) OVER (PARTITION BY …) affiche la moyenne du groupe à côté de chaque ligne. LAG() et LEAD() lisent la valeur de la ligne précédente ou suivante.

Les fonctions de fenêtre sont calculées après le WHERE et le GROUP BY. Pour filtrer sur leur résultat (par exemple « le top 3 »), il faut passer par une sous-requête ou une CTE.

Schéma

Schéma simplifié pour illustrer le principe. Les exemples ci-dessous utilisent la vraie base d'exercices de SQL Quest (une boutique en ligne fictive : voir le schéma complet).

Exemples avec leurs résultats

SELECT categorie, nom, prix,
       RANK() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang
FROM produits
LIMIT 10;

WITH ventes AS (
  SELECT p.categorie, p.nom, SUM(l.quantite) AS vendus,
         ROW_NUMBER() OVER (PARTITION BY p.categorie ORDER BY SUM(l.quantite) DESC) AS rn
  FROM produits p JOIN lignes_commande l ON l.produit_id = p.id
  GROUP BY p.id
)
SELECT categorie, nom, vendus
FROM ventes
WHERE rn <= 2;

WITH mois AS (
  SELECT strftime('%Y-%m', o.date_commande) AS mois, SUM(l.quantite * l.prix_unitaire) AS ca
  FROM commandes o JOIN lignes_commande l ON l.commande_id = o.id
  WHERE o.date_commande LIKE '2025%'
  GROUP BY mois
)
SELECT mois, ROUND(ca, 2) AS ca, ROUND(SUM(ca) OVER (ORDER BY mois), 2) AS cumul
FROM mois
LIMIT 6;

SELECT departement, nom, salaire,
       ROUND(AVG(salaire) OVER (PARTITION BY departement)) AS moyenne_dept
FROM employes
LIMIT 8;

Les erreurs fréquentes

SELECT nom, RANK() OVER (ORDER BY prix DESC) AS r FROM produits WHERE RANK() OVER (ORDER BY prix DESC) <= 3;

SELECT * FROM (SELECT nom, RANK() OVER (ORDER BY prix DESC) AS r FROM produits) WHERE r <= 3;

SELECT categorie, nom, RANK() OVER (ORDER BY prix DESC) FROM produits;

SELECT categorie, nom, RANK() OVER (PARTITION BY categorie ORDER BY prix DESC) FROM produits;

SELECT * FROM (SELECT nom, RANK() OVER (ORDER BY stock) AS r FROM produits) WHERE r <= 1;

SELECT * FROM (SELECT nom, ROW_NUMBER() OVER (ORDER BY stock, id) AS r FROM produits) WHERE r <= 1;

Exercices

Essayez d'écrire la requête vous-même avant d'ouvrir la solution.

SELECT client_id, id, date_commande, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_commande) AS num FROM commandes LIMIT 10;

SELECT departement, prenom, nom, salaire FROM (SELECT *, RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) AS r FROM employes) WHERE r = 1;

Questions fréquentes