Fonctions fenêtre PostgreSQL : rangs et cumuls

Les fonctions fenêtre de PostgreSQL ajoutent un rang, un cumul ou la valeur de la ligne précédente tout en conservant les lignes du résultat. La clause OVER définit la fenêtre. Avant de l’écrire, déterminez quelles lignes vont ensemble, dans quel ordre les traiter et lesquelles inclure dans chaque calcul.

Ce guide utilise six ventes pour rendre ces choix visibles. Nous avons exécuté 28 vérifications sur un serveur PostgreSQL 18.6 isolé, dont tous les résultats présentés ci-dessous. Les exemples vérifient le comportement des requêtes, pas leur vitesse en production.

Créer une petite table de ventes

Utilisez une base de test où window_lab n’existe pas. L’équipe A compte deux ventes le même jour, pour le même montant. Aucune vente n’a lieu le 2 janvier. Ces détails permettent d’observer les ex æquo et la différence entre compter des lignes et compter des jours.

CREATE SCHEMA window_lab;
CREATE TABLE window_lab.sales (
    id integer PRIMARY KEY,
    team text NOT NULL,
    sold_on date NOT NULL,
    amount integer NOT NULL
);
INSERT INTO window_lab.sales VALUES
(1, 'A', '2026-01-01', 100),
(2, 'A', '2026-01-01', 100),
(3, 'A', '2026-01-03', 50),
(4, 'A', '2026-01-04', 20),
(5, 'B', '2026-01-01', 80),
(6, 'B', '2026-01-03', 30);

Le total des six montants est 380 : 270 pour A et 110 pour B. Les montants sont des unités entières, sans problème d’arrondi monétaire.

Conserver chaque vente avec les totaux de sa partition

PARTITION BY team sépare le calcul par équipe. Les parenthèses vides de OVER () réunissent toutes les lignes d’entrée. Sans ordre défini dans ces fenêtres, chaque somme couvre sa partition entière.

SELECT id, team, amount,
       sum(amount) OVER (PARTITION BY team) AS team_total,
       sum(amount) OVER () AS all_total
FROM window_lab.sales
ORDER BY team, id;

Le résultat suit l’ordre des colonnes sélectionnées : identifiant, équipe, montant, total de l’équipe et total général.

1|A|100|270|380
2|A|100|270|380
3|A|50|270|380
4|A|20|270|380
5|B|80|110|380
6|B|30|110|380

Le résultat contient toujours six lignes. Notre contrôle distinct avec GROUP BY team n’en renvoyait que deux : A avec 270 et B avec 110. Utilisez l’agrégation groupée pour obtenir une ligne par équipe, et la fenêtre lorsque chaque vente doit conserver ce contexte. Le tutoriel PostgreSQL sur les fonctions fenêtre présente cette distinction.

Choisir la règle de classement et traiter les ex æquo

Ces trois colonnes répondent à des questions différentes. La première attribue une position unique à chaque vente. Les deux autres classent les montants en conservant les égalités.

SELECT id, team, amount,
       row_number() OVER (PARTITION BY team ORDER BY amount DESC, id) AS position,
       rank() OVER (PARTITION BY team ORDER BY amount DESC) AS tied_rank,
       dense_rank() OVER (PARTITION BY team ORDER BY amount DESC) AS dense_position
FROM window_lab.sales
ORDER BY team, amount DESC, id;

Après le montant viennent position, tied_rank et dense_position :

1|A|100|1|1|1
2|A|100|2|1|1
3|A|50|3|3|2
4|A|20|4|4|3
5|B|80|1|1|1
6|B|30|2|2|2

Pour A, les numéros de ligne sont 1, 2, 3, 4. Les deux montants de 100 partagent le rang 1 ; le rang suivant est donc 3. Les rangs denses sont 1, 1, 2, 3, sans saut après l’égalité.

Seule la fenêtre de row_number() utilise ici l’identifiant pour départager les ventes. L’ajouter aux deux autres ordres rendrait les ventes distinctes pour le classement, alors que nous voulons conserver leur égalité de montant. Nous avons réinséré les données dans l’ordre inverse et confirmé que l’ordre explicite de row_number donnait les mêmes positions.

Le ORDER BY final fixe l’affichage. Il ne corrige pas un ordre incomplet dans OVER. La référence des fonctions fenêtre définit les rangs, les lignes équivalentes dans l’ordre choisi et les fonctions d’accès aux lignes voisines.

Définir explicitement le cadre d’un cumul

La requête suivante compare trois calculs : le total jusqu’à la date courante, le total jusqu’à la vente courante et la somme d’au plus deux ventes consécutives.

