List: General Discussion « Previous MessageNext Message » From: Rick James Date: July 17 2012 5:44pm Subject: RE: Trouble with Average View as plain text
```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
> <<<<<<<<
> 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

```