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

Thread
Trouble with AverageAlbert Padley7 Jul
  • Re: Trouble with Averagehsv10 Jul
    • RE: Trouble with AverageRick James16 Jul
      • RE: Trouble with Averagehsv17 Jul
        • RE: Trouble with AverageRick James17 Jul