Históricamente, los ingenieros de datos enfrentaron el desafío de manejar las lecturas faltantes de sensores al descartar registros o usar imputación constante, ambas opciones distorsionan los resultados analíticos. La interpolación lineal surgió como el método preferido estadísticamente para procesos físicos continuos, asumiendo una trayectoria lineal entre dos puntos de datos conocidos.
El problema requiere reconstruir los valores NULL en una secuencia ordenada al calcular un valor proporcional basado en la distancia temporal de las mediciones válidas circundantes. Esto debe lograrse sin recurrir a bucles procedimentales o unir la tabla consigo misma, manteniendo una lógica puramente basada en conjuntos.
La solución emplea funciones de ventana condicionales para establecer anclas de límite. MAX con un marco que se extiende a todas las filas precedentes captura el valor y la marca de tiempo más reciente no nula antes de la brecha actual. En cambio, MIN con un marco que se extiende a todas las filas siguientes captura el siguiente valor y marca de tiempo no nula después de la brecha. La fórmula de interpolación luego calcula la media ponderada en función de la proporción de tiempo transcurrido entre estos límites.
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;
Una empresa farmacéutica monitoreaba unidades de almacenamiento de vacunas con sensores de temperatura IoT que reportaban cada minuto. La congestión de la red durante las horas operativas pico causó lecturas NULL esporádicas que duraban de 3 a 5 minutos. El cumplimiento regulatorio de la FDA requería un historial completo de temperatura sin huecos, mientras que la simple eliminación violaba los protocolos de monitoreo continuo.
La eliminación de registros NULL se consideró primero. Este enfoque mantenía la integridad fáctica al almacenar solo los valores observados. Sin embargo, creó discontinuidades temporales que rompieron las agregaciones basadas en duración, como el cálculo del tiempo total por encima de los umbrales de temperatura críticos, lo que hacía que el conjunto de datos no cumpliera con los estándares de auditoría.
Last Observation Carried Forward (LOCF) fue evaluado como una alternativa computacionalmente trivial utilizando funciones de ventana LAST_VALUE. Si bien tenía un buen rendimiento, este método asumía que la temperatura permanecía constante durante las interrupciones, lo cual violaba los principios termodinámicos de deriva térmica gradual en sistemas de refrigeración, introduciendo un sesgo significativo en el análisis de varianza y tendencias.
La interpolación lineal fue finalmente seleccionada. Este enfoque modeló la inercia térmica realista entre las mediciones, preservando las tendencias de primer orden y proporcionando estimaciones matemáticamente defendibles para huecos de corta duración típicos de los problemas de red. Aunque asumió un cambio lineal monótono, esto resultó ser preciso para los breves períodos estables entre ciclos de compresor en el almacenamiento de vacunas.
La implementación reconstruyó con éxito el 99.2% de las lecturas eliminadas durante las auditorías de cumplimiento del tercer trimestre. El conjunto de datos interpolado mantuvo la variación de temperatura dentro de 0.1°C de los valores registrados físicamente, cumpliendo con los requisitos de la FDA sin necesidad de costosas actualizaciones de hardware o redes de sensores redundantes.
¿Cómo manejas los valores NULL que ocurren al principio o al final de una partición, donde solo existe un valor de límite para la interpolación?
Los candidatos a menudo implementan la fórmula de interpolación sin considerar los casos límite, lo que provoca que el cálculo devuelva NULL para huecos iniciales o finales donde prev_val o next_val están indefinidos. La solución requiere envolver la lógica en una expresión CASE: cuando prev_time IS NULL, usar next_val (extrapolación hacia atrás); cuando next_time IS NULL, usar prev_val (extrapolación hacia adelante o LOCF); de lo contrario, aplicar la fórmula de interpolación completa. Esto garantiza que la consulta devuelva resultados para todo el conjunto de datos en lugar de solo para el rango interior.
¿Por qué la expresión (next_val - prev_val) / (next_time - prev_time) puede fallar en SQL ANSI estricto, y qué modificación garantiza la corrección numérica?
La aritmética de fecha y hora de SQL ANSI devuelve un tipo INTERVALO, no un escalar numérico. Intentar realizar una división aritmética entre intervalos o mezclar intervalos con decimales genera errores de incompatibilidad de tipos. Además, la división entera truncaría los segundos fraccionarios, destruyendo la precisión. Los candidatos deben extraer los segundos de época utilizando EXTRACT(EPOCH FROM (next_time - prev_time)) para obtener una representación numérica. También deben envolver el denominador en NULLIF(..., 0) para evitar errores de división por cero cuando lecturas válidas consecutivas comparten marcas de tiempo idénticas debido a inserciones por lotes.
¿Cuál es la diferencia fundamental en la complejidad computacional entre este enfoque de función de ventana y un método de auto-unión utilizando subconsultas correlacionadas, y por qué es importante para conjuntos de datos de transmisión de alta frecuencia?
El enfoque de función de ventana se ejecuta en un tiempo O(n log n) dominado por la operación de ordenación, manteniendo un uso de memoria lineal en relación con el tamaño de la partición. Un enfoque de auto-unión con subconsultas escalares (por ejemplo, encontrar el siguiente no nulo mediante MIN(time) WHERE time > current) se degrada a O(n²) a medida que cada fila escanea la tabla en busca de sus vecinos, creando uniones de bucle anidado prohibitivas. Para telemetría de alta frecuencia que genera millones de filas, el método de función de ventana aprovecha los escaneos de índice ordenado y la ejecución de una sola pasada, mientras que las auto-uniones causan productos cartesianos y desbordamientos de memoria. Los candidatos a menudo pasan por alto que los marcos UNBOUNDED FOLLOWING pueden requerir almacenamiento en disco para particiones grandes, aunque esto sigue siendo asintóticamente superior a la complejidad cuadrática.