List:General Discussion« Previous MessageNext Message »
From:(Hal Date:July 17 2012 3:25am
Subject:RE: Trouble with Average
View as plain text  
>>>> 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.

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