我想計算大量分鐘股票資料的 9 分鐘指數移動平均線 (EMA)，該資料集超過 30,000,000 行，包含大約 4,500 個不同的股票代碼。由于 EMA 的性質，我使用的查詢使用遞回 cte，它始終基于前一行（分鐘）。
該代碼有效，但這是問題所在。為了僅計算兩只股票的 EMA，它們總共構成了 minute_data 表的 14,000 行，查詢耗時 19 分鐘 40 秒。假設整個資料集的每行速度一樣快，那么執行 MYSQL 服務器需要 60 小時到 70 小時之間。
minute_data 表的構建如下：
create table min_data
(
t datetime not null,
ticker varchar(10) not null,
o decimal(10, 4) not null,
h decimal(10, 4) not null,
l decimal(10, 4) not null,
c decimal(10, 4) not null,
primary key (t, ticker)
);
我只會使用以下列：
- t = 每個交易分鐘的日期和時間
- 股票代碼 = 股票代碼（例如特斯拉 -> TSLA）
- c = 每個交易分鐘的收盤價
EMA 計算：
EMA = 收盤價（當前分鐘）* alpha EMA（前一分鐘）*（1-alpha）
SET GLOBAL cte_max_recursion_depth=1000000;
SET @alpha = 2 / (1 9);
CREATE TABLE min_data_EMA9 AS
WITH RECURSIVE t AS (
SELECT t, ticker,
row_number() over (partition by ticker order by t) as QuoteId,
c
FROM min_data
),
ema (t, ticker, QuoteId, c, EMA9) AS (
SELECT *, avg(c) as EMA9
FROM t
WHERE QuoteId between 1 and 8
GROUP BY ticker
UNION ALL
SELECT t2.t,
t2.ticker,
t2.QuoteId,
t2.c,
@alpha * t2.c (1 - @alpha) * EMA9 as EMA9
FROM ema
JOIN t t2
ON ema.QuoteId = t2.QuoteId - 1
AND ema.ticker = t2.ticker
)
SELECT t, ticker, QuoteId, EMA9
FROM ema;
當將第一個 select 陳述句限制
WHERE ticker = 'TOPS'為一個 Stock 時EXPLAIN ANALYZE，該陳述句的函式WITH回傳以下內容（執行時間 = 18 分鐘 39 秒）：
-> Table scan on ema (cost=0.01..37822.72 rows=3025619) (actual time=0.002..4.562 rows=68471 loops=1)
-> Materialize recursive CTE ema (cost=1395569.12..1433391.84 rows=3025619) (actual time=1097987.024..1097996.206 rows=68471 loops=1)
-> Table scan on <temporary> (actual time=0.001..0.001 rows=1 loops=1)
-> Aggregate using temporary table (actual time=30081.632..30081.633 rows=1 loops=1)
-> Filter: (t.QuoteId between 1 and 8) (cost=1.01..306376.90 rows=302562) (actual time=30069.843..30081.576 rows=8 loops=1)
-> Table scan on t (cost=2.50..2.50 rows=0) (actual time=0.001..3.723 rows=68471 loops=1)
-> Materialize CTE t if needed (cost=2.50..2.50 rows=0) (actual time=30069.836..30078.154 rows=68471 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY min_data.ticker ORDER BY min_data.t ) (actual time=29997.654..30020.406 rows=68471 loops=1)
-> Sort: min_data.ticker, min_data.t (cost=2861564.76 rows=27233289) (actual time=29997.639..30002.597 rows=68471 loops=1)
-> Filter: (min_data.ticker = 'TOPS') (cost=2861564.76 rows=27233289) (actual time=0.512..29953.891 rows=68471 loops=1)
-> Table scan on min_data (cost=2861564.76 rows=27233289) (actual time=0.510..27585.660 rows=30323912 loops=1)
-> Repeat until convergence
-> Nested loop inner join (cost=1093007.22 rows=3025619) (actual time=0.010..533731.050 rows=34235 loops=2)
-> Filter: (ema.ticker is not null) (cost=34040.61 rows=302561) (actual time=0.004..50.239 rows=34236 loops=2)
-> Scan new records on ema (cost=34040.61 rows=302561) (actual time=0.003..26.824 rows=34236 loops=2)
-> Filter: (ema.QuoteId = (t2.QuoteId - 1)) (cost=0.25..2.50 rows=10) (actual time=7.784..15.587 rows=1 loops=68471)
-> Index lookup on t2 using <auto_key0> (ticker=ema.ticker) (actual time=0.004..7.431 rows=68471 loops=68471)
-> Materialize CTE t if needed (query plan printed elsewhere) (cost=0.00..0.00 rows=0) (never executed)
我是遞回 cte 的新手，并且在某種程度上也是查詢優化的新手。因此，我將非常感謝您對如何使此查詢更快的建議！
uj5u.com熱心網友回復：
由于您一次只關注一個代碼，這可能會好得多：
PRIMARY KEY(ticker, t)
您是從第一個開始計算 EMA 并一直讀到最后一個嗎？并且不保存任何中間結果？您使用的因素是什么？根據因素，您實際上不需要計算超過最后一百個左右的值。
即使沒有任何變化，您是否存盤每分鐘的收盤價？
假設市場關閉的時間不會超過一個長周末（即沒有 9/11 的打嗝），這應該足夠了（并且矯枉過正）：
WHERE ticker = ?
AND t > NOW() - INTERVAL 4 DAYS
ORDER BY t
我強烈建議您SELECT在您的應用程式中輸入并執行 EMA。它會比OVERMySQL 內部的 /CTE / 存盤函式 / 等快很多。
請注意，我對 的更改PRIMARY KEY將使 和 獲取的行WHERE是ORDER BY連續的并且已經排序。而且，與 不同OVER的是，只需要通過一次。在您的應用程式中，您可以簡單地對從SELECT.
今天你“只有2只股票”；明天你將有 3. 為每只股票做一個 Select 并計算它的 EMA。也就是說，回圈瀏覽您的應用程式內的 2 個股票。
另一個想法...在每個小時結束時（包括一天結束）存盤 EMA。然后，當你想重新計算它時，從那里開始；不會有精度損失，但要做的計算要少得多。
將這些每小時 EMA 存盤在單獨的表中。該值非常簡單地啟動了 EMA 演算法。現在你正在看類似的東西
WHERE ticker = ?
AND t > CONCAT(LEFT(NOW(), 13), ":00:00")
ORDER BY t
該 CONCAT 計算當前小時的開始（作為字串），然后使用它與t(a DATETIME) 進行比較。同樣，我的 PK 和“一次只做一個”建議適用。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/493579.html