How To Compute Moving Average Convergence Divergence (MACD) in SQL?

1
2
3
4

SELECT *,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 12 HOUR AND s.date) as short_ema
FROM stock_prices s;

1
2
3
4

SELECT *,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 26 HOUR AND s.date) as long_ema
FROM stock_prices s;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT *,
(short_ema - long_ema) as macd
FROM (
SELECT *,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 12 HOUR AND s.date) as short_ema,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 26 HOUR AND s.date) as long_ema
FROM stock_prices s
) t;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

SELECT *,
(SELECT AVG(macd) FROM (
SELECT *,
(short_ema - long_ema) as macd
FROM (
SELECT *,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 12 HOUR AND s.date) as short_ema,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 26 HOUR AND s.date) as long_ema
FROM stock_prices s
) t
) t2
WHERE t2.rowid BETWEEN t.rowid - 8 AND t.rowid) as signal_line
FROM (
SELECT *,
(short_ema - long_ema) as macd
FROM (
SELECT *,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 12 HOUR AND s.date) as short_ema,
(SELECT AVG(price) FROM stock_prices s2
WHERE s2.date BETWEEN s.date - INTERVAL 26 HOUR AND s.date) as long_ema
FROM stock_prices s
) t
) t;