SQLで実装するSMA・EMA・MACD：時系列データのノイズ除去と日次差分更新の実践

こんにちは！株式会社DearOneの伊藤です。バックエンドを中心に手がけ、最近はGitHub Copilotを活用した開発プロセスの効率化に注力しています。

1. はじめに：時系列データに潜む「ノイズ」と「トレンド」

日々蓄積される大量の時系列データ。その生データは「ノイズ」だらけです。
数値の跳ね上がりが、単なる一時的な現象なのか、あるいは新たなトレンドの始まりなのか。
それを見極めるには、過去の数値をただ集計するだけでなく、そこから一時的な「ノイズ」を取り除いて、本来のトレンドを見えやすくする。
今回はそのためのアプローチとして、株価チャートなどでおなじみの分析手法をSQLで実装してみます。

BIツールを使えばマウス一つでグラフを描ける時代ですが、あえて今、私たちがSQLでこれらのロジックを実装することには実務上の大きな意味があります。
ツール任せにせず計算過程を自ら組み立てることで、算出された数字の根拠に納得感を持って説明できるようになります。
何より、一般的な指標をそのまま使うだけでなく、ビジネス特有の背景や施策のタイミングに合わせて、より実態に即した形へと柔軟に調整できる強みがあります。

本記事では、そのエッセンスをSQLで実装する手法を解説します。

2. データの準備：J-Quants APIを活用した研究環境

解析の練習には、本物の「ノイズ」を含んだ生データが最適です。
今回は、日本取引所グループ（JPX）が提供する「J-Quants API」の株価四本値API（eq-bars-daily）を使用して、解析を進めます。
Freeアカウントでは、直近12週間を除く過去2年間のデータが取得可能です。

APIからのデータ取得とパラメータ

APIを叩く際、以下の3つのパラメータを指定することで、特定銘柄の一定期間のデータを効率よく取得できます。

• code: 銘柄コード（例："7203"）
• from: 取得開始日（例："20230101"）
• to: 取得終了日（例："20231231"）

これにより、「この銘柄の、この期間のトレンドを分析したい」という要件をピンポイントで満たすデータセットを抽出可能です。

レスポンスJSONとテーブルへのマッピング

APIからは各種データがJSON形式で返却されます。今回のSQL分析を回すために、レスポンスの中から最低限以下の3つのプロパティを抽出し、データベースのテーブルへ格納します。

• Date -> stock_date（日付）
• Code -> stock_code（銘柄コード）
• Close -> close_price（終値）

テーブル設計:
抽出したデータを受け止めるため、分析基盤（DWHなど）に以下のシンプルな構造のテーブルを用意します。

CREATE TABLE stock_daily (
    stock_date DATE,             -- Dateからマッピング
    code VARCHAR(10),    -- Codeからマッピング
    close_price NUMERIC  -- AdjustmentCloseまたはCloseからマッピング
);
CREATE TABLE moving_average (
    stock_code VARCHAR(10) NOT NULL, -- 銘柄コード
    stock_date DATE NOT NULL,        -- 日付
    close_price NUMERIC,             -- 終値
    sma5 NUMERIC,                    -- 5日単純移動平均
    sma25 NUMERIC,                   -- 25日単純移動平均
    sma75 NUMERIC,                   -- 75日単純移動平均
    ema12 NUMERIC,                   -- 12日指数平滑移動平均
    ema26 NUMERIC,                   -- 26日指数平滑移動平均
    macd NUMERIC,                    -- MACD (ema12 - ema26)
    signal NUMERIC,                  -- シグナル (MACDの9日移動平均)
    histogram NUMERIC,               -- ヒストグラム (MACD - シグナル)
    
    -- 銘柄と日付の組み合わせを一意にし、検索と結合を高速化する
    PRIMARY KEY (stock_code, stock_date) 
);

JSONを解析してstock_dailyテーブルにINSERTし、それを元に集計を行ってmoving_averageテーブルにINSERTすることになります。

3. SQLで実装する3つの指標

実際の業務データでは、複数の商品やカテゴリが混在しています。そこで重要になるのが、SQLのウィンドウ関数における PARTITION BY です。

単に ORDER BY dt だけで計算してしまうと、異なる商品のデータが混ざって平均が計算されてしまいます。PARTITION BY code を指定することで、商品（銘柄）ごとに計算の「枠組み」を独立させ、各商品のトレンドを正確に抽出することが可能になります。これが、SQLで時系列分析を行う際の「肝」となる記述です。

① 単純移動平均（SMA: Simple Moving Average）

最も基本となる指標です。一定期間の平均をとることで、曜日による変動などの「ノイズ」を平滑化します。

SELECT
    dt,
    code,
    close_price,
    -- PARTITION BY code で商品ごとにグループ化し、
    -- 過去4行＋現在の行（計5日間）の平均を算出
    AVG(close_price) OVER (
        PARTITION BY code 
        ORDER BY dt 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS sma_5d
FROM stock_daily;

② 指数平滑移動平均（EMA: Exponential Moving Average）

直近のデータに重みを置く手法です。SMAよりもトレンドの変化を素早くキャッチできます。

施策を開始した直後の「微かな反応の変化」を、SMAより早く察知し、予算配分の判断を加速させます。

③ MACD（Moving Average Convergence Divergence）

2本のEMAの差を利用した「勢い（加速度）」を測る指標です。

• MACDライン（短期EMA - 長期EMA）: 今どれだけトレンドが加速しているか、直近の「勢い」を測る指標
• シグナル線（MACDの移動平均）: 勢いの「ベースライン」。直近の平均的なトレンドの強さを示す
• ヒストグラム（MACD - シグナル）: 今の勢い（MACD）がベースライン（シグナル）からどれだけ乖離しているか。「トレンド転換の初動」を視覚的に捉える

現場の壁：「教科書通りのSQL」が実務で破綻する理由

MACDの土台となるEMA（指数平滑移動平均）は、「今日の終値」と「前日のEMA」を使って計算するという再帰的な性質を持っています。
分析対象が数年分・数万銘柄にも及ぶ実務データの場合、これを毎日ゼロから再帰計算（WITH RECURSIVEなど）で求めようとすると、クエリーの実行時間が数時間〜数十時間に膨れ上がってしまうおそれがあります。

これを解決するのが、「初期データの構築」と「日次差分更新」 で違うSQLで行うことです。

ステップ1：初期データの構築（フル計算）

計算をスタートするには、起点を強引に作る必要があります。26日EMAの初期値を作るために「過去26日分の単純移動平均（SMA）」を使用し、さらにそこからMACDの9日シグナル線を算出するため、最低でも過去34日分（26日＋8日）のデータが蓄積されたタイミングで、以下の初期構築SQLを実行します。

-- 【1回目】初期データの登録
-- 過去データを使ってSMAを算出し、それをベースに初回のEMAとMACDを生成する
INSERT INTO moving_average
SELECT 
    stock_code, stock_date, close_price,
    sma5, sma25, sma75, ema12, ema26, macd,
    -- MACDの9日移動平均をシグナル線として算出
    AVG(macd) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 8 FOLLOWING) AS signal,
    -- MACDとシグナルの差分（ヒストグラム）
    macd - (AVG(macd) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 8 FOLLOWING)) AS histogram
FROM (
    SELECT 
        stock_date, stock_code, close_price, sma5, sma25, sma75,
        (sma12 * 11 + close_price * 2) / 13 AS ema12,
        (sma26 * 25 + close_price * 2) / 27 AS ema26,
        ((sma12 * 11 + close_price * 2) / 13) - ((sma26 * 25 + close_price * 2) / 27) AS macd
AS macd
    FROM (
        SELECT 
            stock_date, stock_code, close_price,
            -- 日付を降順(DESC)にし、未来方向(FOLLOWING)を指定することで「過去n日分」を表現するハック
            AVG(close_price) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS sma5,
            AVG(close_price) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 24 FOLLOWING) AS sma25,
            AVG(close_price) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 74 FOLLOWING) AS sma75,
            AVG(close_price) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 25 FOLLOWING) AS sma26,
            AVG(close_price) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) AS sma12
        FROM stock_daily
    ) t
) tt
WHERE stock_code = $1;

ステップ2：2日目以降の「差分更新」
履歴テーブル（moving_average）に初期データが書き込まれれば、全件計算は不要です。
翌日からは、LAG関数を使って履歴テーブルから「前日のEMA」を引っ張り出し、当日の終値と掛け合わせるだけで、瞬時に最新の指標が算出できます。

