Une window function calcule une valeur pour chaque ligne à partir d'un ensemble de lignes liées, sa fenêtre, sans fusionner les lignes comme le fait GROUP BY. On l'écrit function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY répartit les lignes en groupes et ORDER BY fixe l'ordre dans chaque groupe.
Chaque ligne reste dans le résultat et porte le total de sa région : 1000 pour north, 500 pour south. La part de chaque commercial tient alors en une expression (ana réalise 50 % de north). N'importe quel agrégat (sum, count, avg, min, max) devient une window function dès que vous ajoutez OVER. Un OVER () vide fait de tout le résultat une seule fenêtre.
Numéroter les lignes avec ROW_NUMBER, RANK et DENSE_RANK
Les trois fonctions de classement ne diffèrent que lorsque des lignes sont à égalité sur la valeur de l'ORDER BY :
ben et cy ont tous deux 85. row_number leur donne quand même 2 et 3 ; lequel obtient 2 n'est pas défini, sauf si l'ORDER BY départage l'égalité. rank leur donne 2 à tous les deux et passe à 4 pour dee. dense_rank leur donne 2 à tous les deux et continue avec 3.
| Fonction | Ex æquo | Trous après une égalité |
|---|---|---|
row_number() | numéros différents | non |
rank() | même numéro | oui |
dense_rank() | même numéro | non |
Obtenir les N premières lignes par groupe
Les window functions sont évaluées après le WHERE, donc vous ne pouvez pas filtrer directement dessus. Numérotez les lignes dans une sous-requête ou une CTE, puis filtrez à l'extérieur :
On obtient les deux meilleurs commerciaux de chaque région. Utilisez rank() au lieu de row_number() pour garder toutes les lignes à égalité à la deuxième place. Pour seulement la première ligne de chaque groupe, DISTINCT ON est plus court.
Calculer un total cumulé ou une moyenne mobile
Avec un ORDER BY dans OVER, un agrégat s'exécute sur une frame. Par défaut, la frame va de la première ligne de la partition jusqu'à la ligne courante, ce qui donne un total cumulé. Une clause ROWS BETWEEN définit une autre frame, par exemple la ligne courante et les deux précédentes :
Le total cumulé atteint 130 au jour 5, et la moyenne sur trois jours au jour 5 vaut (30 + 20 + 50) / 3 = 33.3.
La frame par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Elle inclut toutes les lignes à égalité avec la ligne courante sur la valeur de l'ORDER BY, donc deux lignes du même jour obtiennent le même total cumulé. Écrivez ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW pour les ajouter une par une.
Comparer avec la ligne précédente : LAG et LEAD
lag(col) lit une valeur dans la ligne qui précède la ligne courante dans l'ordre de la fenêtre, et lead(col) dans la ligne qui suit. Les deux renvoient NULL au delà du bord, sauf si vous passez une valeur par défaut en troisième argument.
Le jour 1 n'a pas de ligne précédente, donc previous et change valent NULL. La clause WINDOW définit une fenêtre une seule fois, sous un nom, pour que plusieurs fonctions la partagent.
first_value fonctionne avec la frame par défaut, mais pas last_value : la frame par défaut s'arrête à la ligne courante, donc elle renvoie la valeur de la ligne courante. Ajoutez ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING pour que la frame couvre toute la partition. Les autres fonctions propres au fenêtrage sont ntile(n), percent_rank(), cume_dist() et nth_value() ; la référence des window functions de PostgreSQL les liste toutes.
Filtrer les lignes que compte un agrégat
FILTER (WHERE ...) fonctionne aussi sur les agrégats de fenêtre, pour des totaux conditionnels sur chaque ligne :
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
Questions fréquentes
Quelle différence entre GROUP BY et PARTITION BY ?
GROUP BY renvoie une ligne par groupe. PARTITION BY dans OVER garde chaque ligne et lui donne une valeur calculée sur son groupe, ce qui permet d'afficher le montant d'une ligne à côté du total de son groupe.
Peut-on utiliser une window function dans un WHERE ?
Non. Les window functions s'exécutent après WHERE, GROUP BY et HAVING, donc elles ne sont autorisées que dans la liste du SELECT et dans ORDER BY. Calculez la valeur dans une sous-requête ou une CTE et filtrez dans la requête externe.
Pourquoi LAST_VALUE renvoie la valeur de la ligne courante ?
Avec un ORDER BY dans OVER, la frame par défaut s'arrête à la ligne courante (et à ses ex æquo), donc la dernière ligne de la frame est la ligne courante. Ajoutez ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING pour que la frame couvre toute la partition.
Quelle différence entre ROWS et RANGE dans une frame ?
ROWS compte des lignes physiques : ROWS BETWEEN 2 PRECEDING AND CURRENT ROW désigne la ligne courante et les deux précédentes. RANGE travaille sur la valeur de l'ORDER BY, donc les lignes de même valeur (les pairs) sont toujours ensemble dans la frame.