Historique : Avant que ANSI SQL:2003 n'introduise des fonctions analytiques, le remplissage des données en série temporelle clairsemées nécessitait des auto-joints inefficaces ou des curseurs procéduraux qui traitaient les lignes individuellement. Le modèle LOCF a vu le jour dans des packages statistiques comme SAS et R, où la transmission de la dernière observation connue est une technique standard de nettoyage des données. Par la suite, les fournisseurs de bases de données ont mis en œuvre cette logique dans SQL via des fonctions de fenêtre, avec la clause IGNORE NULLS formalisée dans ANSI SQL:2011 spécifiquement pour traiter ces lacunes de manière déclarative.
Problème : Les réseaux de capteurs et les systèmes de trading financier génèrent fréquemment des valeurs NULL en raison de défaillances de transmission ou d'heures de non-négociation. Les fonctions simples LAG échouent car elles renvoient le prédécesseur immédiat, qui pourrait également être NULL, créant des lacunes dans les métriques calculées. Le défi consiste à scanner en arrière à travers une partition ordonnée jusqu'à rencontrer la valeur non-NULL la plus récente, sans utiliser des auto-joints qui dégradent la performance de manière quadratique.
Solution : Utilisez la fonction de fenêtre LAST_VALUE avec l'option IGNORE NULLS et une spécification de cadre s'étendant du début de la partition jusqu'à la ligne actuelle. Cette configuration indique au moteur de maintenir un tampon en cours de valeurs non-NULL, permettant ainsi de remonter au travers des NULL pour récupérer la dernière observation valide. Pour les systèmes ne disposant pas de IGNORE NULLS, une solution de contournement utilise le COUNT des non-NULL pour créer des groupes stables, bien que cela implique techniquement une sous-requête.
SELECT device_id, reading_time, temperature, LAST_VALUE(temperature IGNORE NULLS) OVER ( PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS locf_temperature FROM sensor_readings;
Une plateforme d'analyse de santé surveille les niveaux de glucose continu chez les patients diabétiques à l'aide de dispositifs portables. En raison d'interférences Bluetooth, environ 12 % des lectures arrivent sous forme de NULL, mais les cliniciens nécessitent des courbes complètes pour les calculs de dosage d'insuline où l'interpolation pourrait être médicalement dangereuse. Une logique LOCF précise est essentielle car des valeurs manquantes pendant le sommeil ou les repas pourraient déclencher de fausses alertes à l'hypoglycémie.
Solution A : Mise à jour procédurale basée sur des curseurs. Un procédure stockée PL/SQL itère à travers les dossiers des patients chronologiquement, maintenant une variable de session pour stocker la dernière lecture de glucose valide et mettant à jour immédiatement les lignes NULL. Avantages : compatible avec les anciennes versions Oracle antérieures au support des fonctions de fenêtre ; facile à comprendre pour les développeurs ayant un arrière-plan en programmation impérative. Inconvénients : le traitement ligne par ligne crée un excès d'I/O et de verrouillage de table ; le traitement de 10 millions de lignes nécessite 45 minutes, rendant les tableaux de bord en temps réel impossibles.
Solution B : Auto-joint avec sous-requête corrélée. La requête effectue un joint gauche pour trouver le timestamp maximum inférieur à la ligne actuelle où le glucose N'EST PAS NULL, recherchant efficacement la valeur précédente pour chaque lacune. Avantages : SQL déclaratif sans code procédural ; fonctionne sur des systèmes conformes à ANSI SQL-92. Inconvénients : la complexité O(n²) cause un ralentissement exponentiel ; la requête expire après 6 heures sur les ensembles de données de production en raison de scans complets de table répétés.
Solution C : Fonction de fenêtre avec IGNORE NULLS. Implémente LAST_VALUE(glucose IGNORE NULLS) partitionné par patient et ordonné par temps, utilisant un seul passage par l'index. Avantages : la complexité O(n log n) s'exécute en 28 secondes sur le même ensemble de données de 10 millions de lignes ; empreinte mémoire minimale et pas de problèmes de verrouillage. Inconvénients : nécessite un support ANSI SQL:2011, nécessitant une mise à niveau de la base de données à partir de l'instance actuelle PostgreSQL 9.5.
L'équipe a sélectionné la solution C après avoir déterminé que le coût de la mise à niveau de la base de données était justifié par l'amélioration de performance de 99 %. La mise en œuvre a permis des alertes en temps réel sur le glucose et a réduit l'utilisation du CPU du serveur de 94 %. En conséquence, la clinique a réussi à surveiller 50 000 patients concurrents sans latence ni valeurs critiques de glucose manquantes.
Question 1 : Pourquoi LAST_VALUE sans IGNORE NULLS renvoie-t-il NULL même lorsque des valeurs non-null précédentes existent dans la partition ?
Par défaut, LAST_VALUE évalue le cadre incluant la ligne actuelle. Lorsque la ligne actuelle contient NULL et que le cadre s'étend à CURRENT ROW, la fonction voit ce NULL comme la dernière valeur dans la fenêtre. Les candidats supposent souvent à tort que la fonction scanne en arrière indéfiniment ; cependant, sans IGNORE NULLS, elle traite les NULL comme des valeurs valides. Le cadre de fenêtre ROWS UNBOUNDED PRECEDING inclut la ligne actuelle, rendant LAST_VALUE équivalent à la valeur de la ligne actuelle à moins d'être explicitement dit d'ignorer les NULL.
Question 2 : Comment pouvez-vous implémenter LOCF dans le SQL ANSI antérieur à 2011 sans IGNORE NULLS, et quelle est l'erreur logique d'utiliser les différences de ROW_NUMBER au lieu de COUNT ?
Vous pouvez utiliser COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) pour créer un identifiant de groupe qui n'augmente que lorsqu'il rencontre des valeurs non-NULL. Tous les NULL suivants partagent ce compte, formant un groupe de portage. Les candidats essaient parfois de soustraire ROW_NUMBER() OVER (ORDER BY ...) de ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...). Cela échoue car cela crée de nouveaux groupes pour chaque lacune entre les non-null plutôt que d'étendre le groupe précédent. La méthode COUNT fonctionne car elle produit un identifiant stable pour toute la période de dernière valeur connue.
Question 3 : Lorsque vous utilisez un encadrement RANGE plutôt que ROWS pour LOCF sur des timestamps avec des doublons, pourquoi les résultats peuvent-ils devenir non déterministes ?
L'encadrement RANGE regroupe les lignes avec des valeurs ORDER BY identiques en groupes de pairs, les traitant comme une seule unité. Si plusieurs lectures de capteur partagent le même timestamp en millisecondes, RANGE UNBOUNDED PRECEDING ne peut pas distinguer leur ordre physique. Lorsque certains doublons contiennent des NULL et d'autres contiennent des valeurs, la fonction de fenêtre pourrait choisir aléatoirement parmi le groupe de pairs en fonction du plan d'exécution. L'encadrement ROWS garantit des résultats déterministes en traitant l'ordre physique des lignes, s'assurant que la séquence spécifique des insertions détermine quelle valeur est portée en avant. Cette distinction est critique pour les données de trading à haute fréquence où les microsecondes comptent.