Ce modèle, connu sous le nom de jointure 'as-of' ou correspondance précédente la plus proche, provient de bases de données financières où les événements de trading doivent être associés à la citation la plus récente valide au moment de l'exécution. Il se généralise à tout domaine comportant des événements discrets et des dimensions évolutives, comme les calibrages de capteurs IoT ou l'historique des départements des employés. Le défi réside dans la navigation temporelle sans compromettre la performance basée sur des ensembles.
Une approche naïve utilise une sous-requête scalaire corrélée avec un ORDER BY et FETCH FIRST 1 ROW ONLY, ce qui oblige le moteur à exécuter la sous-requête pour chaque ligne (RBAR), entraînant une complexité de O(n²) et une mauvaise localisation dans le cache. Alternativement, une jointure d'inégalité (<=) entre les événements et les points de référence génère un produit semi-cartésien qui explose en taille avant le filtrage, provoquant potentiellement des débordements de disque sur de grands ensembles de données. Les deux approches risquent des délais d'attente lors du traitement de millions de lignes.
La solution robuste emploie une jointure d'inégalité sur les clés de timestamp, puis utilise la fonction de fenêtre ROW_NUMBER() partitionnée par l'ID de l'événement et ordonnée par le timestamp de référence en ordre décroissant. Le filtrage pour row_num = 1 ne conserve que la correspondance la plus proche précédant, transformant l'opération en un tri et filtrage basé sur des ensembles que les optimisateurs peuvent exécuter avec des jointures par hachage ou fusion.
WITH matches AS ( SELECT e.event_id, e.event_time, e.reading, r.calibration_value, ROW_NUMBER() OVER ( PARTITION BY e.event_id ORDER BY r.valid_from DESC ) AS rn FROM events e JOIN reference r ON r.sensor_id = e.sensor_id AND r.valid_from <= e.event_time ) SELECT event_id, event_time, reading, calibration_value FROM matches WHERE rn = 1;
Une usine de fabrication collecte des données de vibration provenant de 5 000 capteurs chaque seconde dans vibration_logs. Les coefficients de calibration pour chaque capteur sont mis à jour sporadiquement dans sensor_calibrations (environ une fois par mois). L'équipe d'analytique doit ajuster chaque lecture brute par le facteur de calibration actif à cette microseconde, mais la sous-requête corrélée naïve a pris plus de 3 minutes par lot et bloqué le pipeline d'ingestion.
Solution A (Sous-requête corrélée) : Cette approche repose sur une sous-requête scalaire corrélée pour récupérer la dernière calibration pour chaque ligne de journal de vibration individuellement. Le moteur de base de données évalue cette sous-requête une fois par ligne externe, utilisant généralement une recherche d'index B-arbre sur le timestamp calibrated_at pour localiser l'enregistrement correspondant unique. Bien que cela retourne le bon résultat, cela empêche l'optimiseur d'utiliser des jointures par hachage ou des jointures par fusion et crée une boucle imbriquée.
Solution B (Jointure d'inégalité avec fonction de fenêtre) : Cette méthode utilise une jointure d'inégalité combinée avec la fonction de fenêtre ROW_NUMBER() pour attribuer un rang séquentiel à chaque correspondance de calibration potentielle au sein d'une partition d'événements spécifique à un capteur. Une fois la jointure produite tous les paires candidates, la fonction de fenêtre les classe par temps de calibration en ordre décroissant et filtre pour le rang 1. Cela transforme la logique en une opération basée sur des ensembles propice à un traitement en masse.
Solution C (Union-Tout avec logique conditionnelle) : Cette stratégie fusionne les deux tables via UNION ALL en un seul flux chronologique marqué de drapeaux de type, puis tente d'utiliser LAST_VALUE(... IGNORE NULLS) pour transmettre la dernière calibration connue à travers les lignes d'événements suivantes. Cette approche théoriquement scanne chaque table une seule fois sans explosion de jointure.
IGNORE NULLS n'est pas strictement ANSI SQL (c'est une fonctionnalité optionnelle T611) ; sans cela, la logique devient compliquée et échoue pour des attributs non numériques ; nécessite de trier le flux unifié.Solution choisie : La solution B a été sélectionnée après avoir vérifié que l'optimiseur de requêtes PostgreSQL pouvait effectuer une jointure par fusion partielle combinée avec un opérateur de tri pour la fonction de fenêtre. Le surcoût mémoire de la matérialisation de la jointure intermédiaire a été jugé acceptable à 2 Go de RAM pour 10 millions de lignes. De plus, cette approche a évité la performance non déterministe des boucles imbriquées observées dans la solution A.
Résultat : Le temps d'exécution de la requête est passé de 45 secondes à 1,2 seconde sur l'ensemble de données de production. Le pipeline traite désormais des lots horaires en temps réel sans bloquer le flux d'ingestion continu. Cela a permis à l'équipe d'analytique de générer des rapports de vibration calibrés avec seulement un délai de cinq minutes.
Pourquoi la jointure d'inégalité avec ROW_NUMBER() ne souffre-t-elle pas de la même performance O(n²) que la sous-requête corrélée, malgré la production conceptuelle d'un ensemble intermédiaire important ?
La sous-requête corrélée est dépendante ; elle doit être réévaluée pour chaque ligne externe, entraînant souvent une boucle imbriquée. La jointure d'inégalité est indépendante ; l'optimiseur peut choisir une jointure par hachage ou jointure par fusion pour produire le produit cartésien approximatif, puis appliquer la fonction de fenêtre. De manière cruciale, les moteurs modernes mettent en œuvre une optimisation top-N pour les filtres ROW_NUMBER() = 1, qui interrompt le tri après avoir trouvé la première ligne par partition, transformant effectivement l'opération en une recherche d'index ou un sondage par hachage par événement plutôt qu'un tri complet de toutes les calibrations historiques.
Comment traitez-vous des événements qui se produisent avant l'existence de la première enregistrement de calibration, s'assurant qu'ils reçoivent une valeur par défaut plutôt que d'être jetés ?
La jointure d'inégalité (<=) exclut intrinsèquement les événements précédant le temps de référence minimum parce que la condition de jointure échoue. Pour les inclure, utilisez une JOIN GAUCHE au lieu d'une JOIN INTERNE, puis enveloppez la valeur de référence dans COALESCE pour substituer une valeur par défaut. De plus, vous pouvez ajouter une ligne sentinelle à la table de référence avec valid_from = '1900-01-01' et un coefficient par défaut, garantissant que chaque événement a au moins une correspondance précédant. Cela garantit la clôture relationnelle sans logique de post-filtrage.
Ce problème peut-il être résolu en utilisant uniquement la clause RANGE dans une fonction de fenêtre sans rejoindre les tables, en supposant que les deux ensembles de données sont dans une seule table unifiée ?
Non. La clause RANGE fonctionne sur les lignes de l'ensemble de résultats actuel en fonction de la valeur de la colonne d'ordonnancement ; elle ne peut pas regarder sélectivement les valeurs d'une table physiquement distincte sans un prédicat de jointure. Même si vous unifiez les deux tables via UNION ALL, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW inclurait toutes les lignes précédentes, y compris d'autres événements, pas seulement les lignes de calibration. Pour isoler uniquement les lignes de calibration, vous auriez besoin d'utiliser IGNORE NULLS avec LAST_VALUE, qui n'est pas strictement ANSI SQL (c'est une fonctionnalité optionnelle T611). Par conséquent, une opération de jointure est obligatoire pour une conformité stricte à ANSI SQL lors de la combinaison de deux sources relationnelles distinctes.