-- 【2回目以降】前日のEMA（履歴）を参照し、当日のデータと掛け合わせて計算コストを最小化する
INSERT INTO moving_average
SELECT 
    stock_code, stock_date, close_price,
    sma5, sma25, sma75, ema12, ema26, macd, signal, histogram
FROM (
    SELECT 
        stock_code, stock_date, close_price,
        sma5, sma25, sma75, ema12, ema26, macd,
        AVG(macd) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 8 FOLLOWING) AS signal,
        macd - (AVG(macd) OVER (PARTITION BY stock_code ORDER BY stock_date DESC ROWS BETWEEN CURRENT ROW AND 8 FOLLOWING)) AS histogram
    FROM (
        SELECT 
            d.stock_code, d.stock_date, d.close_price,
            AVG(d.close_price) OVER (PARTITION BY d.stock_code ORDER BY d.stock_date DESC ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING) AS sma5,
            AVG(d.close_price) OVER (PARTITION BY d.stock_code ORDER BY d.stock_date DESC ROWS BETWEEN CURRENT ROW AND 24 FOLLOWING) AS sma25,
            AVG(d.close_price) OVER (PARTITION BY d.stock_code ORDER BY d.stock_date DESC ROWS BETWEEN CURRENT ROW AND 74 FOLLOWING) AS sma75,
            
            -- 前日のEMA(lag)を11倍し、当日の終値を2倍して13で割る（12日EMAの完全な差分計算）
            COALESCE((LAG(m.ema12) OVER (PARTITION BY d.stock_code ORDER BY d.stock_date) * 11 + d.close_price * 2) / 13, m.ema12) AS ema12,
            COALESCE((LAG(m.ema26) OVER (PARTITION BY d.stock_code ORDER BY d.stock_date) * 25 + d.close_price * 2) / 27, m.ema26) AS ema26,
            
            COALESCE((LAG(m.ema12) OVER (PARTITION BY d.stock_code ORDER BY d.stock_date) * 11 + d.close_price * 2) / 13, m.ema12) -
            COALESCE((LAG(m.ema26) OVER (PARTITION BY d.stock_code ORDER BY d.stock_date) * 25 + d.close_price * 2) / 27, m.ema26) AS macd
            
        FROM stock_daily d 
        -- 前日のデータを参照するために履歴テーブルを結合
        LEFT JOIN moving_average m ON d.stock_code = m.stock_code AND d.stock_date = m.stock_date
    ) dm
) sa
WHERE stock_date = $1;

💡 実務でのパフォーマンス・チューニング
上記のSQLは解説用に全体の構造をシンプルにしていますが、実環境で一番外側に WHERE stock_date = $1 を置くと、データベースは過去全期間のデータを計算してから最後に1日分だけを絞り込むため、計算コストが膨らんでしまいます。 実際の日次バッチ処理に組み込む際は、一番内側の FROM stock_daily d の直後に WHERE d.stock_date >= CURRENT_DATE - INTERVAL '100 days' のように期間指定を追加し、あらかじめ直近数ヶ月分のデータのみに絞り込んでからウィンドウ関数を回すことで、真の「計算コスト最小化」が実現できます。

シグナル期間は「短期と長期の差（26-12=14）の半分〜3分の2程度」や、「短期期間の約0.75倍」あたりが、統計的に「トレンドの転換点」を捉えやすい傾向にあるとされています。

1回目の計算は、重い再帰処理を避けるため直近のSMAで代用した「仮の起点」であり、指定期間のデータしか反映されません。一方、2回目以降は前日データを引き継ぐため、日次更新を繰り返すほど過去の推移が指数関数的に練り込まれ、高精度なEMAへと自動的に収束していきます。
初日から精度の高いMACDを使いたい場合は、100〜200日前のデータで1回目の起点を作り、現在まで2回目の計算を繰り返させてみてください。

全体の数値が右肩上がりでも、ヒストグラムが縮小し始めたらトレンド転換の予兆かもしれません。
ただ過去の数値を集計するだけでなく、データから少し先の変化を読み取り、
次の打ち手を考える、ひとつの参考シグナルとして活用できるかもしれません。

4. おわりに

今回紹介した手法は、株価分析に限らず、売上やDAUなど様々なビジネスデータの「トレンド転換」をいち早く検知するのにも応用できます。
複雑なロジックをSQLという共通言語で体現することで、データソースのすぐ近くで高度な洞察を得ることができるようになります。