Fonctions de fenêtrage : des agrégats sans effondrer les lignes

Ce que c'est

Le tutoriel PostgreSQL définit une fonction de fenêtrage comme une fonction qui effectue un calcul sur un ensemble de lignes de table liées d'une manière ou d'une autre à la ligne courante, comparable à un agrégat mais sans effondrer les lignes en une seule ligne de sortie. La clause OVER définit la fenêtre : PARTITION BY répartit les lignes en groupes, ORDER BY ordonne les lignes au sein d'une partition, et un cadre optionnel (ROWS, RANGE ou GROUPS BETWEEN ... AND ...) limite les lignes de la partition que la fonction voit. La page de référence liste row_number, rank, dense_rank, percent_rank, ntile, lag, lead, first_value, last_value et nth_value ; n'importe quel agrégat tel que sum ou avg peut aussi être utilisé avec OVER.

Pourquoi c'est important

Sans fonctions de fenêtrage, « le salaire de chaque employé à côté de la moyenne du service », « les trois dernières commandes par client » ou « un solde cumulé » nécessitent des auto-jointures ou des sous-requêtes corrélées plus difficiles à lire. Une fonction de fenêtrage exprime chacun de ces cas directement dans la liste SELECT.

Comment l'appliquer

• Total cumulé : sum(amount) OVER (PARTITION BY account_id ORDER BY created_at, id). Inclure un critère de départage dans ORDER BY : le tutoriel indique qu'avec ORDER BY, le cadre par défaut va du début de la partition jusqu'à la ligne courante, plus toute ligne suivante qui lui est égale, si bien que des clés de tri égales sont sommées ensemble.
• Top-N par groupe : row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn dans une sous-requête ou une CTE, puis WHERE rn <= 3 à l'extérieur ; les fonctions de fenêtrage ne peuvent pas apparaître directement dans WHERE.
• Comparaison avec la ligne précédente : lag(value) OVER (ORDER BY ts) pour des écarts, lead pour la valeur suivante.
• Classement : rank laisse des trous après des égalités, dense_rank non, row_number est arbitraire en cas d'égalité sauf si l'ORDER BY est total.
• Partager une fenêtre avec WINDOW w AS (PARTITION BY ... ORDER BY ...) et écrire OVER w pour plusieurs fonctions.

Pièges

Les fonctions de fenêtrage sont évaluées après WHERE, GROUP BY et HAVING, si bien que filtrer sur leur résultat nécessite une requête englobante. last_value avec le cadre par défaut renvoie le dernier pair de la ligne courante (la ligne elle-même quand l'ORDER BY n'a pas d'égalités), pas la dernière ligne de la partition ; la page de référence indique que cela risque de donner des résultats peu utiles, d'où l'intérêt de définir ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Les grandes partitions se trient en mémoire ou sur disque ; rechercher les nœuds WindowAgg et Sort dans EXPLAIN.