Geek Answers Handbook

Calculating Moving Average MySQL?

Good afternoon,

I use the following code to calculate a 9-day moving average.

SELECT SUM(close) FROM tbl WHERE date <= '2002-07-05' AND name_id = 2 ORDER BY date DESC LIMIT 9

But this does not work, because it first calculates all returned fields before the limit is called. In other words, it will calculate all previously closed or equal to this date, not just the last 9.

Therefore, I need to calculate the SUM from the returned select, and not calculate it directly.

IE Select SUM from SELECT ...

Now, how will I do this, and is it very expensive or is there a better way?

+4
source share
4 answers

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 internal query returns all filtered rows in desc order, and then you avg , sum return these rows.

The reason your query doesn't work is because sum computed first, and the LIMIT is applied after sum has already been calculated, giving you the sum all the rows present

+4
source

If you need a moving average for each date, try the following:

 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.

+8
source

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 require an average of more than 50 or 100 values, it is tedious to write, but Worth the effort. The speed is close to an ordered selection.

0
source

another method is to make 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 that you can use this:

 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
0
source

More articles:


All Articles