SQLを使って株にテクニカル指標を出す④ MACD

WITH RECURSIVE
-- 初項を生成するWITH句
wr_select_ema(i, date, close, exponent12, exponent26) AS (
SELECT
	ROW_NUMBER() OVER (ORDER BY date) AS i,
	date,
	close AS close,
	CASE WHEN COUNT(close) OVER w1 = 12 THEN
		ROUND(AVG(close) OVER w1, 10)
	ELSE
		NULL
	END AS exponent12,
	CASE WHEN COUNT(close) OVER w2 = 26 THEN
		ROUND(AVG(close) OVER w2, 10)
	ELSE
		NULL
	END AS exponent26
FROM
	nikkei225
WINDOW
	w1 AS (ORDER BY date ASC ROWS BETWEEN 11 PRECEDING AND current ROW),
	w2 AS (ORDER BY date ASC ROWS BETWEEN 25 PRECEDING AND current ROW)
),
-- 指数移動平均を計算するWITH句
wr_calc_ema AS  (
	SELECT 
		i,
		date,
		close,
		exponent12,
		exponent26
		-- 試験用に3/11日を基準日とした初項を強制代入
		-- 25783.35 AS exponent12,
		-- 26596.25 AS exponent26
	FROM
		wr_select_ema
	WHERE
		i = (SELECT MIN(i) FROM wr_select_ema WHERE exponent26 IS NOT NULL)
		-- 試験用に3/11日を基準日とした初項を強制代入
		-- i = 46
UNION ALL
	SELECT 
		T2.i,
		T2.date, T2.close, 
		CASE WHEN T1.exponent12 IS NOT NULL THEN
			ROUND(T1.exponent12 + (0.153846153846154) * (T2.close-T1.exponent12), 10)
		ELSE
			NULL
		END AS exponent12,
		CASE WHEN T1.exponent26 IS NOT NULL THEN
			ROUND(T1.exponent26 + (0.074074074074074) * (T2.close-T1.exponent26), 10)
		ELSE
			NULL
		END AS exponent26
	FROM
		wr_calc_ema AS T1, wr_select_ema AS T2
	WHERE
		T1.i + 1 = T2.i
),
-- シグナルの初項を計算するWITH句
wr_select_macd(i, date, macd, signal) AS (
	SELECT
		i,
		date,
		exponent12 - exponent26 AS macd,
		CASE WHEN COUNT(close) OVER w = 9 THEN
			ROUND(AVG(exponent12 - exponent26) OVER w, 10)
		ELSE
			NULL
		END AS signal
	FROM
		wr_calc_ema
	WINDOW
		w AS (ORDER BY date ASC ROWS BETWEEN 8 PRECEDING AND current ROW)	
),
-- MACDの計算と、シグナル、ヒストグラムの計算
wr_select_macd_all(i, date, macd, signal, histogram) AS (
	SELECT
		i,
		date,
		ROUND(macd, 2),
		ROUND(signal, 2),
		ROUND(macd - signal, 2) AS histogram
	FROM
		wr_select_macd
	WHERE
		i = (SELECT MIN(i) FROM wr_select_macd WHERE signal IS NOT NULL)
UNION ALL
	SELECT 
		T2.i,
		T2.date,  
		ROUND(T2.macd, 2) AS macd,
		ROUND(T1.signal + (0.2) * (T2.macd - T1.signal), 2) AS signal,
		ROUND(T2.macd - ROUND(T1.signal + (0.2) * (T2.macd - T1.signal), 2), 2) AS histogram
    FROM
		wr_select_macd_all AS T1, wr_select_macd AS T2
	WHERE
		T1.i + 1 = T2.i
) 
SELECT * FROM wr_select_macd_all;