Historia: En el almacenamiento de datos temporales, la técnica de Última Observación Transmitida Hacia Adelante (LOCF) domina la imputación de valores faltantes, utilizando registros válidos anteriores para llenar vacíos. Sin embargo, dominios analíticos específicos—como aplicar conciliaciones al final del día a transacciones financieras intradía o retro-propagar confirmaciones de laboratorio a diagnósticos provisionales anteriores—requieren el enfoque inverso de Siguiente Observación Transmitida Hacia Atrás (NOCB). Históricamente, NOCB se implementó a través de subconsultas correlacionadas o cursores procedimentales, ambos exhiben complejidad O(n²) y no aprovechan los optimizadores modernos basados en conjuntos.
El Problema: Dada una secuencia totalmente ordenada (por ejemplo, event_time), cada valor NULL debe ser reemplazado por el valor no-NULL más cercano que ocurre después de él en la secuencia. Los NULL consecutivos que preceden a un registro válido deben recibir el mismo valor subsiguiente. Las funciones estándar como LEAD() solo acceden a la fila inmediata siguiente, fallando cuando existen múltiples NULL consecutivos antes de un ancla no-NULL. Las auto-uniones y los CTEs recursivos están prohibidos por restricciones de rendimiento.
La Solución: La solución explota la semántica que ignora los NULL de COUNT(expresión). Al contar valores no-NULL desde la fila actual hasta el final de la partición (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), generamos un "identificador de grupo" estable que es idéntico para todas las filas entre dos anclas no-NULL. Dentro de cada grupo, MAX(val)—que también ignora NULLs—recupera el valor del ancla y lo transmite a todas las filas en ese grupo.
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;
Contexto y Descripción del Problema: Una firma de trading de alta frecuencia mantiene una tabla de ejecución que captura operaciones de acciones a nivel de microsegundos. Debido a los protocolos de informes de la bolsa, el "precio consolidado" final para cualquier minuto dado—verificado por la cámara de compensación—llega 30 segundos después de que el minuto termina y se marca solo en el límite (por ejemplo, 14:30:00.000). Para los cálculos regulatorios de TWAP (Precio Promedio Ponderado por Tiempo), cada milisegundo de ese minuto debe reflejar el precio consolidado final, requiriendo rellenar todos los registros anteriores de 14:29:00.000 - 14:29:59.999. El volumen diario excede los 50 millones de filas, y la ventana de procesamiento es de 10 minutos.
Solución 1: Subconsulta Escalar Correlacionada. Este enfoque utiliza una subconsulta escalar para cada fila para localizar el MIN(event_time) de filas futuras donde consolidated_price IS NOT NULL, luego se une de nuevo para recuperar ese precio.
Ventajas: Conceptualmente sencillo para desarrolladores con antecedentes procedimentales.
Desventajas: Ejecuta comparaciones O(n²). En datos de producción, el tiempo de ejecución de la consulta superó los 45 minutos, violando la ventana de procesamiento. Manejar múltiples NULL consecutivos requiere lógica adicional para avanzar, aumentando la complejidad y las tasas de error.
Solución 2: Recorrido con CTE Recursivo. Un CTE recursivo itera hacia atrás fila por fila, transportando el precio no-NULL hacia atrás hasta encontrar otro no-NULL.
Ventajas: Garantizado para funcionar en cualquier base de datos compatible con ANSI SQL.
Desventajas: Los CTEs recursivos procesan filas secuencialmente en muchos motores (por ejemplo, PostgreSQL), lo que resulta en una ejecución de un solo hilo y un posible desbordamiento de pila en particiones profundas. Las pruebas mostraron un tiempo de ejecución de 20 minutos con alta presión de memoria, haciéndolo inadecuado para SLA de producción.
Solución 3: Bucle de Función de Ventana (Elegida). Implementar el patrón de COUNT y MAX. El COUNT que mira hacia atrás crea grupos idénticos para todas las filas que requieren el mismo valor futuro, mientras que MAX propaga ese valor dentro del grupo.
Ventajas: Completamente basado en conjuntos, paralelizable y se ejecuta en tiempo O(n log n) debido a la operación de ordenación. Escala linealmente con el volumen y utiliza ANSI SQL estándar portátil a través de PostgreSQL, SQL Server, Oracle y DB2.
Desventajas: Requiere dos pasadas sobre los datos (el CTE y la consulta externa), aunque los optimizadores modernos a menudo fusionan estas. Requiere un orden total; marcas de tiempo duplicadas requieren una columna de desempate para asegurar la determinación.
Resultado: El tiempo de ejecución de la tubería se redujo de 45 minutos a 8 segundos en el conjunto de datos de 50 millones de filas. La firma eliminó un frágil script de relleno en Python, reduciendo la complejidad de infraestructura y asegurando que los informes regulatorios se generaran dentro de la ventana de cumplimiento.
¿Por qué debe usarse COUNT(column) en lugar de COUNT(*) o ROW_NUMBER() al construir la clave de agrupamiento?
Muchos candidatos utilizan intuitivamente COUNT(*) o ROW_NUMBER(), creyendo que pueden segmentar los datos. COUNT(*) cuenta cada fila indistintamente de los NULL, produciendo un valor único y cambiando monotonamente por cada fila en el marco hacia atrás, lo que impide la formación de grupos estables. ROW_NUMBER() asigna un identificador único a cada fila, destruyendo de manera similar la agrupación. Solo COUNT(column) se incrementa exclusivamente al encontrar valores no-NULL, asignando así el mismo "ID de grupo" a todos los NULL anteriores hasta el próximo límite no-NULL. Esta distinción es crucial porque aprovecha las semánticas que ignoran NULL de las funciones de ventana agregadas para simular un "mira adelante" sin lógica procedimental.
¿Cómo se comporta la consulta si la partición termina con valores NULL finales, y qué modificación asegura un manejo determinista cuando no hay observación futura?
Si las filas finales en la partición ordenada son NULL, COUNT(status_code) evalúa a cero para esas filas. En consecuencia, MAX(status_code) devuelve NULL, lo cual es lógicamente correcto—no existe una observación futura para transportar hacia atrás. Los candidatos a menudo olvidan manejar esto en la lógica empresarial posterior. Para proporcionar un valor predeterminado (por ejemplo, un marcador de posición estático o un valor de una búsqueda externa), se debe envolver el resultado en COALESCE. Además, para distinguir entre "NULL llenos" y "NULL no rellenables" para el monitoreo de calidad de datos, se debe comparar los valores originales y llenos: CASE WHEN status_code IS NULL AND bucket_id = 0 THEN 'UNCONFIRMADO' END.
¿Qué problema de determinismo surge si la cláusula ORDER BY contiene valores duplicados, y por qué cambiar de ROWS a RANGE agrava el problema?
Cuando las claves de ordenación contienen duplicados (empates), la definición del marco de ventana se vuelve ambigua. Usar ROWS (desplazamientos físicos) asigna grupos basados en el orden físico de la tabla, lo que es arbitrario a menos que se proporcione una clave secundaria única de ordenación. Cambiar a RANGE (rangos de valores lógicos) trata todas las filas con el mismo valor de ordenamiento como pares, lo que hace que compartan el mismo marco. En esta solución, si múltiples filas comparten el mismo event_time, RANGE podría agrupar incorrectamente filas NULL con filas no-NULL del mismo marcador de tiempo o dividir grupos de manera impredecible. Los candidatos deben asegurarse de que haya un orden total añadiendo una clave única (por ejemplo, record_id) a la cláusula ORDER BY: ORDER BY event_time, record_id para garantizar la asignación determinista de grupos en todas las implementaciones de ANSI SQL.