Fonctions de fenêtrage ③ — Classement, fonctions analytiques et ordre d'exécution

Classement — ROW_NUMBER / RANK / DENSE_RANK

Les fonctions de classement dédiées sont ROW_NUMBER() / RANK() / DENSE_RANK().

Les trois numérotent les lignes dans l'ordre donné par OVER (ORDER BY ...), mais elles gèrent les ex æquo (valeurs égales) différemment.

Le SQL ci-dessous — et le schéma qui visualise son résultat — parcourt un cas concret avec des totaux ex æquo ligne par ligne.

-- Calcule le total des ventes de chaque employé, puis classe par ordre décroissant de total de 3 façons
SELECT emp_id,
  SUM(amount) AS total,
  ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS rn,
  RANK()       OVER (ORDER BY SUM(amount) DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dr
FROM sales
GROUP BY emp_id
ORDER BY total DESC;
-- Tu peux d'abord agréger avec GROUP BY puis classer ces agrégats

Imagine que tu veux un classement des employés par total de ventes. Il y a des ex æquo, et tu aimerais voir les trois côte à côte : numérotation consécutive, saut des rangs suivants, et sans saut. (Si ta requête s'exécute correctement, une explication apparaîtra.)

① GROUP BY la table sales par emp_id et sélectionne SUM(amount) avec l'alias total.

② Sur une fenêtre triée par total décroissant, ajoute ROW_NUMBER() avec l'alias rn, RANK() avec l'alias rk, et DENSE_RANK() avec l'alias dr.

③ Le correcteur vérifie l'ordre des lignes, donc termine par ORDER BY total DESC, emp_id (décroissant par total, ascendant par emp_id en cas d'ex æquo).

LAG / LEAD — tirer les valeurs de la ligne précédente / suivante

LAG(column) tire la valeur de la ligne précédente sur la ligne courante, et LEAD(column) tire de la ligne suivante (lag = être en retard, lead = aller en avant).

OVER (PARTITION BY ... ORDER BY ...) décide ce que « précédent » et « suivant » signifient en fixant l'ordre.

Utilise-les pour les écarts période sur période (vs la vente précédente), les deltas mois sur mois, ou les comparaisons avec l'événement programmé suivant.

LAG(amount) renvoie NULL sur la première ligne de chaque partition (il n'y a pas de ligne précédente) ; de même, LEAD est NULL sur la dernière ligne.

Le deuxième argument optionnel contrôle l'offset (« combien de lignes en arrière ») et le troisième définit une valeur par défaut quand la ligne cible manque — par exemple, LAG(amount, 1, 0).

Soustrais avec amount - LAG(amount) OVER (...) pour obtenir directement l'écart par rapport à la ligne précédente.

-- Montrer la vente précédente (LAG) et l'écart vs la vente précédente
SELECT emp_id, sale_date, amount,
  LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS diff_prev
FROM sales
ORDER BY emp_id, sale_date;

-- Utilise LEAD si tu veux montrer la vente suivante à côté de la ligne courante
SELECT emp_id, sale_date, amount,
  LEAD(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS next_amount
FROM sales
ORDER BY emp_id, sale_date;

Imagine que tu veux une liste de ventes par employé triée par date, avec la vente précédente et l'écart vs cette vente précédente à côté de chaque ligne.

① Sélectionne emp_id, sale_date et amount depuis sales.

② Sur une fenêtre partitionnée par employé et triée par sale_date, ajoute l'amount de la ligne précédente avec l'alias prev_amount.

③ Ajoute aussi amount moins l'amount précédent avec l'alias diff_prev.

④ Le correcteur vérifie l'ordre des lignes, donc termine par ORDER BY emp_id, sale_date.

NTILE — diviser les lignes triées en n groupes de taille égale

NTILE(n) divise les lignes triées par OVER (ORDER BY ...) en n groupes de tailles aussi égales que possible, étiquetant chaque ligne d'un numéro de groupe de 1 à n.

Utilise-le pour les quartiles, quintiles, « top 25 % » et autres regroupements.

Lorsque le nombre de lignes n'est pas divisible par n, les premiers groupes reçoivent chacun une ligne supplémentaire (par exemple, diviser 5 lignes en 4 groupes donne 2 / 1 / 1 / 1).

Combine avec PARTITION BY pour faire des choses comme « diviser les ventes de chaque employé en groupes haut / bas au sein de cet employé ».

Le schéma ci-dessous montre ce qui se passe quand les 5 ventes d'emp_id=11 sont triées par amount décroissant et divisées avec NTILE(4).

-- Par employé, divise les ventes de cet employé en 4 groupes par amount desc
SELECT emp_id, sale_date, amount,
  NTILE(4) OVER (PARTITION BY emp_id ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY emp_id, amount DESC;

-- Divise toutes les ventes en 4 paniers par amount (le top 25 % a quartile = 1)
SELECT sale_id, emp_id, amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile
FROM sales
ORDER BY amount DESC;

Imagine que tu veux une liste de ventes divisée en 4 groupes par employé (par amount desc), affichée par ordre de quartile du groupe du haut vers le bas.

① Sélectionne emp_id, sale_date et amount depuis sales.

② Sur une fenêtre partitionnée par employé et triée par amount desc, calcule NTILE(4) et ajoute-le comme 4ᵉ colonne avec l'alias quartile.

③ Le correcteur vérifie l'ordre des lignes, donc termine par `ORDER BY quartile, emp_id, sale_id` (ORDER BY peut faire référence à l'alias quartile calculé dans SELECT).

Imagine que tu veux filtrer pour ne garder que le top 25 % (quartile = 1). Essaie d'ajouter WHERE quartile = 1 à la requête de l'Exercice 3.

① Utilise le même SELECT que l'Exercice 3 et ajoute WHERE quartile = 1 après FROM sales.

② L'exécution renvoie une erreur (une erreur signifie que tu as bon).

③ La section suivante, « Ordre d'exécution SQL », explique pourquoi. quartile fonctionnait dans ORDER BY mais ne fonctionne pas dans WHERE — garde cette énigme en tête pendant que nous avançons.

Ordre d'exécution SQL — pourquoi tu ne peux pas mettre une fonction de fenêtrage dans WHERE

Tu pourrais te demander : « ne puis-je pas simplement filtrer sur le rang que j'ai calculé, comme WHERE rk = 1 ? »

Tu obtiendrais une erreur. La raison est l'ordre d'exécution logique de SQL.

L'ordre dans lequel tu écris les clauses (SELECT → FROM → WHERE ...) n'est pas l'ordre dans lequel elles sont évaluées.

L'évaluation se déroule grosso modo ainsi : FROM / JOIN (assembler les tables) → WHERE (filtrer les lignes) → GROUP BY (grouper) → HAVING (filtrer les groupes) → SELECT (calculer les colonnes — c'est ici que les fonctions de fenêtrage s'exécutent) → ORDER BY (trier) → LIMIT (tronquer).

Puisque WHERE s'exécute avant SELECT, il ne peut pas voir le rang que SELECT est sur le point de calculer.

Pour filtrer sur un rang, pousse la requête qui calcule la fonction de fenêtrage dans une sous-requête (ou CTE) et applique WHERE à l'extérieur.

Le SELECT interne termine le calcul de la fonction de fenêtrage, et la requête externe peut alors traiter ce résultat comme une simple colonne.

-- Ceci échoue : WHERE s'exécute avant SELECT, donc rk n'existe pas encore
-- SELECT emp_id, RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
-- FROM sales GROUP BY emp_id WHERE rk <= 3;

-- Correct : calcule le rang dans une sous-requête interne, filtre à l'extérieur
SELECT * FROM (
  SELECT emp_id,
    SUM(amount) AS total,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
  FROM sales
  GROUP BY emp_id
) AS ranked
WHERE rk <= 3
ORDER BY rk, emp_id;

Imagine que tu veux juste les 3 meilleurs employés par total de ventes. Puisque tu filtres sur un rang, tu as besoin d'une sous-requête pour contourner l'ordre d'exécution.

① Dans la sous-requête interne, GROUP BY sales par emp_id et sélectionne SUM(amount) avec l'alias total, plus RANK() OVER (ORDER BY SUM(amount) DESC) avec l'alias rk. Donne à la sous-requête l'alias ranked.

② Filtre la requête externe avec WHERE rk <= 3.

③ Le correcteur vérifie l'ordre des lignes, donc termine par ORDER BY rk, emp_id (ascendant par rk, puis par emp_id en cas d'ex æquo).