Oops, there should be an ORDER BY. We Your B,C formula is equivalent to mine; I simplified it by dividing out C. Anyway, I like your way of describing it.
This might be a 'good' initial value: SELECT @a := AVG(Value) FROM tbl; or, maybe SELECT @a := AVG(Value) FROM tbl WHERE ...; -- selecting only the first few rows. > -----Original Message----- > From: Hal?sz S?ndor [mailto:h...@tbbs.net] > Sent: Monday, July 16, 2012 8:25 PM > To: mysql@lists.mysql.com > Subject: RE: Trouble with Average > > >>>> 2012/07/16 14:25 -0700, Rick James >>>> > Here's a different way to "smooth" numbers. It uses an exponential > moving average instead of "the last 5". > > SELECT Time, > @a := (9 * @a + Value) / 10 AS moving_avg FROM tbl JOIN ( > SELECT @a := 0 ) AS x; > > Notes: > * Make 10 larger or smaller, depending on how smooth you want it. > * 9=10-1 > * @a := 0 should be changed to some reasonable starting value, else > the graph will be artificially low to start with. > <<<<<<<< > Hunh, MySQL: to use the optimizer s order for an initial value never > would have occurred to me. > > The important thing in using decaying average (I so know it) is this: > @a := (B * @a + C * Value) / (B + C) and B, C > 0. The bigger B is the > more important the past; therefore, the change is smaller and the graph > is smoother. The smaller B is the less important the initial value is. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
