Historique : Dans le stockage de données temporelles, la technique du Dernière Observation Reportée (LOCF) domine l'imputation des valeurs manquantes, utilisant les enregistrements valides précédents pour combler les lacunes. Cependant, certains domaines analytiques spécifiques—comme l'application de réconciliations en fin de journée aux transactions financières intrajournalières ou la rétropropagation des confirmations de laboratoire vers des diagnostics provisoires antérieurs—nécessitent l'approche inverse Prochaine Observation Reportée en Arrière (NOCB). Historiquement, le NOCB était mis en œuvre via des sous-requêtes corrélées ou des curseurs procéduraux, qui affichent tous deux une complexité de O(n²) et ne tirent pas parti des optimiseurs modernes basés sur des ensembles.
Le Problème : Étant donné une séquence totalement ordonnée (par exemple, event_time), chaque valeur NULL doit être remplacée par la valeur non-NULL la plus proche qui se produit après elle dans la séquence. Les NULL consécutifs précédant un enregistrement valide devraient recevoir la même valeur suivante. Les fonctions standard comme LEAD() accèdent uniquement à la ligne immédiatement suivante, échouant lorsque plusieurs NULL consécutifs existent avant un point d'ancrage non-NULL. Les auto-joints et les CTE récursifs sont prohibés par des contraintes de performance.
La Solution : La solution exploite la sémantique d'ignorance des NULL de COUNT(expression). En comptant les valeurs non-NULL de la ligne actuelle jusqu'à la fin de la partition (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), nous générons un "identifiant de seau" stable qui est identique pour toutes les lignes entre deux points d'ancrage non-NULL. Au sein de chaque seau, MAX(val)—qui ignore également les NULL—récupère la valeur d'ancrage et la propage à toutes les lignes de ce groupe.
WITH bucketed AS ( SELECT record_id, event_time, status_code, COUNT(status_code) OVER ( ORDER BY event_time, record_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS bucket_id FROM audit_log ) SELECT record_id, event_time, COALESCE( MAX(status_code) OVER ( PARTITION BY bucket_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), 'UNKNOWN' ) AS confirmed_status FROM bucketed;
Contexte et Description du Problème : Une société de trading à haute fréquence maintient une table execution capturant des transactions d'actions au niveau des microsecondes. En raison des protocoles de rapport d'échange, le "prix consolidé" final pour une minute donnée—vérifié par la chambre de compensation—arrive 30 secondes après la fin de la minute et est estampillé uniquement à la frontière (par exemple, 14:30:00.000). Pour les calculs de TWAP (Prix Moyen Pondéré par le Temps) réglementaires, chaque milliseconde de cette minute doit refléter le prix consolidé final, nécessitant un remplissage vers tous les enregistrements 14:29:00.000 - 14:29:59.999. Le volume quotidien dépasse 50 millions de lignes, et la fenêtre de traitement est de 10 minutes.
Solution 1 : Sous-requête scalaire corrélée. Cette approche utilise une sous-requête scalaire pour chaque ligne afin de localiser le MIN(event_time) des lignes futures où consolidated_price IS NOT NULL, puis revient pour récupérer ce prix.
Avantages: Conceptuellement simple pour les développeurs ayant des antécédents procéduraux.
Inconvénients: Exécute des comparaisons O(n²). Sur des données de production, le temps d'exécution de la requête a dépassé 45 minutes, violant la fenêtre de traitement. Gérer plusieurs NULL consécutifs nécessite une logique supplémentaire pour avancer, augmentant la complexité et les taux d'erreur.
Solution 2 : Parcours récursif des CTE. Un CTE récursif itère en arrière ligne par ligne, portant le prix non-NULL en arrière jusqu'à rencontrer un autre non-NULL.
Avantages: Garanti de fonctionner sur toute base de données compatible SQL ANSI.
Inconvénients: Les CTE récursifs traitent les lignes séquentiellement dans de nombreux moteurs (par exemple, PostgreSQL), entraînant une exécution mono-thread et un potentiel de débordement de pile sur des partitions profondes. Les tests ont montré un temps d'exécution de 20 minutes avec une forte pression mémoire, ce qui le rend inadapté aux SLA de production.
Solution 3 : Bucketisation par Fonction de Fenêtre (Choisi). Implémentez le modèle COUNT et MAX. Le COUNT regardant en arrière crée des seaux identiques pour toutes les lignes nécessitant la même valeur future, tandis que MAX propage cette valeur au sein du seau.
Avantages: Entièrement basé sur des ensembles, parallélisable et s'exécute en temps O(n log n) en raison de l'opération de tri. Il évolue de manière linéaire avec le volume et utilise un SQL ANSI standard portable sur PostgreSQL, SQL Server, Oracle et DB2.
Inconvénients: Nécessite deux passes sur les données (le CTE et la requête externe), bien que les optimiseurs modernes fusionnent souvent ces deux. Il impose un ordonnancement total; les horodatages dupliqués nécessitent une colonne de rupture d'égalité pour garantir le déterminisme.
Résultat : Le temps d'exécution de la pipeline est passé de 45 minutes à 8 secondes sur le jeu de données de 50 millions de lignes. La société a éliminé un script de remplissage fragile en Python, réduisant la complexité de l'infrastructure et garantissant que les rapports réglementaires étaient générés dans la fenêtre de conformité.
Pourquoi doit-on utiliser COUNT(column) au lieu de COUNT(*) ou ROW_NUMBER() lors de la construction de la clé de regroupement ?
De nombreux candidats utilisent intuitivement COUNT(*) ou ROW_NUMBER(), croyant que ceux-ci peuvent segmenter les données. COUNT(*) compte chaque ligne, quel que soit les NULL, produisant une valeur unique, changeante de manière monotone pour chaque ligne dans le cadre arrière, ce qui empêche la formation de groupes stables. ROW_NUMBER() attribue un identifiant unique à chaque ligne, détruisant également le regroupement. Seul COUNT(column) s'incrémente exclusivement lors de la rencontre de valeurs non-NULL, assignant ainsi le même "ID de seau" à tous les NULL précédents jusqu'à la prochaine frontière non-NULL. Cette distinction est cruciale car elle tire parti de la sémantique d'ignorance des NULL des fonctions de fenêtre agrégées pour simuler un "regard en avant" sans logique procédurale.
Comment la requête se comporte-t-elle si la partition se termine par des valeurs NULL traînantes, et quelle modification garantit une gestion déterministe lorsqu'aucune observation future n'existe ?
Si les dernières lignes dans la partition ordonnée sont NULL, COUNT(status_code) évalue à zéro pour ces lignes. Par conséquent, MAX(status_code) renvoie NULL, ce qui est logiquement correct—aucune observation future n'existe à porter en arrière. Les candidats oublient souvent de gérer cela dans la logique métier en aval. Pour fournir une valeur par défaut (par exemple, un espace réservé statique ou une valeur d'une recherche externe), on doit envelopper le résultat dans COALESCE. De plus, pour distinguer entre "NULL rempli" et "NULL non remplissable" pour le monitoring de la qualité des données, il convient de comparer les valeurs originales et remplies : CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMED' END.
Quel problème de déterminisme se pose si la clause ORDER BY contient des valeurs dupliquées, et pourquoi le passage de ROWS à RANGE aggrave-t-il le problème ?
Lorsque les clés d'ordonnancement contiennent des duplicats (égalités), la définition du cadre de fenêtre devient ambiguë. L'utilisation de ROWS (décalages physiques) attribue des groupes en fonction de l'ordre physique de la table, ce qui est arbitraire à moins qu'une clé de tri secondaire unique ne soit fournie. Passer à RANGE (plages de valeurs logiques) traite toutes les lignes avec la même valeur d'ordonnancement comme des pairs, ce qui les fait partager le même cadre. Dans cette solution, si plusieurs lignes partagent le même event_time, RANGE pourrait regrouper incorrectement les lignes NULL avec les lignes non-NULL du même horodatage ou diviser les groupes de manière imprévisible. Les candidats doivent garantir un ordonnancement total en ajoutant une clé unique (par exemple, record_id) à la clause ORDER BY : ORDER BY event_time, record_id pour garantir une attribution déterministe des seaux à travers toutes les implémentations SQL ANSI.