指数平滑移動平均をSQLで求める

為替レートのデータはあるので，指標値を計算してみることにした．まずは単純移動平均(SMA)と指数平滑移動平均(EMA)を計算する．PostgreSQL8.4のSQLを使った．

以前株ロボの本を読んで，為替レートの自動分析のためにテクニカル分析で使われている指標値を計算しようと考えていたが，ようやく着手した．

為替レートのデータは，RSS生成のために収集してデータベース(postgres)に入っているので，プログラムを組むのではなく，(無理矢理)SQLで工夫して計算してみた．

↑

環境

PostgreSQL 8.4rc1

↑

為替レートデータのテーブル

以下のkawaseテーブルに，毎日の値が入っている．

カラム名 (データ型)	説明
pubdate (timestamp)	発表日時
currency (text)	通貨．USD，EURなど
ttm (numeric)	仲値．その日のレートとして扱う

↑

単純移動平均

まずは単純移動平均．これは普通に，平均対象期間を条件として抽出したレコードをSQLのavg()関数で平均すれば良い．

/*
 * 単純移動平均を計算する
 * target - 対象日
 * span - 平均期間(日)
 * currency - 通貨
*/
CREATE FUNCTION calc_sma(target date, span integer, currency text)
RETURNS real AS $$
SELECT
	avg(ttm)::real
FROM
	kawase
WHERE
	currency = $3 AND
	date_trunc('day', pubdate) BETWEEN
		($1 - interval '1 day' * ($2 - 1) ) AND	$1
$$ LANGUAGE sql;

↑

指数平滑移動平均

指数平滑移動平均(以下EMA)は，次の漸化式で定義されていて，ちとややこしい．

本日のEMA = 前日のEMA + 2/(平均期間 + 1) × (本日のレート - 前日のEMA)

ただしデータ量は有限であり，前日を無限にさかのぼることはできないので，出発点として以下の仮定をおくことになっている．

計算初日のEMA = その日から平均期間さかのぼっての単純平均

漸化式をSQLでどうやって計算するか？というのが問題．

/*
 * 指数平滑移動平均を計算する
 * target - 対象日
 * span - 平均期間(日)
 * currency - 通貨
 * depth - 計算でさかのぼる期間(日)．理論的には無限大だが実用上はある程度とれば良いはず
*/
CREATE FUNCTION calc_ema(target date, span integer, currency text, depth integer)
RETURNS real AS $$
WITH RECURSIVE
-- 連番iを付与する共通表式
win_kawase AS (
	SELECT
		row_number() over (order by pubdate) AS i,
		ttm,
		date_trunc('day', pubdate) AS pdate
		FROM
			kawase
		WHERE
			currency = $3 AND
			date_trunc('day', pubdate) >= $1 - interval '1 day' * $4
),
-- 再帰のための共通表式
recur AS (
	-- 漸化式の初項 (単純平均)
	SELECT
		i,
		(SELECT avg(ttm)
		FROM
			kawase
		WHERE
			pdate BETWEEN
				($1 - interval '1 day' * ($2 * 2 - 1) ) AND ($1 - interval '1 day' * $2)
		)::real AS ema,
		pdate
	FROM
		win_kawase
	WHERE
		i = 1
	UNION ALL
	-- 漸化式の2項目以降
	SELECT
		win_kawase.i,
		(recur.ema + 2/($2 + 1)::real * (ttm - recur.ema))::real AS ema,
		win_kawase.pdate
	FROM
		recur,
		win_kawase
	WHERE
		recur.i = win_kawase.i - 1
)
SELECT ema FROM recur ORDER BY i DESC LIMIT 1;
$$ LANGUAGE sql;

漸化式をSQL一発で解くには再帰SQLを使う必要がある(はず)だが，PostgreSQLの場合は8.4から再帰SQLが使えるぜ．オッホッホ． WITH RECURSIVEで定義しているrecurの箇所が，再帰SQL．

前の項を参照するためのキーとして，row_number()関数(これも8.4から追加！)で発番した値を使っている．

連番を付与するための共通表式(win_kawase) → 再帰SQL(recur) → 本体

という入れ子になっている．

PostgreSQLの8.4で追加されたwindow関数とWITH RECURSIVEによる再帰を使えば，為替レートの計算で色々遊べるかも．

↑

指数平滑移動平均をSQLで求める

指数平滑移動平均をSQLで求める

環境

為替レートデータのテーブル

単純移動平均

指数平滑移動平均

参考