El promedio móvil exponencial (EMA) se originó en el análisis técnico durante mediados del siglo XX como una técnica de suavizado que otorga mayor peso a las observaciones recientes. A diferencia de los promedios móviles simples, el cálculo de EMA posee una propiedad matemática recursiva donde cada valor depende del EMA calculado previamente, creando una cadena de dependencia que resiste la vectorización. Esta característica lo hace notoriamente difícil de implementar en SQL basado en conjuntos, ya que las funciones de ventana estándar operan en marcos estáticos en lugar de resultados acumulados. Los entrevistadores plantean esta pregunta para evaluar la comprensión del candidato sobre las capacidades recursivas de ANSI SQL y su habilidad para traducir algoritmos iterativos en lógica de conjuntos declarativa.
Matemáticamente, el EMA en el tiempo t se define como: EMAt = α × Precio_t + (1-α) × EMA{t-1}, donde α es el factor de suavización (típicamente 2/(N+1) para un promedio de N períodos). El caso base utiliza el precio del primer período como el EMA inicial. En un contexto de base de datos, enfrentamos el desafío de mantener este cálculo continuo a través de millones de filas ordenadas por marca de tiempo, donde cada fila requiere acceso al resultado calculado de la fila anterior. Las funciones agregadas estándar de ANSI SQL como SUM o AVG no pueden expresar esta dependencia recursiva, y las funciones de ventana con cláusulas ROWS o RANGE solo acceden a valores de entrada en bruto, no a salidas calculadas de filas anteriores.
Implementamos esto utilizando un CTE recursivo (Expresión de Tabla Común) que atraviesa el conjunto de datos ordenado secuencialmente. Primero, establecemos un orden de fila determinista usando ROW_NUMBER() para manejar huecos o marcas de tiempo irregulares. El miembro ancla selecciona la fila inicial para cada partición (por ejemplo, símbolo de acción), configurando el EMA inicial igual al primer precio. Luego, el miembro recursivo une el CTE a la siguiente fila secuencial (donde row_number = anterior + 1) y aplica la fórmula de EMA usando el valor calculado de la iteración anterior. Este enfoque se adhiere estrictamente a los estándares de ANSI SQL:1999 y se ejecuta como una sola operación basada en conjuntos.
WITH RECURSIVE numbered_trades AS ( SELECT symbol, price, trade_time, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY trade_time) AS rn FROM trades ), ema_series AS ( -- Ancla: primera fila para cada símbolo SELECT symbol, price, rn, price AS ema -- EMA inicial igual al primer precio FROM numbered_trades WHERE rn = 1 UNION ALL -- Recursivo: calcular EMA para las filas subsecuentes SELECT t.symbol, t.price, t.rn, 0.2 * t.price + 0.8 * e.ema AS ema -- α = 0.2 para EMA de 9 períodos FROM ema_series e JOIN numbered_trades t ON t.symbol = e.symbol AND t.rn = e.rn + 1 ) SELECT symbol, price, ema, rn FROM ema_series ORDER BY symbol, rn;
Una firma de trading cuantitativa necesitaba rellenar indicadores EMA para cinco años de datos históricos de ticks a través de 5,000 símbolos de acciones para validar un nuevo algoritmo. El conjunto de datos contenía 250 millones de filas de datos de mercado de alta frecuencia, y la solución existente en Python Pandas requería transferir gigabytes de datos a través de la red, causando frecuentes tiempos de espera y errores de memoria en la estación de trabajo de análisis durante períodos de alta volatilidad del mercado.
El equipo primero consideró implementar un script de preprocesamiento en Python utilizando el método ewm() de Pandas. Este enfoque ofrecía un rápido prototipado y una sintaxis familiar para los analistas cuantitativos, y manejaba el cálculo recursivo de manera nativa utilizando extensiones optimizadas en C. Sin embargo, introdujo una sobrecarga significativa de transferencia de datos entre la base de datos PostgreSQL y el servidor de aplicaciones, requería cargar millones de filas en memoria, y necesitaba una lógica compleja de fragmentación para procesar símbolos en lotes sin perder la continuidad del cálculo del EMA a través de los límites de lotes.
En segundo lugar, examinaron un enfoque puramente basado en conjuntos utilizando un SELF JOIN con cálculos de peso exponencial, donde cada fila se une a todas las filas anteriores dentro de un retroceso de 200 períodos y aplica pesajes geométricos. Este método evitó la recursión por completo y teóricamente permitió que el optimizador de la base de datos paralelizar la operación. Sin embargo, sufría de complejidad O(n²) en relación al tamaño de la ventana de retroceso, creando enormes conjuntos de resultados intermedios que abrumaban el tempdb al procesar datos de ticks de alta frecuencia, y proporcionaba solo una aproximación del verdadero EMA debido a la truncación de la ventana finita.
En tercer lugar, evaluaron la solución del CTE recursivo utilizando la sintaxis estándar de ANSI SQL. Este enfoque se ejecutó completamente dentro del motor de la base de datos, eliminó la sobrecarga de transferencia de red y calculó el EMA matemáticamente exacto al seguir estrictamente la definición recursiva. Aunque corría el riesgo de alcanzar límites de profundidad de recursión en historias de símbolos extremadamente largas y se ejecutaba de manera unitaria por símbolo en la mayoría de las implementaciones de ANSI SQL, demostró ser eficiente en el uso de memoria y evitó la explosión cuadrática del método de auto-unión.
Seleccionaron el enfoque de CTE recursivo porque eliminó el movimiento de datos, aseguró una precisión numérica idéntica a Pandas, y podía ser programado como una actualización de vista materializada nativa de la base de datos sin dependencias externas. El DBA configuró el parámetro max_recursive_iterations para acomodar la historia de símbolo más larga (aproximadamente 50,000 ticks por símbolo).
La implementación procesó todo el conjunto de datos de 250 millones de filas en aproximadamente 12 minutos. Los valores resultantes de EMA coincidieron con los cálculos de Pandas dentro de la precisión de punto flotante, validando la corrección matemática de la implementación de SQL. La firma posteriormente puso en producción la consulta como una actualización nocturna de vista materializada, eliminando la necesidad de scripts externos en Python y reduciendo significativamente la complejidad de su canal de datos.
¿Cómo manejas el cálculo cuando la tabla fuente contiene huecos en la secuencia o marcas de tiempo irregulares que no forman una secuencia de enteros perfecta?
Muchos candidatos suponen que trade_time o una columna ID proporciona una secuencia densa adecuada para uniones rn = e.rn + 1. En realidad, los ticks perdidos o los registros eliminados crean huecos que rompen la cadena de recursión. La solución requiere materializar un rango denso utilizando ROW_NUMBER() o DENSE_RANK() antes del CTE recursivo, asegurando enteros consecutivos independientemente de los huecos en las marcas de tiempo. Esto desacopla el orden lógico de los valores de clave física, permitiendo que la recursión continúe ininterrumpida mientras se preserva la secuencia temporal correcta.
¿Por qué podría fallar un enfoque de CTE recursivo para series temporales extremadamente largas (por ejemplo, más de 100,000 filas por símbolo), y cómo mitigas esto dentro de las restricciones de ANSI SQL?
Los candidatos a menudo pasan por alto que el estándar ANSI SQL no manda una profundidad de recursión infinita, y las implementaciones como PostgreSQL tienen un límite predeterminado de 1000 iteraciones mientras que SQL Server tiene un límite de 100. Exceder estos límites aborta la consulta. La mitigación implica el procesamiento por lotes utilizando una tabla de control o un enfoque iterativo, pero dentro de un ANSI SQL estricto, debes aumentar el límite de recursión de la sesión (no conforme a ANSI) o implementar un enfoque híbrido usando funciones de ventana para aproximar el EMA sobre períodos de retroceso fijos (por ejemplo, 200 períodos) donde la decadencia exponencial hace que las contribuciones más antiguas sean insignificantes. Para cálculos exactos, debes asegurarte de que el límite de recursión de la plataforma supere la longitud máxima de tu secuencia o usar un bucle de procedimiento almacenado (prohibido en las restricciones de esta pregunta).
¿Cómo previenes la contaminación cruzada al calcular EMAs para múltiples series temporales independientes (por ejemplo, diferentes símbolos de acciones) simultáneamente en una sola consulta recursiva?
Un error común es omitir la clave de partición del predicado de unión recursiva. Los candidatos escriben t.rn = e.rn + 1 sin incluir t.symbol = e.symbol, causando que la recursión salte entre diferentes símbolos cuando los números de fila se alinean. La implementación correcta requiere llevar la clave de partición (símbolo) a través de ambos miembros ancla y recursivos, y unirse estrictamente tanto en el incremento del número de secuencia como en la igualdad de partición. Esto asegura que el árbol de recursión permanezca aislado por símbolo, creando efectivamente contextos de cálculo separados dentro de la ejecución única de CTE.