SELECT id, team, sold_on, amount,
       sum(amount) OVER (PARTITION BY team ORDER BY sold_on) AS through_day,
       sum(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS through_row,
       sum(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS two_rows
FROM window_lab.sales
ORDER BY team, sold_on, id;

Les trois dernières colonnes sont through_day, through_row et two_rows :

1|A|2026-01-01|100|200|100|100
2|A|2026-01-01|100|200|200|200
3|A|2026-01-03|50|250|250|150
4|A|2026-01-04|20|270|270|70
5|B|2026-01-01|80|80|80|80
6|B|2026-01-03|30|110|110|110

Les deux premières lignes de A affichent 200 dans through_day. Avec un tri limité à la date, elles sont équivalentes dans cet ordre et le cadre par défaut les inclut toutes les deux. Dans through_row, l’identifiant les départage et le cadre ROWS avance vente par vente : 100, 200, 250, 270.

Deux choix changent entre ces colonnes : l’ordre et le cadre. Pour isoler leurs effets, nous avons aussi ajouté l’identifiant à l’ordre de la première fenêtre. Sans égalité restante, son cumul par défaut correspondait au cumul ROWS. Préciser ROWS sans définir un ordre complet laisserait encore indéterminée la séquence des lignes ex æquo.

Pour la vente de A du 3 janvier, two_rows vaut 150 : le montant courant de 50 plus la vente précédente de 100, datée du 1er janvier. Il s’agit de deux lignes, pas de deux jours. Une date absente ne crée pas une ligne de valeur zéro. Définissez séparément le calcul calendaire si le rapport l’exige.

La référence des cadres de fenêtre distingue ROWS, RANGE et GROUPS. Nos exemples testent des cadres de lignes et le cadre cumulatif par défaut incluant les ex æquo, sans couvrir toutes les options.

Comparer une vente à la précédente avec LAG

Une fenêtre nommée conserve le même ordre dans les deux expressions :

SELECT id, team, sold_on, amount,
       lag(amount) OVER w AS previous_amount,
       amount - lag(amount) OVER w AS change
FROM window_lab.sales
WINDOW w AS (PARTITION BY team ORDER BY sold_on, id)
ORDER BY team, sold_on, id;

Les deux dernières colonnes contiennent le montant précédent et la différence. Les champs vides de cette sortie psql représentent SQL NULL.

1|A|2026-01-01|100||
2|A|2026-01-01|100|100|0
3|A|2026-01-03|50|100|-50
4|A|2026-01-04|20|50|-30
5|B|2026-01-01|80||
6|B|2026-01-03|30|80|-50

La première vente de chaque équipe n’a pas de prédécesseur. La vente de A du 3 janvier est comparée à la vente enregistrée juste avant, le 1er janvier, ce qui donne −50. Ce n’est pas une comparaison avec le jour calendaire précédent.

Nous avons également testé une séquence distincte contenant un montant NULL. Le troisième argument explicite de lag fournissait une valeur par défaut uniquement en l’absence de ligne précédente ; il ne remplaçait pas un NULL présent dans cette ligne. Réduire le cadre à la ligne courante n’a pas modifié nos résultats de LAG. La fonction utilise un décalage dans la partition, sans se limiter aux lignes de ce cadre.

Vérifier ce que LAST_VALUE considère comme dernier

Les deux colonnes utilisent ici le même ordre unique : seul le cadre change.

SELECT id, team, amount,
       last_value(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
       ) AS frame_last,
       last_value(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS team_last
FROM window_lab.sales
ORDER BY team, sold_on, id;

Les dernières colonnes sont frame_last et team_last :

1|A|100|100|20
2|A|100|100|20
3|A|50|50|20
4|A|20|20|20
5|B|80|80|30
6|B|30|30|30

Avec le cadre par défaut, la dernière ligne est la ligne courante dans cet ordre. frame_last correspond donc au montant courant. Étendre le cadre jusqu’à UNBOUNDED FOLLOWING donne le montant de la dernière vente de l’équipe : 20 sur chaque ligne de A et 30 sur chaque ligne de B. « Dernier » signifie dernier dans l’ordre choisi, pas montant le plus élevé.

Filtrer les rangs dans une requête extérieure

Pour conserver deux ventes par équipe, calculez le numéro de ligne dans une sous-requête, puis filtrez son résultat à l’extérieur :

SELECT id, team, amount, position
FROM (
    SELECT id, team, amount,
           row_number() OVER (
               PARTITION BY team ORDER BY amount DESC, id
           ) AS position
    FROM window_lab.sales
) AS ranked
WHERE position <= 2
ORDER BY team, position;

1|A|100|1
2|A|100|2
5|B|80|1
6|B|30|2

Cette requête sélectionne au plus deux lignes par équipe, l’identifiant départageant les montants égaux. Elle ne garantit pas d’inclure tous les ex æquo à la limite. Si le besoin porte sur les deux montants distincts les plus élevés, dense_rank() ordonné uniquement par montant exprime une autre règle. Notre variante a renvoyé trois lignes de A et deux de B.

Un WHERE row_number() OVER (...) <= 2 au même niveau a échoué avec SQLSTATE 42P20. Un filtre ordinaire placé avant la fenêtre modifie aussi son entrée : ne garder que les montants d’au moins 50 a réduit les totaux des équipes à 250 et 80. Décidez si le rapport exige les totaux avant filtrage ou ceux des seules ventes conservées. La référence SELECT décrit ces étapes.

Valider les résultats avant d’étudier les performances

Nos contrôles ont comparé les résultats exacts, répété les tests d’ordre après une insertion inversée et recalculé indépendamment les cumuls en Python. Une entrée vide et une partition d’une ligne ont aussi été vérifiées. Ces tests établissent le comportement sur ces données ; ils ne prédisent pas le coût du tri de millions de lignes.

Pour une charge réelle, examinez la requête à l’aide du guide PostgreSQL EXPLAIN ANALYZE, en anglais. EXPLAIN ANALYZE exécute la requête. Le guide pg_stat_statements, en anglais aide à repérer les requêtes qui méritent cette analyse. Une fonction fenêtre ou un index supplémentaire ne garantit pas un gain de vitesse.

Après vérification, supprimez uniquement les objets créés pour ces exemples :

DROP TABLE window_lab.sales;
DROP SCHEMA window_lab;

Le nettoyage du test a réussi et le serveur isolé a été arrêté. Pour votre requête, commencez par définir la partition, la règle d’égalité, l’ordre et le cadre. Vérifiez ensuite un ex æquo, une ligne en bordure de partition et une date absente par rapport au résultat attendu.