Respuesta

La cuestión del procesamiento de series temporales en bases de datos relacionales surge en la intersección de la analítica y la programación clásica con SQL. En SQL-92 no hay funciones de ventana especiales, por lo que es necesario ingeniárselas con subconsultas para calcular métricas dinámicas (suma móvil, promedio, etc.) y condiciones por tiempo.

Problema — falta de herramientas estándar para la agregación por ventana móvil, búsqueda del valor anterior/siguiente por tiempo, agrupamiento efectivo por intervalos de calendario arbitrarios (por ejemplo, cálculo de indicadores semanales/mensuales).

Solución:

Usando solo medios estándar, se aplican subconsultas de correlación para cada fila o agrupación por un criterio calculado (por ejemplo, mes, semana):

Ejemplo de código:

-- Ejemplo de agrupación por semanas sin funciones de ventana
SELECT 
   YEAR(event_date) AS year,
   WEEK(event_date) AS week,
   SUM(value) AS total
FROM timeseries
GROUP BY YEAR(event_date), WEEK(event_date)
ORDER BY year, week;

-- Subconsulta correlacionada para buscar el registro anterior
SELECT t1.id, t1.event_date, t1.value,
       (
         SELECT t2.value 
           FROM timeseries t2 
          WHERE t2.event_date < t1.event_date
          ORDER BY t2.event_date DESC
          LIMIT 1
       ) as prev_value
FROM timeseries t1;

Características clave:

Es necesario agrupar y agregar explícitamente por funciones temporales (YEAR, MONTH, WEEK, etc.).
Para obtener cálculos móviles es necesario utilizar subconsultas correlacionadas o tablas temporales.
La eficiencia se ve afectada en grandes volúmenes de datos: la subconsulta para cada fila rápidamente ralentiza la ejecución.

Preguntas capciosas.

¿Siempre WEEK(event_date) define de manera inequívoca la semana calendario para cualquier fecha?

No — diferentes DBMS (e incluso parámetros de un mismo DBMS) definen la primera semana del año de manera diferente (por ejemplo, ISO 8601 vs el sistema estadounidense), lo que puede llevar a resultados diferentes al agregar. Es necesario especificar el modo de operación de la función o utilizar YEARWEEK.

SELECT YEARWEEK(event_date, 1)  -- 1: la semana ISO comienza el lunes
FROM timeseries;

¿El subconsulta correlacionada elimina duplicados automáticamente al buscar el valor anterior?

No, la subconsulta correlacionada no filtra duplicados por defecto. Si en la tabla hay varios eventos en una fecha, la subconsulta devolverá el primero de ellos por orden de clasificación, pero ignorará el resto.

¿Se puede agregar por fechas sin considerar el tiempo a través de GROUP BY?

Sí, pero es necesario eliminar explícitamente la parte del tiempo, por ejemplo, utilizando DATE(event_date) o TRUNC(event_date) en diferentes DBMS:

SELECT DATE(event_datetime), COUNT(*)
FROM events
GROUP BY DATE(event_datetime)

Errores típicos y anti-patrones

Uso de YEAR/MONTH/WEEK sin considerar la localización y estándares del calendario para periodos de informes
Creación de subconsultas correlacionadas demasiado complicadas para ventanas móviles — aumento explosivo del tiempo de ejecución en grandes volúmenes
Trabajar con funciones temporales sin considerar la zona horaria

Ejemplo de la vida

Caso negativo

El equipo calculaba la analítica semanal utilizando funciones WEEK(date), sin ajustar los parámetros a ISO-8601. Como resultado, los informes de la primera semana de enero "se perdían" — parte de los eventos se atribuían a diciembre del año anterior según la lógica estadounidense. La analítica no concordaba.

Ventajas:

Implementación rápida de la descomposición de periodos

Desventajas:

Datos incorrectos, los informes no coinciden con la lógica empresarial

Caso positivo

El especialista implementó YEARWEEK(date, 1) y una tabla de calendario, aumentando significativamente la consistencia de los informes entre países y divisiones comerciales.