Fonctions de fenêtrage ② — ORDER BY et cadres (ROWS / RANGE)

Fenêtres avec ORDER BY — produire un total cumulé

Ajoute ORDER BY dans OVER et la cible de l'agrégation passe à « du début de la fenêtre jusqu'à la ligne courante » — la valeur grandit ligne par ligne, produisant un total cumulé (la somme depuis le début jusqu'à la ligne courante).

La forme est SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date).

PARTITION BY emp_id divise la fenêtre par employé, ORDER BY sale_date organise les ventes de chaque employé par date, puis le total cumulé est calculé par employé.

Le schéma ci-dessous trace la croissance du total cumulé pour les 5 ventes d'emp_id=29 dans l'ordre des dates.

-- Sans ORDER BY : chaque ligne reçoit le total inchangé de l'employé
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id) AS emp_total
FROM sales
ORDER BY emp_id, sale_date;

-- Avec ORDER BY : total cumulé par employé du début jusqu'à la ligne courante
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS running_total
FROM sales
ORDER BY emp_id, sale_date;

Imagine que tu veux un total cumulé des ventes par employé, trié par date. (Si ta requête s'exécute correctement, une explication apparaîtra.)

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

② Calcule SUM(amount) sur une fenêtre partitionnée par employé et triée par date, et ajoute-le comme 4ᵉ colonne avec l'alias running_total.

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

Le cadre — quelles lignes sont réellement agrégées dans la fenêtre

Lorsque tu ajoutes ORDER BY à une fonction de fenêtrage, la plage de lignes que chaque ligne agrège réellement s'appelle le cadre.

Si tu n'écris pas le cadre explicitement, la valeur par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (du début de la fenêtre jusqu'à la ligne courante).

C'est exactement pour cela qu'ajouter ORDER BY dans l'exercice précédent te donnait un total cumulé — ce cadre par défaut fait le travail.

-- A : cadre omis (défaut = RANGE UNBOUNDED PRECEDING ... CURRENT ROW)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (PARTITION BY emp_id ORDER BY sale_date) AS run_default
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

-- B : le cadre par défaut écrit explicitement (même résultat que A)
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS run_explicit
FROM sales WHERE emp_id = 14
ORDER BY sale_date;

Les cadres se construisent à partir de ces 5 mots-clés, utilisés pour dire « depuis où » et « jusqu'où » par rapport à la ligne courante.

Confirmons le comportement de total cumulé en écrivant le cadre explicitement.

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

② Sur une fenêtre partitionnée par employé et triée par sale_date, calcule SUM(amount) avec le cadre explicite ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, et ajoute-le comme 4ᵉ colonne avec l'alias running_total.

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

ROWS BETWEEN n PRECEDING — sommes mobiles et moyennes mobiles

Configure le cadre sur ROWS BETWEEN n PRECEDING AND CURRENT ROW et l'agrégation se restreint aux « n dernières lignes + la ligne courante ».

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW couvre les 3 lignes les plus récentes (en incluant la courante), donc SUM devient une somme mobile et AVG devient une moyenne mobile.

C'est la forme classique pour lisser une tendance récente.

Près du début de la fenêtre, le cadre a moins de lignes à puiser (la ligne 1 a 1 ligne, la ligne 2 a 2 lignes, à partir de la ligne 3 il y a 3 lignes).

Le schéma ci-dessous parcourt les 5 ventes d'emp_id=14 ligne par ligne, en montrant ce qui se retrouve dans le cadre à chaque « ligne courante ».

-- Moyenne mobile des 3 dernières ventes (2 lignes préc. + courante)
SELECT emp_id, sale_date, amount,
  AVG(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg3
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

-- Somme mobile des 2 dernières ventes (1 ligne préc. + courante) utilise la même forme
SELECT emp_id, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY emp_id ORDER BY sale_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_sum2
FROM sales WHERE emp_id = 2
ORDER BY sale_date;

Imagine que tu veux une somme mobile par employé sur les 3 ventes les plus récentes (les 2 dernières + la courante), triée par date.

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

② Sur une fenêtre partitionnée par employé et triée par sale_date, calcule SUM(amount) avec le cadre ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, et ajoute-le comme 4ᵉ colonne avec l'alias moving_sum3.

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

ROWS vs RANGE — comment les lignes ex æquo sont traitées

Les cadres existent en deux modes de comptage : ROWS et RANGE.

ROWS compte les lignes physiques (« les 2 lignes précédentes » signifie exactement 2 lignes). Même avec des valeurs ex æquo, chaque ligne est comptée séparément, donc le total cumulé grandit ligne par ligne.

RANGE regroupe les lignes ayant la même valeur d'ORDER BY (ex æquo) dans un seul bloc. Les lignes ex æquo partagent un cadre et finissent avec le même total cumulé.

Les deux schémas ci-dessous montrent comment ROWS et RANGE traitent chacun les 3 lignes où amount = 100000.

-- ORDER BY amount a 3 lignes ex æquo à 100000, donc ROWS et RANGE divergent
SELECT sale_id, amount,
  SUM(amount) OVER (ORDER BY amount ROWS  UNBOUNDED PRECEDING) AS rows_run,
  SUM(amount) OVER (ORDER BY amount RANGE UNBOUNDED PRECEDING) AS range_run
FROM sales
WHERE amount = 100000
ORDER BY sale_id;
-- ROWS compte une ligne à la fois ; RANGE regroupe les 3 lignes 100000 ex æquo dans un seul cadre

Imagine que tu veux voir, dans un seul résultat, comment un total cumulé change entre ROWS et RANGE lorsque plusieurs lignes partagent la même valeur. Il y a 3 ventes où amount = 100000.

① Sélectionne sale_id et amount depuis sales.

② Sur une fenêtre triée par amount, ajoute SUM(amount) avec le cadre ROWS UNBOUNDED PRECEDING avec l'alias rows_run, et un autre SUM(amount) avec le cadre RANGE UNBOUNDED PRECEDING avec l'alias range_run.

③ Filtre uniquement les lignes où amount = 100000.

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