Formations — 1 037 leçons en français

Accueil › SQL & bases de données › Les cadres : ROWS, RANGE et moyennes glissantes

Les cadres : ROWS, RANGE et moyennes glissantes

Fonctions de fenêtrageavance

Le cadre définit quelles lignes de la partition entrent dans le calcul pour la ligne courante. Il explique pourquoi un même sum() peut donner un total ou un cumul.

-- Sans ORDER BY : toute la partition
sum(total) OVER (PARTITION BY client_id)

-- Avec ORDER BY : cumul (cadre par défaut = début → ligne courante)
sum(total) OVER (PARTITION BY client_id ORDER BY creee_le)

-- Cadre explicite : moyenne glissante sur 3 lignes
avg(total) OVER (ORDER BY creee_le ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- Cadre temporel : 7 jours calendaires
avg(total) OVER (ORDER BY jour RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

La distinction essentielle :

Pour une moyenne mobile « sur 7 jours », ROWS BETWEEN 6 PRECEDING est faux dès qu'un jour manque : on remonte alors à 10 ou 15 jours en arrière. C'est RANGE avec un intervalle qu'il faut.

Enfin, EXCLUDE CURRENT ROW permet de comparer une ligne aux autres, sans s'inclure soi-même.

Les termes à connaître

Cadre (*frame*)

Le sous-ensemble de la partition réellement pris en compte pour la ligne courante. Défini par ROWS, RANGE ou GROUPS, il est ce qui distingue une somme globale d'un cumul ou d'une moyenne glissante.

Cadre par défaut

Sans ORDER BY : toute la partition. Avec ORDER BY : RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — d'où les agrégats cumulatifs.

ROWS

Compte des lignes : ROWS BETWEEN 2 PRECEDING AND CURRENT ROW prend les deux lignes précédentes et la courante, quelles que soient leurs valeurs.

RANGE

Compte des valeurs : RANGE BETWEEN 2 PRECEDING AND CURRENT ROW prend toutes les lignes dont la valeur de tri est dans l'intervalle. Les ex æquo sont donc inclus ensemble.

RANGE avec intervalle

PostgreSQL 11+ accepte RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW : une fenêtre temporelle réelle, qui ignore les jours sans données.

EXCLUDE

Retire des lignes du cadre : EXCLUDE CURRENT ROW pour comparer une ligne à ses voisines sans s'inclure elle-même.

Lire l'explication détaillée et faire le quiz →

Cette page présente la partie essentielle de la leçon. Les exemples commentés, les pièges courants et les trois questions de quiz sont dans l'application.