From: Rick James Date: July 17 2012 5:44pm Subject: RE: Trouble with Average List-Archive: http://lists.mysql.com/mysql/227837 Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148892B7F5@SP2-EX07VS01.ds.corp.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 :=3D AVG(Value) FROM tbl; or, maybe SELECT @a :=3D AVG(Value) FROM tbl WHERE ...; -- selecting only the first f= ew 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 >=20 > >>>> 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". >=20 > SELECT Time, > @a :=3D (9 * @a + Value) / 10 AS moving_avg FROM tbl JOIN ( > SELECT @a :=3D 0 ) AS x; >=20 > Notes: > * Make 10 larger or smaller, depending on how smooth you want it. > * 9=3D10-1 > * @a :=3D 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. >=20 > The important thing in using decaying average (I so know it) is this: > @a :=3D (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. >=20 >=20 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql