mysql分组求移动平均_mysql - MySQL：同一查询中有两个移动平均线？ - 堆栈内存溢出...

在达到平均值后，您将获得限制。 子查询的正确形式是：

(select avg(Positive)

from (SELECT t2.`Positive`

FROM `tbl_DATA_KeywordResults` as t2

WHERE t2.`DateTime` <= t1.`DateTime`

ORDER BY t2.`DateTime` DESC

LIMIT 96

) t

) AS `DailyAverage`

我不是100％肯定这将作为子查询工作。 我相信MySQL将外部引用( where子句中的内容)限制为一层深层。

在MySQL中有更多痛苦的方法：

select t1.DateTime, t1.RawData,

avg(case when t2.DateTime between avg_96_dt and t1.DateTime then t2.Positive end) as avg96,

avg(case when t2.DateTime between avg_674_dt and t1.DateTime then t2.Positive end) as avg674

from (SELECT t1.`DateTime`, t1.`Positive` AS `RawData`,

(SELECT t2.DateTime

FROM `tbl_DATA_KeywordResults` t2

WHERE t2.`DateTime` <= t1.`DateTime`

ORDER BY t2.`DateTime` DESC

LIMIT 95, 1

) as avg_96_dt,

(SELECT t2.DateTime

FROM `tbl_DATA_KeywordResults` t2

WHERE t2.`DateTime` <= t1.`DateTime`

ORDER BY t2.`DateTime` DESC

LIMIT 673, 1

) as avg_674_dt

FROM `tbl_DATA_KeywordResults` t1

) t1 join

tbl_DATA_KeywordResults t2

group by t1.DateTime, t1.RawData

ORDER BY t1.`DateTime`;

也就是说，获取日期时间范围的限制，然后在不同的步骤中进行平均。