Leçon 7.3 · Temps de lecture : ~8 min

Un cadre de fenêtre détermine quelles lignes autour de la ligne courante une fonction de fenêtrage d'agrégation utilise réellement : toutes les lignes depuis le début, les trois dernières lignes ou les sept derniers jours. Dans cette leçon, vous apprendrez la syntaxe des cadres ROWS et RANGE, construirez des totaux cumulés et des moyennes mobiles, verrez pourquoi « les 3 dernières lignes » et « les 3 derniers jours » donnent des réponses différentes sur des données réelles, et éviterez le piège du cadre par défaut.

Cadres de fenêtre SQL : ROWS vs RANGE

PARTITION BY divise les lignes en groupes et ORDER BY les ordonne. Le cadre est la troisième partie de OVER : dans la partition courante, il sélectionne une plage glissante de lignes par rapport à la ligne courante. Les fonctions d'agrégation (SUM, AVG, COUNT, MIN, MAX) et de valeur (FIRST_VALUE, LAST_VALUE) respectent le cadre ; les fonctions de classement et LAG/LEAD l'ignorent.

Les exemples s'exécutent sur la base d'exemple Sakila (MySQL 8.0).

Syntaxe du cadre

function_name(expression) OVER (
    PARTITION BY partition_columns
    ORDER BY sort_column
    { ROWS | RANGE } BETWEEN frame_start AND frame_end
)

• ROWS compte des lignes physiques : 2 PRECEDING désigne les deux lignes au-dessus de la ligne courante, quelles que soient leurs valeurs.
• RANGE travaille avec les valeurs de la colonne de l'ORDER BY : INTERVAL 2 DAY PRECEDING désigne toutes les lignes dont la date est au plus deux jours plus tôt. Un cadre RANGE avec décalage exige exactement une colonne numérique ou de date dans l'ORDER BY.

Les données : le revenu quotidien

Tous les exemples utilisent le revenu quotidien du 26 juillet au 23 août 2005. Notez le trou : après le 2 août, le jour suivant avec des paiements est le 16 août.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT pay_day, revenue
FROM daily
ORDER BY pay_day;

Résultat : 16 jours : du 26 juillet au 2 août et du 16 au 23 août.

Total cumulé et total restant

UNBOUNDED PRECEDING AND CURRENT ROW accumule depuis la première ligne ; CURRENT ROW AND UNBOUNDED FOLLOWING additionne ce qui reste à venir :

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total
FROM daily
ORDER BY pay_day
LIMIT 3;

Résultat : le 26 juillet, le total cumulé vaut 160.67 et le total restant correspond à toute la période, 37971.56 ; le 28 juillet, ils valent 5464.98 et 35084.38.

Le total cumulé est traité plus en profondeur, avec les totaux par groupe et la solution sans fonctions de fenêtrage, dans la leçon 12.7.

Moyenne mobile : 3 lignes, ce n'est pas 3 jours

Une moyenne mobile lisse les variations quotidiennes. Le premier réflexe est « la ligne courante et les deux précédentes », un cadre ROWS. Un cadre RANGE avec INTERVAL prend plutôt « le jour courant et les deux jours calendaires précédents » :

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3_rows,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW), 2) AS avg_3_days
FROM daily
ORDER BY pay_day;

Résultat : 16 lignes ; les deux moyennes sont égales partout sauf juste après le trou : le 16 août, la moyenne sur 3 lignes vaut 1885.21 et celle sur 3 jours 111.77.

Le 16 août, « les 3 dernières lignes » sont le 1er août, le 2 août et le 16 août : deux d'entre elles datent de deux semaines. « Les 3 derniers jours » ne contiennent que le 16 août lui-même, car il n'y a eu aucun paiement les 14 et 15 août. Quand les dates ont des trous, utilisez RANGE avec INTERVAL pour des fenêtres calendaires, ou joignez d'abord les données à un calendrier complet (leçon 6.6) pour que chaque jour ait une ligne.

MariaDB n'accepte pas INTERVAL dans un cadre. Ordonnez alors par le numéro du jour et utilisez un décalage numérique, qui donne le même résultat : AVG(revenue) OVER (ORDER BY TO_DAYS(pay_day) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW).

