Introduction aux fonctions de fenêtre

Les fonctions de fenêtre permettent d'effectuer des calculs sur un ensemble de lignes liées à la ligne courante, sans regrouper ces lignes en une seule sortie comme le fait GROUP BY. Cela signifie que vous conservez le détail au niveau de la ligne tout en obtenant des agrégats, des classements et des comparaisons côte à côte. Les cas d'usage courants incluent le classement des produits les plus vendus par région, le calcul des totaux cumulés pour les rapports financiers et le calcul des moyennes mobiles dans le temps. La syntaxe clé combine une fonction d'agrégation avec une clause OVER qui définit la manière dont la « fenêtre » de lignes est découpée.

Syntaxe de base et fonctions principales

Un appel de fonction de fenêtre ressemble à une agrégation ordinaire, mais avec une clause OVER ajoutée. À l'intérieur de OVER, vous définissez PARTITION BY (comment regrouper les lignes en fenêtres), ORDER BY (comment trier dans chaque fenêtre), et éventuellement une clause de cadre comme ROWS BETWEEN. Les fonctions de fenêtre les plus utilisées sont ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG et COUNT — chacune fonctionne soit comme agrégat, soit comme fonction de navigation selon le contexte.

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

ROW_NUMBER attribue un numéro séquentiel unique dans chaque partition, en redémarrant à 1 lorsque la partition change. RANK et DENSE_RANK se comportent de manière similaire, mais attribuent le même numéro aux ex æquo et diffèrent dans la gestion des sauts après des valeurs à égalité.

Classement et requêtes Top-N

Le classement est l'une des applications les plus pratiques. Supposons que vous souhaitiez les 3 employés les mieux rémunérés par département. Vous calculez une colonne de rang et vous filtrez dessus, soit avec une sous-requête, soit avec un CTE pour plus de clarté. Ce motif est bien plus efficace que les sous-requêtes corrélées et se lit naturellement dans le code applicatif.

WITH ranked AS (
    SELECT
        employee_id,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT *
FROM ranked
WHERE rk <= 3
ORDER BY department, rk;

Remarquez que PARTITION BY department redémarre le classement pour chaque département, de sorte que vous obtenez toujours exactement 3 gagnants par groupe, même si certains salaires sont à égalité.

Totaux cumulés et cadres mobiles

Les totaux cumulés et les moyennes glissantes nécessitent une clause de cadre à l'intérieur de OVER. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW donne une somme cumulative qui croît du début de la partition jusqu'à la ligne courante. Passer à une fenêtre fixe comme ROWS BETWEEN 6 PRECEDING AND CURRENT ROW produit une moyenne mobile sur 7 lignes, ce qui est courant dans l'analyse de séries temporelles.

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales
ORDER BY order_date;

La première colonne croît de manière monotone, tandis que la moyenne mobile lisse les pics quotidiens pour révéler la tendance sous-jacente. Les définitions de cadre peuvent utiliser ROWS (nombre physique) ou RANGE (logique, basé sur les valeurs), mais ROWS est plus rapide et plus prévisible pour la plupart des besoins de reporting.

Pièges courants et conseils de performance

Une erreur fréquente est d'oublier que les fonctions de fenêtre s'exécutent après WHERE, HAVING et GROUP BY — elles ne peuvent donc pas être filtrées directement dans la même requête sans CTE ni sous-requête. Un autre piège consiste à mélanger les fonctions de fenêtre avec SELECT DISTINCT ou à agréger l'ensemble de résultats d'une manière qui perturbe le planificateur. Côté performance, indexez toujours les colonnes utilisées dans PARTITION BY et ORDER BY, et privilégiez ROWS à RANGE lorsque vous n'avez pas besoin d'un cadrage basé sur les valeurs. Évitez de placer des fonctions de fenêtre dans les clauses WHERE en encapsulant d'abord la requête dans un CTE.

Résumé

Les fonctions de fenêtre dans PostgreSQL sont l'outil adapté dès lors que vous avez besoin du détail au niveau de la ligne alongside des calculs groupés. La clause OVER avec PARTITION BY et ORDER BY vous donne un contrôle total sur la définition de la fenêtre, et les clauses de cadre étendent ce contrôle aux calculs glissants. Maîtriser le classement, les totaux cumulés et les moyennes mobiles couvre environ 80 % des requêtes analytiques réelles que vous écrirez en tant que développeur backend.