La moyenne mobile exponentielle (EMA) a vu le jour dans l'analyse technique au milieu du 20ème siècle en tant que technique de lissage qui donne plus de poids aux observations récentes. Contrairement aux moyennes mobiles simples, le calcul de l'EMA possède une propriété mathématique récursive où chaque valeur dépend de l'EMA précédemment calculée, créant une chaîne de dépendance qui résiste à la vectorisation. Cette caractéristique rend son implémentation notoirement difficile en SQL basé sur des ensembles, car les fonctions de fenêtre standard opèrent sur des cadres statiques plutôt que sur des résultats accumulés. Les interviewers posent cette question pour évaluer la compréhension par le candidat des capacités récursives de ANSI SQL et leur capacité à traduire des algorithmes itératifs en logique déclarative sur des ensembles.
Mathématiquement, l'EMA au temps t est définie comme : EMAt = α × Price_t + (1-α) × EMA{t-1}, où α est le facteur de lissage (typiquement 2/(N+1) pour une moyenne sur N périodes). Le cas de base utilise le prix de la première période comme première EMA. Dans un contexte de base de données, nous faisons face au défi de maintenir ce calcul en cours sur des millions de lignes ordonnées par horodatage, où chaque ligne nécessite d'accéder au résultat calculé de la ligne précédente. Les fonctions d'agrégation standard ANSI SQL telles que SUM ou AVG ne peuvent pas exprimer cette dépendance récursive, et les fonctions de fenêtre avec des clauses ROWS ou RANGE n'accèdent qu'aux valeurs d'entrée brutes, pas aux sorties calculées des lignes précédentes.
Nous implémentons cela à l'aide d'un CTE récursif (Common Table Expression) qui parcourt l'ensemble de données ordonné séquentiellement. Tout d'abord, nous établissons un ordre de ligne déterministe en utilisant ROW_NUMBER() pour gérer les lacunes ou les horodatages irréguliers. Le membre d'ancre sélectionne la ligne initiale pour chaque partition (par exemple, le symbole boursier), définissant l'EMA initiale égale au premier prix. Le membre récursif relie ensuite le CTE à la ligne séquentielle suivante (où row_number = précédent + 1) et applique la formule de l'EMA en utilisant la valeur calculée de l'itération précédente. Cette approche adhère strictement aux normes ANSI SQL:1999 et s'exécute comme une seule opération basée sur un ensemble.
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 ( -- Ancre : première ligne pour chaque symbole SELECT symbol, price, rn, price AS ema -- EMA initiale égale au premier prix FROM numbered_trades WHERE rn = 1 UNION ALL -- Récursif : calculer l'EMA pour les lignes suivantes SELECT t.symbol, t.price, t.rn, 0.2 * t.price + 0.8 * e.ema AS ema -- α = 0.2 pour une EMA sur 9 périodes 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;
Une société de trading quantitatif avait besoin de rétro-remplir des indicateurs EMA pour cinq ans de données historiques de tick sur 5 000 symboles d'actions afin de valider un nouvel algorithme. L'ensemble de données contenait 250 millions de lignes de données de marché à haute fréquence, et la solution Python Pandas existante nécessitait le transfert de gigaoctets de données via le réseau, provoquant des délais d'attente fréquents et des erreurs de mémoire sur la station d'analyse pendant les périodes de forte volatilité du marché.
L'équipe a d'abord envisagé d'implémenter un script de prétraitement en Python utilisant la méthode ewm() de Pandas. Cette approche offrait un prototypage rapide et une syntaxe familière pour les analystes quantitatifs, et elle gérait le calcul récursif nativement à l'aide d'extensions C optimisées. Cependant, elle introduisait un surcoût de transfert de données significatif entre la base de données PostgreSQL et le serveur d'application, nécessitait de charger des millions de lignes en mémoire, et exigeait une logique de traitement de morceaux complexe pour traiter les symboles par lots sans perdre la continuité du calcul de l'EMA à travers les limites des lots.
Deuxièmement, ils ont examiné une approche entièrement basée sur des ensembles utilisant une AUTORELATION avec des calculs de poids exponentiels, où chaque ligne se joint à toutes les lignes précédentes dans une période de retour de 200 périodes et applique des poids géométriques. Cette méthode évitait totalement la récursivité et théoriquement permettait à l'optimiseur de base de données de paralléliser l'opération. Cependant, elle souffrait de complexité O(n²) par rapport à la taille de la fenêtre de retour, créant des ensembles de résultats intermédiaires massifs qui surchargeaient le tempdb lors du traitement de données de tick à haute fréquence, et elle ne fournissait qu'une approximation de la véritable EMA en raison de la troncature de la fenêtre finie.
Troisièmement, ils ont évalué la solution CTE récursive utilisant la syntaxe standard ANSI SQL. Cette approche s'exécutait entièrement au sein du moteur de base de données, éliminait le surcoût de transfert réseau, et calculait l'EMA mathématiquement exacte en respectant strictement la définition récursive. Bien qu'elle risquait de toucher des limites de profondeur de récursion sur des histoires de symboles extrêmement longues et s'exécutait en mode mono-thread par symbole dans la plupart des implementations ANSI SQL, elle s'est avérée efficace en mémoire et a évité l'explosion quadratique de la méthode de jointure automatique.
Ils ont choisi l'approche CTE récursive car elle éliminait le mouvement des données, garantissait une précision numérique identique à celle de Pandas, et pouvait être programmée en tant que mise à jour d'une vue matérialisée native à la base de données sans dépendances externes. Le DBA a configuré le paramètre max_recursive_iterations pour accueillir la plus longue histoire de symbole (environ 50 000 ticks par symbole).
L'implémentation a traité l'ensemble de données de 250 millions de lignes en environ 12 minutes. Les valeurs EMA résultantes correspondaient aux calculs Pandas avec une précision flottante, validant la correcte mathématique de l'implémentation SQL. La société a ensuite mis en production la requête en tant que mise à jour de vue matérialisée nocturne, éliminant le besoin de scripts externes en Python et réduisant considérablement la complexité de leur pipeline de données.
Comment gérez-vous le calcul lorsque la table source contient des lacunes dans la séquence ou des horodatages irréguliers qui ne forment pas une séquence parfaite d'entiers ?
De nombreux candidats supposent que trade_time ou une colonne ID fournit une séquence dense appropriée pour rn = e.rn + 1 joint. En réalité, les ticks manquants ou les enregistrements supprimés créent des lacunes qui rompent la chaîne de récursion. La solution nécessite de matérialiser un classement dense en utilisant ROW_NUMBER() ou DENSE_RANK() avant le CTE récursif, garantissant des entiers consécutifs indépendamment des lacunes d'horodatage. Cela découple l'ordre logique des valeurs de clé physique, permettant à la récursion de se poursuivre sans interruption tout en préservant la bonne séquence temporelle.
Pourquoi une approche de CTE récursive pourrait-elle échouer pour des séries temporelles extrêmement longues (par exemple, plus de 100 000 lignes par symbole), et comment atténuez-vous cela dans les contraintes de ANSI SQL ?
Les candidats oublient souvent que la norme ANSI SQL ne mandate pas une profondeur de récursion infinie, et des implémentations comme PostgreSQL par défaut limitent les itérations à 1000 tandis que SQL Server se limite à 100. Dépasser ces limites annule la requête. L'atténuation implique le traitement par lots en utilisant une table de contrôle ou une approche itérative, mais dans le cadre strict de ANSI SQL, vous devez soit augmenter la limite de récursion de la session (non-ANSI) soit mettre en œuvre une approche hybride utilisant des fonctions de fenêtre pour approximativement l'EMA sur des périodes de retour fixes (par exemple, 200 périodes) où le déclin exponentiel rend les contributions plus anciennes négligeables. Pour des calculs exacts, vous devez vous assurer que la limite de récursion de la plateforme dépasse votre longueur maximale de séquence ou utiliser une boucle de procédure stockée (interdite dans les contraintes de cette question).
Comment empêchez-vous la contamination croisée lors du calcul des EMAs pour plusieurs séries temporelles indépendantes (par exemple, différents symboles boursiers) simultanément dans une seule requête récursive ?
Une erreur courante consiste à omettre la clé de partition dans le prédicat de jointure récursive. Les candidats écrivent t.rn = e.rn + 1 sans inclure t.symbol = e.symbol, causant une rupture de la récursion entre différents symboles lorsque les numéros de ligne s'alignent. La mise en œuvre correcte nécessite de porter la clé de partition (symbole) à travers les membres d'ancre et récursifs, et de s'assurer de rejoindre strictement sur l'incrément du numéro de séquence et l'égalité de partition. Cela garantit que l'arbre de récursion reste isolé par symbole, créant efficacement des contextes de calcul séparés dans l'exécution unique du CTE.