Au début des données, les deux cadres contiennent simplement moins de lignes : le premier jour est moyenné sur une valeur, le deuxième sur deux.

Le cadre par défaut

Si OVER contient un ORDER BY mais aucun cadre, la base de données utilise RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. C'est un calcul cumulatif, et comme il s'agit de RANGE, les lignes ayant la même valeur de tri sont ajoutées ensemble, en bloc. Sans ORDER BY, le cadre par défaut est toute la partition.

Deux conséquences pratiques :

• SUM(x) OVER (ORDER BY day) est un total cumulé, pas un total général. Supprimez ORDER BY pour obtenir le total.
• Quand la colonne de tri contient des doublons, le total cumulé fait un saut de tout le groupe de lignes égales. La leçon 12.7 le montre sur deux paiements effectués à la même seconde. Écrivez ROWS BETWEEN ... explicitement chaque fois que le cadre compte.

Nommer une fenêtre avec WINDOW

Quand plusieurs fonctions utilisent la même fenêtre, définissez-la une seule fois avec la clause WINDOW et faites-y référence par son nom :

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    MIN(revenue) OVER last_3_days AS min_3_days,
    MAX(revenue) OVER last_3_days AS max_3_days,
    COUNT(*) OVER last_3_days AS days_in_window
FROM daily
WINDOW last_3_days AS (
    ORDER BY pay_day
    RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
)
ORDER BY pay_day;

Résultat : 16 lignes ; le 16 août, days_in_window vaut 1, le 17 août 2, et à partir du 18 août de nouveau 3.

La clause WINDOW se place après HAVING et avant ORDER BY. MySQL 8.0, MariaDB 10.2+, PostgreSQL et SQLite la prennent en charge ; SQL Server à partir de la version 2022.

Ce que MySQL ne prend pas en charge

Le standard SQL prévoit aussi des cadres GROUPS, qui comptent des groupes de valeurs égales, et une option EXCLUDE. PostgreSQL et SQLite les prennent en charge ; MySQL et MariaDB non.

Points clés :

• Un cadre sélectionne les lignes autour de la ligne courante qu'utilise une fonction de fenêtrage d'agrégation : ROWS ou RANGE BETWEEN start AND end.
• UNBOUNDED PRECEDING AND CURRENT ROW donne un total cumulé ; n PRECEDING AND CURRENT ROW donne une fenêtre glissante.
• ROWS compte des lignes, RANGE compare des valeurs : avec des trous dans les dates, « 3 lignes » et « 3 jours » donnent des moyennes différentes.
• Avec ORDER BY et sans cadre, le défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ; écrivez le cadre explicitement quand il compte.
• La clause WINDOW nomme une fenêtre et permet à plusieurs fonctions de la réutiliser.

Questions fréquentes

Quelle est la différence entre ROWS et RANGE en SQL ?

ROWS compte des lignes physiques : 2 PRECEDING désigne les deux lignes au-dessus de la ligne courante. RANGE compare les valeurs de la colonne de l'ORDER BY : INTERVAL 2 DAY PRECEDING désigne toutes les lignes dont la date est dans les deux jours précédant la ligne courante, quel que soit leur nombre.

Comment calculer une moyenne mobile en SQL ?

Utilisez AVG avec un cadre : AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) fait la moyenne de la ligne courante et des six précédentes. Si certains jours n'ont pas de lignes, utilisez RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW ou comblez d'abord les trous avec un calendrier.

Quel est le cadre de fenêtre par défaut ?

Avec ORDER BY dans OVER et sans clause de cadre, c'est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, un calcul cumulatif dans lequel les lignes de même valeur de tri sont incluses ensemble. Sans ORDER BY, le cadre est toute la partition.

Les cadres de fenêtre fonctionnent-ils avec ROW_NUMBER et RANK ?

Non. Les fonctions de classement (ROW_NUMBER, RANK, DENSE_RANK, NTILE) et LAG/LEAD travaillent toujours sur toute la partition et ignorent le cadre. Les cadres concernent les fonctions d'agrégation ainsi que FIRST_VALUE, LAST_VALUE et NTH_VALUE.