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:hsv@stripped]
> Sent: Monday, July 16, 2012 8:25 PM
> To: mysql@stripped
> 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
