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 :
ROWScompte des lignes — trois lignes, quelles que soient leurs dates.RANGEcompte des valeurs — tout ce qui tombe dans l'intervalle, ex æquo compris.
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,RANGEouGROUPS, 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. AvecORDER BY:RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW— d'où les agrégats cumulatifs.- ROWS
Compte des lignes :
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWprend les deux lignes précédentes et la courante, quelles que soient leurs valeurs.- RANGE
Compte des valeurs :
RANGE BETWEEN 2 PRECEDING AND CURRENT ROWprend 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 ROWpour comparer une ligne à ses voisines sans s'inclure elle-même.
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.