mysql - 使用 stddev 优化外汇数据历史记录的子查询

Question

我正在尝试优化以下查询:

SELECT idnum AS ID, 
   (SELECT stddev(close) 
    from hist.eurusd1 
    where idnum between ID-7 AND ID) AS Sdev 
FROM hist.eurusd1 
where idnum between 1001 and 2000;

仅处理 1000 行就需要大约1800 秒，而我还有大约 100 万行需要处理。

简单的 STDDEV 查询执行速度要快得多:

SELECT stddev(close)
from hist.eurusd1
where idnum between 1003 AND 1010; 

第一次获取部分需要 0.047 秒，因此 1000 次查询将需要 47 秒而不是 1800 秒。

我认为子查询中的某些内容使整个第一个查询变慢，但不知道如何优化它。

Answer 1

我意识到这不是一个答案，因为我无法解释为什么给定的子查询如此慢。但我确实弄清楚了如何在大约 30 秒内计算出一百万行，所以我想发布代码:

SELECT a.idnum as id, stddev(b.close)
from eurusd1 as a
JOIN (
   select idnum+7 as ix, close from eurusd1
   UNION ALL
   select idnum+6 as ix, close from eurusd1
   UNION ALL
   select idnum+5 as ix, close from eurusd1
   UNION ALL
   select idnum+4 as ix, close from eurusd1
   UNION ALL
   select idnum+3 as ix, close from eurusd1
   UNION ALL
   select idnum+2 as ix, close from eurusd1
   UNION ALL
   select idnum+1 as ix, close from eurusd1
   UNION ALL
   select idnum as ix, close from eurusd1
    ) as b on a.idnum = b.ix
group by a.idnum