Historia de la pregunta
Los promedios ponderados por tiempo surgieron como una métrica crítica en el análisis industrial de IoT y las series temporales financieras. Los medios aritméticos simples distorsionan la realidad porque los valores de los sensores persisten hasta la próxima medición. Antes del estándar ANSI SQL:2003, calcular estos promedios requería cursores procedimentales o costosos auto-uniones. Estos métodos se desempeñaban en una complejidad de tiempo de O(n²).
La introducción de las funciones de ventana LEAD y LAG revolucionó este dominio. Permitieron cálculos de intervalos basados en conjuntos en una sola pasada que se ejecutan en O(n) tiempo. Esto hace que el análisis en tiempo real sobre miles de millones de filas sea factible dentro de la capa de base de datos.
El problema
Dada una tabla readings con columnas device_id, ts (timestamp) y value, el objetivo es calcular un promedio ponderado. Cada fila debe contribuir proporcionalmente al delta de tiempo hasta la siguiente lectura. Matemáticamente, esto es $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$.
La última fila presenta una condición límite. No tiene un timestamp posterior, por lo que su intervalo debe definirse como cero, extrapolado a un tiempo actual, o limitado a un tiempo final conocido. La solución debe evitar cursores, funciones definidas por el usuario o auto-uniones para permanecer puramente declarativa.
La solución
Utilice la función de ventana LEAD para proyectar el siguiente timestamp en la fila actual. Calcule la diferencia de época para derivar el peso. Luego aplique fórmulas estándar de media ponderada.
WITH weighted AS ( SELECT device_id, value, ts, COALESCE( EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY device_id ORDER BY ts) - ts)), 0 ) AS duration_seconds FROM readings ) SELECT device_id, SUM(value * duration_seconds) / NULLIF(SUM(duration_seconds), 0) AS time_weighted_avg FROM weighted GROUP BY device_id;
Este enfoque utiliza PARTITION BY para asegurar que la ventana se reinicie por dispositivo. Esto previene que se mezclen timestamps de diferentes sensores. El COALESCE maneja la fila terminal asignando un peso cero, excluyéndola efectivamente del denominador.
Una línea de fabricación farmacéutica monitorea 200 biorreactores. Cada uno emite datos de temperatura a intervalos irregulares: cada 10 segundos durante fases de calentamiento, pero cada 30 minutos durante períodos de espera inactiva. El equipo de calidad requería un promedio ponderado diario para asegurar el cumplimiento. Un promedio simple sobrecargaría excesivamente las muestras rápidas de calentamiento y subestimaría las esperas estables, potencialmente enmascarando excursiones peligrosas de temperatura.
Una solución propuesta involucró extraer todos los datos a un DataFrame de pandas de Python. Los ingenieros calcularían diff() en los timestamps y computarían la media ponderada. Si bien era flexible, este método transfirió gigabytes de datos a través de la red. También bloqueó la estación de trabajo de análisis al procesar informes de fin de trimestre que abarcaban 90 días de datos de alta frecuencia.
Otra alternativa utilizó una subconsulta correlacionada para encontrar el MIN(ts) mayor que la fila actual para cada dispositivo. Esto se ejecutó correctamente en conjuntos de prueba de 1,000 filas. Sin embargo, mostró un deterioro cuadrático, tomando 45 minutos para la historia completa de un solo reactor.
El equipo seleccionó el enfoque de función de ventana ANSI SQL. Al mantener el cálculo dentro del clúster de PostgreSQL, la consulta aprovechó los escaneos secuenciales paralelos y evitó la sobrecarga de red. La implementación final procesó 50 millones de filas a través de todos los reactores en menos de 12 segundos. Esto permitió actualizaciones de tableros en tiempo real que permitieron a los operadores detectar desviaciones térmicas en minutos en lugar de horas.
¿Cómo manejas la última observación en cada partición donde no existe un timestamp posterior para definir el peso del intervalo?
Los candidatos a menudo omiten la condición límite. Esto causa que el intervalo de la fila terminal se evalúe como NULL, que SQL ignora en las agregaciones. En consecuencia, la contribución de la última lectura se pierde, sesgando el promedio. El enfoque correcto utiliza COALESCE para sustituir ya sea cero o EXTRACT(EPOCH FROM (boundary_time - ts)) si el promedio debe extenderse a un tiempo final conocido como CURRENT_TIMESTAMP.
¿Por qué la fórmula SUM(value * duration) / SUM(duration) representa matemáticamente el promedio ponderado por tiempo, y qué sucede si usas AVG(value) en su lugar?
Esto calcula la media aritmética ponderada donde la duración actúa como el peso $w_i$. Los candidatos a menudo confunden esto con una media geométrica o intentan usar AVG(value * duration), lo que produce una suma de productos sin normalización. Usar AVG(value) trata cada fila por igual, asumiendo pasos de tiempo uniformes, lo que viola el requisito de que los valores de larga duración ejercen una mayor influencia.
¿Cómo afectan los huecos temporales o los timestamps duplicados dentro de la misma partición el comportamiento de la función LEAD, y por qué es esencial PARTITION BY device_id?
Los candidatos a veces olvidan que LEAD opera en el orden físico de las filas dentro de la especificación de la ventana. Sin PARTITION BY device_id, la función calcula intervalos entre diferentes sensores, creando duraciones masivas o negativas sin sentido. Además, si existen timestamps duplicados, LEAD devuelve la siguiente fila distinta independientemente, lo que potencialmente crea intervalos de cero segundos. Los candidatos deben decidir si deduplicar primero utilizando DISTINCT o filtrado con ROW_NUMBER() para evitar errores de división por cero.