Historiquement, les ingénieurs des données ont été confrontés au défi de gérer les lectures de capteurs manquantes en soit en éliminant des enregistrements, soit en utilisant une imputation constante, ce qui déforme à la fois les résultats analytiques. L'interpolation linéaire est devenue la méthode statistiquement préférée pour les processus physiques continus, supposant une trajectoire en ligne droite entre deux points de données connus.
Le problème nécessite de reconstruire des valeurs NULL dans une séquence ordonnée en calculant une valeur proportionnelle basée sur la distance temporelle des mesures valides environnantes. Cela doit être réalisé sans avoir recours à des boucles procédurales ou à des auto-joints, maintenant ainsi une logique pure basée sur des ensembles.
La solution utilise des fonctions de fenêtre conditionnelles pour établir des ancres de limites. MAX avec une plage s'étendant à toutes les lignes précédentes capture la valeur non nulle la plus récente et le timestamp avant le vide actuel. À l'inverse, MIN avec une plage s'étendant à toutes les lignes suivantes capture la prochaine valeur non nulle et le timestamp après le vide. La formule d'interpolation calcule ensuite la moyenne pondérée basée sur le ratio du temps écoulé entre ces limites.
WITH boundaries AS ( SELECT device_id, reading_time, reading, MAX(CASE WHEN reading IS NOT NULL THEN reading_time END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS UNBOUNDED PRECEDING) as prev_time, MAX(CASE WHEN reading IS NOT NULL THEN reading END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS UNBOUNDED PRECEDING) as prev_val, MIN(CASE WHEN reading IS NOT NULL THEN reading_time END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_time, MIN(CASE WHEN reading IS NOT NULL THEN reading END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_val FROM sensor_readings ) SELECT device_id, reading_time, COALESCE( reading, prev_val + (next_val - prev_val) * (EXTRACT(EPOCH FROM (reading_time - prev_time)) / NULLIF(EXTRACT(EPOCH FROM (next_time - prev_time)), 0)) ) as interpolated_reading FROM boundaries;
Une entreprise pharmaceutique a surveillé les unités de stockage de vaccins à froid à l'aide de capteurs de température IoT rapportant toutes les minutes. La congestion du réseau pendant les heures de pointe a causé des lectures NULL sporadiques durant 3 à 5 minutes. La conformité réglementaire de la FDA exigeait un historique de température complet sans lacunes, tandis que la simple suppression violait les protocoles de surveillance continue.
La suppression des enregistrements NULL a d'abord été envisagée. Cette approche maintenait l'intégrité factuelle en ne stockant que les valeurs observées. Cependant, elle créait des discontinuités temporelles qui rompaient les agrégations basées sur la durée, telles que le calcul du temps total au-dessus des seuils de température critiques, rendant le jeu de données non conforme aux normes d'audit.
Le dernier observation reportée (LOCF) a été évalué comme une alternative computationnellement triviale utilisant des fonctions de fenêtre LAST_VALUE. Bien que performante, cette méthode supposait que la température restait constante pendant les pannes, ce qui violait les principes thermodynamiques de dérive thermique progressive dans les systèmes de réfrigération, introduisant un biais significatif dans l'analyse de variance et de tendance.
L'interpolation linéaire a finalement été sélectionnée. Cette approche modélisait l'inertie thermique réaliste entre les mesures, préservant les tendances de premier ordre et fournissant des estimations mathématiquement défendables pour les lacunes de courte durée typiques des petits problèmes de réseau. Bien qu'elle supposait un changement linéaire monotone, cela s'est avéré précis pour les brèves périodes stables entre les cycles du compresseur dans le stockage de vaccins.
L'implémentation a réussi à reconstruire 99,2 % des lectures perdues lors des audits de conformité du troisième trimestre. Le jeu de données interpolé a maintenu la variance de température dans une plage de 0,1°C par rapport aux valeurs réellement enregistrées, satisfaisant aux exigences de la FDA sans nécessiter d'importantes mises à niveau de matériel ou de réseaux de capteurs redondants.
Comment gérez-vous les valeurs NULL qui se produisent au tout début ou à la fin d'une partition, où il n'existe qu'une seule valeur de limite pour l'interpolation ?
Les candidats mettent souvent en œuvre la formule d'interpolation sans tenir compte des cas limites, ce qui fait que le calcul renvoie NULL pour les lacunes initiales ou finales où prev_val ou next_val est indéfini. La solution nécessite d'enrober la logique dans une expression CASE : lorsque prev_time IS NULL, utilisez next_val (extrapolation inverse) ; lorsque next_time IS NULL, utilisez prev_val (extrapolation directe ou LOCF) ; sinon appliquez la formule d'interpolation complète. Cela garantit que la requête retourne des résultats pour l'ensemble du jeu de données plutôt que seulement pour l'intervalle intérieur.
Pourquoi l'expression (next_val - prev_val) / (next_time - prev_time) peut-elle échouer en SQL ANSI strict, et quelle modification garantit la justesse numérique ?
L'arithmétique des dates et heures SQL ANSI renvoie un type INTERVAL, et non un scalaire numérique. Tenter une division arithmétique entre des intervalles ou de mélanger des intervalles avec des décimaux déclenche des erreurs d'incompatibilité de type. De plus, une division entière tronquerait les secondes fractionnaires, détruisant ainsi la précision. Les candidats doivent extraire les secondes d'époque en utilisant EXTRACT(EPOCH FROM (next_time - prev_time)) pour obtenir une représentation numérique. Ils doivent également envelopper le dénominateur dans NULLIF(..., 0) pour éviter les erreurs de division par zéro lorsque des lectures valides consécutives partagent des timestamps identiques en raison d'inserts par lot.
Quelle est la différence fondamentale de complexité computationnelle entre cette approche de fonction de fenêtre et une méthode d'auto-joint utilisant des sous-requêtes corrélées, et pourquoi cela est-il important pour les ensembles de données de streaming à haute fréquence ?
L'approche de la fonction de fenêtre s'exécute en temps O(n log n) dominé par l'opération de tri, maintenant une utilisation de mémoire linéaire par rapport à la taille de la partition. Une méthode d'auto-joint avec des sous-requêtes scalaires (par exemple, trouver le prochain non-nul via MIN(time) WHERE time > current) se dégrade en O(n²) car chaque ligne analyse le tableau pour ses voisins, créant des jointures de boucle imbriquées prohibitivement coûteuses. Pour la télémétrie à haute fréquence générant des millions de lignes, la méthode de fonction de fenêtre exploite des analyses d'index ordonnées et une exécution en une seule passe, tandis que les auto-joints causent des produits cartésiens et des débordements de mémoire. Les candidats oublient souvent que les cadres UNBOUNDED FOLLOWING peuvent nécessiter un swap disque pour de grandes partitions, bien que cela reste asymptotiquement supérieur à la complexité quadratique.