Dernière ligne par groupe dans PostgreSQL : rendre le tri de row_number déterministe
Sélectionnez un événement entier par compte avec un départage explicite et une politique délibérée pour les horodatages manquants.
Dans ce guide
La réponse courte
Classez chaque groupe avec row_number sur PARTITION BY son identifiant et ORDER BY l'horodatage de l'événement plus un départage unique stable. Ensuite, filtrez rn=1 dans une requête externe. Utilisez NULLS LAST si les horodatages manquants doivent perdre face aux horodatages connus. Le tri de la fenêtre sélectionne le gagnant au sein d'un groupe ; un ORDER BY externe séparé contrôle l'ordre dans lequel les lignes sélectionnées sont affichées.
Suivez l'exemple de petite requête
Le compte 7 construit possède les événements 10 et 11 avec le même horodatage, donc id DESC sélectionne 11. Le compte 8 possède seulement l'événement 12, donc il sélectionne 12 même si son horodatage est manquant. NULLS LAST ne supprime pas les valeurs manquantes ; il les place après les valeurs connues au sein de chaque groupe. Ce sont des conséquences attendues de la requête, et non un résultat d'une requête exécutée contre ce projet.
Définissez ce que signifie le plus récent
Spécifiez l'horodatage de l'événement utilisé pour la sélection et si les valeurs manquantes sont éligibles. L'heure d'ingestion, l'heure de l'événement métier et un identifiant numérique peuvent représenter des notions différentes de récence. Un identifiant plus élevé est un départage déterministe pratique dans cet exemple, et non la preuve qu'un événement s'est produit plus tard. Accordez-vous sur cette règle avant d'écrire la requête, surtout lorsque les événements peuvent arriver hors ordre.
Conservez la ligne entière du gagnant
Une agrégation telle que max(event_time) trouve un horodatage mais ne sélectionne pas par elle-même les autres colonnes de sa ligne correspondante. Joindre ce maximum avec la table peut retourner plusieurs lignes lorsque les horodatages sont égaux. Le classement par fenêtre attribue une position à chaque candidat tout en conservant sa charge utile et son identifiant. Vous avez toujours besoin d'une politique explicite si le métier veut tous les événements les plus récents ex-aequo plutôt qu'un seul.
Partitionnez et triez indépendamment
PARTITION BY account_id crée un classement séparé pour chaque compte. Le tri de la fenêtre ORDER BY event_time DESC NULLS LAST, id DESC place les horodatages récents connus en premier et brise les horodatages égaux en utilisant l'identifiant. L'identifiant doit distinguer les lignes candidates pour que ce tri soit déterministe. Si la source ne fournit pas une telle clé, choisissez un autre départage unique stable plutôt que de compter sur l'ordre de stockage physique des lignes.
WITH events (id, account_id, event_time, payload) AS (
VALUES
(10, 7, TIMESTAMP '2026-09-30 10:00:00', 'first'),
(11, 7, TIMESTAMP '2026-09-30 10:00:00', 'second'),
(12, 8, NULL::timestamp, 'unknown time')
), ranked AS (
SELECT events.*,
row_number() OVER (
PARTITION BY account_id
ORDER BY event_time DESC NULLS LAST, id DESC
) AS rn
FROM events
)
SELECT id, account_id, event_time, payload
FROM ranked
WHERE rn = 1
ORDER BY account_id;
Filtrez dans la bonne couche de requête
Un résultat de fenêtre est calculé après que les lignes d'entrée ont été sélectionnées par cette couche de requête. La CTE fournit une couche où rn existe comme colonne, et la requête externe la filtre. Distinguez aussi le filtrage des candidats avant le classement du filtrage des gagnants sélectionnés ensuite. Par exemple, le dernier événement réussi et le dernier événement qui se trouve être réussi répondent à des questions différentes et peuvent produire différents comptes dans le résultat.
Définissez une politique pour les identifiants de groupe manquants
Si account_id peut être nul, ces lignes forment une partition ensemble plutôt que de devenir automatiquement des comptes séparés. Décidez s'il faut les exclure avant le classement ou les traiter comme un groupe explicitement défini. De même, NULLS LAST permet à une partition dont tous les horodatages sont nuls de produire un gagnant. Si les horodatages inconnus ne doivent jamais être sélectionnés, excluez-les de l'entrée des candidats au lieu d'espérer que la clause de tri les supprime.
Vérifiez l'ordre de sortie et les modifications concurrentes
Le ORDER BY account_id externe donne un ordre d'affichage ; il ne change pas quel événement a gagné. Sans une clause de tri externe, les applications ne doivent pas supposer que les lignes arrivent dans un ordre de présentation stable. Des événements nouveaux insérés après que la requête a lu son instantané peuvent changer le résultat d'une requête ultérieure. Un départage déterministe rend la sélection bien définie pour les lignes considérées, mais ne fige pas un ensemble de données changeant entre des requêtes séparées.
Inspectez les performances sur la vraie charge de travail
Évaluez le plan de requête avec des tailles de groupe réalistes et les colonnes sélectionnées avant de choisir un index. Un index impliquant les colonnes de partition et de tri peut aider certaines charges de travail, mais ce n'est pas une promesse universelle que le tri disparaît ou que chaque charge utile est couverte. Gardez la sémantique de sélection correcte en premier. Si vous choisissez une autre technique PostgreSQL telle que DISTINCT ON, conservez les mêmes règles de valeurs nulles et de départage lors de la comparaison des résultats.
Points à vérifier
- Définissez l'horodatage et la politique de valeurs nulles.
- Utilisez un départage unique stable.
- Filtrez les résultats de la fenêtre dans une couche externe.
- Gardez les filtres de candidats séparés des filtres de gagnants.
- Utilisez un ORDER BY externe pour la présentation.
Champ d’application
La requête sélectionne un gagnant par partition selon l'ordre indiqué. Elle ne retourne pas tous les ex-aequo, n'infère pas l'heure de l'événement à partir des identifiants, n'exclut pas automatiquement les groupes entièrement nuls et ne garantit pas un plan d'index. Les lignes d'exemple sont hypothétiques.