计算移动平均 MySQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16121023/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating a Moving Average MySQL?
提问by surfer190
Good Day,
再会，
I am using the following code to calculate the 9 Day Moving average.
我正在使用以下代码来计算 9 天移动平均线。
SELECT SUM(close)
FROM tbl
WHERE date <= '2002-07-05'
AND name_id = 2
ORDER BY date DESC
LIMIT 9
But it does not work because it first calculates all of the returned fields before the limit is called. In other words it will calculate all the closes before or equal to that date, and not just the last 9.
但它不起作用，因为它在调用限制之前首先计算所有返回的字段。换句话说，它将计算该日期之前或等于该日期的所有收盘价，而不仅仅是最后 9 个。
So I need to calculate the SUM from the returned select, rather than calculate it straight.
所以我需要从返回的选择中计算 SUM，而不是直接计算它。
IE. Select the SUM from the SELECT...
IE。从 SELECT... 中选择 SUM
Now how would I go about doing this and is it very costly or is there a better way?
现在我将如何去做这件事，它是非常昂贵的还是有更好的方法？
采纳答案by Akash
Use something like
使用类似的东西
SELECT
sum(close) as sum,
avg(close) as average
FROM (
SELECT
(close)
FROM
tbl
WHERE
date <= '2002-07-05'
AND name_id = 2
ORDER BY
date DESC
LIMIT 9 ) temp
The inner query returns all filtered rows in descorder, and then you avg, sumup those rows returned.
内部查询按desc顺序返回所有过滤的行，然后你avg，sum向上返回这些行。
The reason why the querygiven by you doesn't work is due to the fact that the sumis calculated first and the LIMITclause is applied after the sumhas already been calculated, giving you the sumof all the rows present
query您给出的无效的原因是因为sum首先计算了 并且在已经计算了该LIMIT子句之后应用了该子句sum，从而为您提供了sum存在的所有行
回答by Gordon Linoff
If you want the moving average for each date, then try this:
如果您想要每个日期的移动平均值，请尝试以下操作：
SELECT date, SUM(close),
(select avg(close) from tbl t2 where t2.name_id = t.name_id and datediff(t2.date, t.date) <= 9
) as mvgAvg
FROM tbl t
WHERE date <= '2002-07-05' and
name_id = 2
GROUP BY date
ORDER BY date DESC
It uses a correlated subquery to calculate the average of 9 values.
它使用相关子查询来计算 9 个值的平均值。
回答by Lukas Eder
Starting from MySQL 8, you should use window functions for this. Using the window RANGEclause, you can create a logical windowover an interval, which is very powerful. Something like this:
从 MySQL 8 开始，您应该为此使用窗口函数。使用windowRANGE子句，你可以在一个区间上创建一个逻辑窗口，这个功能非常强大。像这样的东西：
SELECT
date,
close,
AVG (close) OVER (ORDER BY date DESC RANGE INTERVAL 9 DAY PRECEDING)
FROM tbl
WHERE date <= DATE '2002-07-05'
AND name_id = 2
ORDER BY date DESC
For example:
例如：
WITH t (date, `close`) AS (
SELECT DATE '2020-01-01', 50 UNION ALL
SELECT DATE '2020-01-03', 54 UNION ALL
SELECT DATE '2020-01-05', 51 UNION ALL
SELECT DATE '2020-01-12', 49 UNION ALL
SELECT DATE '2020-01-13', 59 UNION ALL
SELECT DATE '2020-01-15', 30 UNION ALL
SELECT DATE '2020-01-17', 35 UNION ALL
SELECT DATE '2020-01-18', 39 UNION ALL
SELECT DATE '2020-01-19', 47 UNION ALL
SELECT DATE '2020-01-26', 50
)
SELECT
date,
`close`,
COUNT(*) OVER w AS c,
SUM(`close`) OVER w AS s,
AVG(`close`) OVER w AS a
FROM t
WINDOW w AS (ORDER BY date DESC RANGE INTERVAL 9 DAY PRECEDING)
ORDER BY date DESC
Leading to:
导致：
date |close|c|s |a |
----------|-----|-|---|-------|
2020-01-26| 50|1| 50|50.0000|
2020-01-19| 47|2| 97|48.5000|
2020-01-18| 39|3|136|45.3333|
2020-01-17| 35|4|171|42.7500|
2020-01-15| 30|4|151|37.7500|
2020-01-13| 59|5|210|42.0000|
2020-01-12| 49|6|259|43.1667|
2020-01-05| 51|3|159|53.0000|
2020-01-03| 54|3|154|51.3333|
2020-01-01| 50|3|155|51.6667|
回答by bebbo
This query is fast:
这个查询很快：
select date, name_id,
case @i when name_id then @i:=name_id else (@i:=name_id)
and (@n:=0)
and (@a0:=0) and (@a1:=0) and (@a2:=0) and (@a3:=0) and (@a4:=0) and (@a5:=0) and (@a6:=0) and (@a7:=0) and (@a8:=0)
end as a,
case @n when 9 then @n:=9 else @n:=@n+1 end as n,
@a0:=@a1,@a1:=@a2,@a2:=@a3,@a3:=@a4,@a4:=@a5,@a5:=@a6,@a6:=@a7,@a7:=@a8,@a8:=close,
(@a0+@a1+@a2+@a3+@a4+@a5+@a6+@a7+@a8)/@n as av
from tbl,
(select @i:=0, @n:=0,
@a0:=0, @a1:=0, @a2:=0, @a3:=0, @a4:=0, @a5:=0, @a6:=0, @a7:=0, @a8:=0) a
where name_id=2
order by name_id, date
If you need an average over 50 or 100 values, it's tedious to write, but worth the effort. The speed is close to the ordered select.
如果您需要平均超过 50 或 100 个值，编写起来很乏味，但值得付出努力。速度接近有序select。
回答by olivier dufour
an other technique is to do a table:
另一种技术是做一个表格：
CREATE TABLE `tinyint_asc` (
`value` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (value)
) ;
?
INSERT INTO `tinyint_asc` VALUES (0),(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),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);
After you can used it like that:
在你可以像这样使用它之后：
select date_add(tbl.date, interval tinyint_asc.value day) as mydate, count(*), sum(myvalue)
from tbl inner join tinyint_asc.value <= 30 -- for a 30 day moving average
where date(date_add(o.created_at, interval tinyint_asc.value day)) between '2016-01-01' and current_date()
group by mydate
