SQLで移動平均を計算する

背景

株式分析を始めてみようと、データベース（RDB）に株価を格納した後、分析の第一歩として、とりあえず移動平均を算出してみようと思って調べてみました。

実現方法

標準SQLの中に窓関数（ウィンドウ関数）と呼ばれる関数があります。その関数を使うことで、部分的に切り出した行に対して、集約関数の結果を得ることができます。

GROUP BYと似ていますが、窓関数はレコードを集約せずに各レコードごとに結果を出力します。

例えば、5日移動平均価格であれば、過去4日分の株価と当日の株価の平均になります。つまり、各行に対して、avg(過去4日の株価と当日の株価)という、値を求める事になります。通常このような書き方はできませんが、ウィンドウ関数を使うことで、過去4日の株価と当日の株価を部分的に切り出した形で集計することが可能になります。

要件定義

株価が格納されたDBにMovingAverage（移動平均）という各移動平均価格を持つViewを作成します。
移動平均に使用する価格は終値にします。

実行環境とテーブル設計

下記環境で実行

• OS: Windows10
• DB: SQLite ver.3.36.0
• クライアントソフト: DB Browser for SQLite

テーブル設計

CREATE TABLE "prices" ( -- 4本値と売買高
  "code" TEXT,　 -- 証券コード 
  "date" TEXT,   -- 日付/SQLiteは日付型がないため文字列
  "open" REAL,   -- 始値 
  "high" REAL,   -- 高値 
  "low" REAL,    -- 安値 
  "close" REAL,  -- 終値 
  "volume" INT,  -- 売買高 
  PRIMARY KEY("code","date") 
) 

具体的なSQL文とその説明

まずは、具体的に5日移動平均価格（d5_moving_average）を出力します。

select date, code, close,    
  avg(close) over (PARTITION by code order by date rows between 4 preceding and current row)as d5_moving_average  
from prices   
order by code asc, date asc; 

移動平均価格を窓関数で計算している箇所は下記になります。

avg(close) over (PARTITION by code order by date rows between 4 preceding and current row) 

一つずつ、意味を確認していきます。

avg(close) over   -- OVER以下で記述した範囲で終値の平均を返す 
PARTITION by code -- 証券コード(code)でグループ分け
（テーブル内には、複数の銘柄の価格が入っているため、銘柄ごとに計算する） 
order by date     -- 日付で昇順ソートする（過去に遡る） 
rows between      -- 集計する行の範囲は次の間[between] 
4 preceding and current row -- 4行前[PRECEDING]から現在[current]の行まで 

日本語で整理すると

コードごとに区切り、日付でソートした表の各行に対して、4行前から現在の行までの範囲で終値の平均を取る

という意味になります。

なので、4を

• 24にすれば、25日移動平均線
• 74にすれば、75日移動平均線
• 199にすれば、200日移動平均線
  とそれぞれ計算ができます。

また、over以下の長い部分をまとめて、Window句以下で定義する表記もあります。

select date, code, close,    
avg(close) over w_5 as d5_moving_average -- w_5はWindow以下で定義されている 
from prices  
WINDOW  
w_5 as (PARTITION by code order by date rows between 4 preceding and current row)
order by code asc, date asc; 

よく見る他の指標も追加して、私は下記のビューを作成しました。

CREATE VIEW MovingAverage as select  
date, code, close, volume,  
avg(close) over w_5 as d5_moving_average, 
avg(close) over w_25 as d25_moving_average, 
avg(close) over w_75 as d75_moving_average 
avg(close) over w_200 as d200_moving_average, 
from prices  
WINDOW  
w_5 as (PARTITION by code order by date rows between 4 preceding and current row), 
w_25 as (PARTITION by code order by date rows between 24 preceding and current row), 
w_75 as (PARTITION by code order by date rows between 74 preceding and current row), 
w_200 as (PARTITION by code order by date rows between 199 preceding and current row) 
order by code asc, date asc; 

特定のコードや日付で絞る場合は、Where句を入れてもらえれば、その範囲や銘柄で表示されます。

まとめ

窓関数を使って、株価データから移動平均線の価格をSQL文で記述しました。
他にも窓関数を使えば、平均(avg)ではなく、最大値(max)などにすれば、直近10日の高値を計算するなども可能です。
ブレイクアウトや逆張りなど取引戦略に応じて、色々な指標がSQL文で計算可能になりました。

補足

SQLite標準だと、sqrt（平方根）がなく、ボリンジャーバンドは計算できませんでした。「SQLite 拡張ライブラリ」等でググってもらうと、SQLiteに集計関数を追加する方法がでてきましたので、ご参考まで。