Historia: Antes de que ANSI SQL:2003 introdujera funciones analíticas, llenar datos escasos de series temporales requería auto-uniones ineficientes o cursores procedimentales que procesaban filas individualmente. El patrón LOCF se originó en paquetes estadísticos como SAS y R, donde llevar adelante la última observación conocida es una técnica estándar de limpieza de datos. Los proveedores de bases de datos luego implementaron esta lógica en SQL a través de funciones de ventana, con la cláusula IGNORE NULLS formalizada en ANSI SQL:2011 específicamente para manejar tales lagunas de manera declarativa.
Problema: Las redes de sensores y los sistemas de comercio financiero generan frecuentemente valores NULL debido a fallos de transmisión o horas no operativas. Las funciones simples LAG fallan porque devuelven el predecesor inmediato, que también puede ser NULL, creando lagunas en las métricas calculadas. El desafío requiere escanear hacia atrás a través de una partición ordenada hasta encontrar el valor no nulo más reciente, sin utilizar auto-uniones que degradan el rendimiento cuadráticamente.
Solución: Utiliza la función de ventana LAST_VALUE con la opción IGNORE NULLS y una especificación de marco que se extiende desde el inicio de la partición hasta la fila actual. Esta configuración indica al motor que mantenga un búfer en ejecución de valores no nulos, mirando efectivamente hacia atrás a través de NULL para recuperar la última observación válida. Para sistemas que carecen de IGNORE NULLS, una solución alternativa utiliza COUNT de no nulos para crear grupos estables, aunque esto técnicamente involucra una subconsulta.
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;
Una plataforma de análisis de salud monitorea niveles de glucosa continuos para pacientes diabéticos utilizando dispositivos portátiles. Debido a interferencias de Bluetooth, aproximadamente el 12% de las lecturas llegan como NULL, pero los clínicos requieren curvas completas para cálculos de dosis de insulina donde la interpolación podría ser médicamente insegura. La lógica precisa de LOCF es esencial porque los valores faltantes durante el sueño o las comidas podrían activar alertas falsas de hipoglucemia.
Solución A: Actualización procedimental basada en cursores. Un procedimiento almacenado en PL/SQL itera sobre los registros de pacientes cronológicamente, manteniendo una variable de sesión para almacenar la última lectura válida de glucosa y actualizando filas NULL de inmediato. Pros: compatible con versiones heredadas de Oracle anteriores al soporte de funciones de ventana; fácil de entender para desarrolladores con antecedentes en programación imperativa. Contras: el procesamiento fila por fila crea un exceso de I/O y bloqueo de tablas; procesar 10 millones de filas requiere 45 minutos, lo que hace que los paneles en tiempo real sean imposibles.
Solución B: Auto-unión con subconsulta correlacionada. La consulta realiza una unión izquierda para encontrar la marca de tiempo máxima anterior a la fila actual donde la glucosa NO ES NULL, buscando efectivamente el valor anterior para cada laguna. Pros: SQL declarativo sin código procedimental; funciona en sistemas compatibles con ANSI SQL-92. Contras: la complejidad O(n²) causa una desaceleración exponencial; la consulta se cuela después de 6 horas en conjuntos de datos en producción debido a repetidos escaneos completos de tablas.
Solución C: Función de ventana con IGNORE NULLS. Implementa LAST_VALUE(glucose IGNORE NULLS) particionado por paciente y ordenado por tiempo, utilizando un solo pase a través del índice. Pros: la complejidad O(n log n) se ejecuta en 28 segundos en el mismo conjunto de datos de 10 millones de filas; mínimo consumo de memoria y sin problemas de bloqueo. Contras: requiere soporte ANSI SQL:2011, lo que requiere una actualización de base de datos desde la instancia existente de PostgreSQL 9.5.
El equipo seleccionó la Solución C después de determinar que el costo de la actualización de la base de datos estaba justificado por la mejora del 99% en el rendimiento. La implementación permitió alertas de glucosa en tiempo real y redujo la utilización de CPU del servidor en un 94%. En consecuencia, la clínica monitoreó con éxito a 50,000 pacientes concurrentes sin latencia ni faltantes críticos de picos de glucosa.
Pregunta 1: ¿Por qué LAST_VALUE sin IGNORE NULLS devuelve NULL incluso cuando existen valores no nulos anteriores en la partición?
Por defecto, LAST_VALUE evalúa el marco que incluye la fila actual. Cuando la fila actual contiene NULL y el marco se extiende a CURRENT ROW, la función ve este NULL como el último valor en la ventana. Los candidatos asumen incorrectamente que la función escanea hacia atrás indefinidamente; sin embargo, sin IGNORE NULLS, trata a los NULLs como valores válidos. El marco de ventana ROWS UNBOUNDED PRECEDING incluye la fila actual, haciendo que LAST_VALUE sea equivalente al valor de la fila actual a menos que se le indique explícitamente que desprecie los NULLs.
Pregunta 2: ¿Cómo puedes implementar LOCF en ANSI SQL anterior a 2011 sin IGNORE NULLS, y cuál es el error lógico al usar diferencias de ROW_NUMBER en lugar de COUNT?
Puedes utilizar COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) para crear un identificador de agrupamiento que solo se incrementa al encontrar valores no nulos. Todos los NULLs subsiguientes comparten este conteo, formando un grupo de carga. A veces, los candidatos intentan restar ROW_NUMBER() OVER (ORDER BY ...) de ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...). Esto falla porque crea nuevos grupos para cada laguna entre no nulos en lugar de extender el grupo anterior hacia adelante. El método COUNT funciona porque produce un identificador estable para todo el período del último valor conocido.
Pregunta 3: Cuando se utiliza RANGE en lugar de ROWS en el marco para LOCF en marcas de tiempo con duplicados, ¿por qué los resultados podrían volverse no deterministas?
El marco RANGE agrupa filas con valores idénticos de ORDER BY en grupos compañeros, tratándolos como una sola unidad. Si múltiples lecturas de sensores comparten la misma marca de tiempo en milisegundos, RANGE UNBOUNDED PRECEDING no puede distinguir su orden físico. Cuando algunos duplicados contienen NULLs y otros valores, la función de ventana podría elegir aleatoriamente entre el grupo compañero dependiendo del plan de ejecución. El marco ROWS garantiza resultados deterministas procesando el orden físico de las filas, asegurando que la secuencia específica de inserciones determine qué valor se lleva hacia adelante. Esta distinción es crítica para datos de comercio de alta frecuencia donde los microsegundos importan.