指数移動平均（EMA）は、20世紀中頃にテクニカル分析の分野で誕生したスムージング手法であり、最近の観測値により大きな重みを与えます。単純移動平均とは異なり、EMAの計算は、各値が以前に計算されたEMAに依存する再帰的な数学的特性を持っており、依存関係のチェーンを形成し、ベクトル化を妨げます。この特性により、標準のウィンドウ関数が静的なフレームで操作するため、セットベースのSQLでの実装が非常に困難になります。面接官は、この質問を通じて候補者のANSI SQLの再帰的機能に対する理解と、反復アルゴリズムを宣言的な集合論理に変換する能力を評価します。
数学的に、時間tにおけるEMAは次のように定義されます：EMAt = α × Price_t + (1-α) × EMA{t-1}、ここでαはスムージングファクター（通常はN期間の平均の場合に対して2/(N+1)）。ベースケースでは、最初の期間の価格を初期EMAとします。データベースの文脈では、各行が前の行の計算された結果にアクセスすることが必要であり、タイムスタンプで順序付けられた数百万行の間でこの連続計算を維持するという課題に直面します。標準のANSI SQLの集約関数（SUMやAVGなど）はこの再帰的な依存関係を表現することができず、ROWSやRANGE句を含むウィンドウ関数は、以前の行からの計算された出力ではなく、生の入力値にしかアクセスしません。
この問題は、順序付けられたデータセットを再帰的に横断する再帰的CTE（共通テーブル式）を使用して実装します。まず、ROW_NUMBER()を使用してギャップまたは不定のタイムスタンプを処理するための決定論的な行の順序を確立します。アンカー要素は、各パーティション（例えば、株式シンボル）に対して最初の行を選択し、初期のEMAを最初の価格に設定します。再帰的なメンバーはその後、次の順序の行（row_number = previous + 1）にCTEを結合し、以前のイテレーションで計算された値を使用してEMAの式を適用します。このアプローチは、ANSI SQL:1999の標準に厳密に準拠し、単一のセットベースの操作として実行されます。
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 ( -- アンカー：各シンボルの最初の行 SELECT symbol, price, rn, price AS ema -- 初期EMAは最初の価格に等しい FROM numbered_trades WHERE rn = 1 UNION ALL -- 再帰的：後続の行のEMAを計算 SELECT t.symbol, t.price, t.rn, 0.2 * t.price + 0.8 * e.ema AS ema -- α = 0.2で9期間EMA 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;
ある量的取引会社は、新しいアルゴリズムを検証するために、5000の株式シンボルにわたる5年間の履歴ティックデータのEMA指標をバックフィルする必要がありました。このデータセットには、2億5000万行の高頻度市場データが含まれており、既存のPython Pandasソリューションは、ネットワークを介してギガバイトのデータを転送する必要があるため、ピーク市場のボラティリティの期間中に頻繁にタイムアウトやメモリエラーが発生していました。
チームはまず、Pandasのewm()メソッドを使用したPython前処理スクリプトの実装を検討しました。このアプローチは迅速なプロトタイピングと量的アナリストにとって馴染みのある構文を提供し、最適化されたC拡張を使用して再帰計算をネイティブに処理しました。しかし、それはPostgreSQLデータベースとアプリケーションサーバー間のデータ転送のオーバーヘッドを著しく引き起こし、数百万行をメモリに読み込む必要があり、バッチ境界を横断してEMA計算の連続性を失うことなくシンボルを処理するための複雑なチャンクロジックが必要でした。
次に、彼らは200期間のルックバック内で各行がすべての前の行に結合し、幾何平均の重みを適用するSELF JOINを使用した純粋なセットベースのアプローチを検討しました。この方法は再帰を完全に回避し、理論的にはデータベースオプティマイザーが操作を並列化できるようにしました。しかし、これはルックバックウィンドウサイズに対してO(n²)の複雑さに悩まされ、高頻度ティックデータを処理する際にtempdbを圧倒する膨大な中間結果セットを生成しました。また、それは有限のウィンドウ切り捨てによる真のEMAの近似を提供しました。
三つ目に、彼らはANSI SQL標準構文を使用した再帰的CTEソリューションを評価しました。このアプローチは、データベースエンジン内で完全に実行され、ネットワーク転送のオーバーヘッドを排除し、再帰的な定義に厳密に従うことによって数学的に正確なEMAを計算しました。これは、非常に長いシンボル履歴で再帰の深さ制限に達するリスクを抱えていましたが、ほとんどのANSI SQL実装でシンボルごとに単一スレッドで実行され、メモリ効率が良く、セルフ結合メソッドの二次的爆発を回避しました。
彼らは、データの移動を排除し、Pandasと同一の数値的精度を保証できる再帰的CTEアプローチを選択しました。また、外部依存関係なしにデータベースネイティブのマテリアライズドビューの更新としてスケジュール可能です。DBAは、最長のシンボル履歴に対応するためにmax_recursive_iterationsパラメータを設定しました（おおよそ50,000ティックごとのシンボル）。
実装は、約12分で2億5000万行のデータセット全体を処理しました。結果として得られたEMA値は、Pandasの計算と浮動小数点精度の範囲内で一致し、SQL実装の数学的正確性を検証しました。その後、同社はこのクエリを夜間のマテリアライズドビューの更新としてプロダクション化し、外部のPythonスクリプトの必要性を排除し、データパイプラインの複雑さを大幅に削減しました。
ソーステーブルにシーケンスのギャップや不規則なタイムスタンプが含まれている場合、計算をどのように処理しますか？
多くの候補者は、trade_timeまたはID列がrn = e.rn + 1結合に適した密なシーケンスを提供すると仮定します。実際には、欠損ティックや削除されたレコードがギャップを生じ、この再帰のチェーンを壊します。この解決策は、再帰的CTEの前に**ROW_NUMBER()またはDENSE_RANK()**を使用して密なランクをマテリアライズし、タイムスタンプのギャップにかかわらず連続した整数を保証します。これにより、論理的な順序が物理的なキー値から切り離され、正しい時間的順序を維持しながら再帰を中断なく進行させることができます。
なぜ再帰的CTEアプローチが非常に長い時系列（例：各シンボルに対して100,000行以上）で失敗する可能性があるのか、そしてこの問題をANSI SQLの制約内でどのように軽減しますか？
候補者はしばしば、ANSI SQL標準が無限の再帰深度を義務付けていないことを見落とします。また、PostgreSQLの実装はデフォルトで1000回のイテレーションを持ち、SQL Serverはデフォルトで100回です。これらの制限を超えると、クエリが中断されます。軽減策は、制御テーブルを使用したバッチ処理または反復アプローチを伴いますが、厳密なANSI SQLのもとでは、セッションの再帰制限を増加させる（非ANSI）か、ウィンドウ関数を使用して固定ルックバック期間にわたる近似EMAを実装する必要があります（例えば、200期間）。正確な計算には、プラットフォームの再帰制限が最大シーケンス長を超えることを確認するか、ストアドプロシージャのループを使用する必要があります（この質問の制約を禁止します）。
複数の独立した時系列（例：異なる株式シンボル）の同時計算時に交差汚染を防ぐためにEMAsをどのように処理しますか？**
一般的なエラーは、再帰結合の述語からパーティションキーを省略することです。候補者はt.rn = e.rn + 1と書きながら、t.symbol = e.symbolを含めずに、行番号が一致している時に再帰が異なるシンボル間を飛んでしまいます。正しい実装は、シンボルを通じてアンカーと再帰メンバーの両方にパーティションキーを保持し、シーケンス番号のインクリメントとパーティションの等価性の両方で厳密に結合を行う必要があります。これにより、再帰ツリーがシンボルごとに隔離された状態を保ち、単一のCTE実行内で計算のコンテキストを効果的に分離します。