From: Rick James
Date: July 16 2012 9:25pm
Subject: RE: Trouble with Average
List-Archive: http://lists.mysql.com/mysql/227831
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148892B759@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Here's a different way to "smooth" numbers.  It uses an exponential moving =
average instead of "the last 5".

SELECT Time,
       @a :=3D (9 * @a + Value) / 10  AS moving_avg
FROM tbl
JOIN ( SELECT @a :=3D 0 ) AS x;

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.


> -----Original Message-----
> From: Hal?sz S?ndor [mailto:hsv@stripped]
> Sent: Monday, July 09, 2012 7:48 PM
> To: mysql@stripped
> Subject: Re: Trouble with Average
>=20
> >>>> 2012/07/07 12:10 -0600, Albert Padley >>>>
> I have a log file that captures data from various sensors every minute
> that we use to draws graphs on our website.
>=20
> The table looks like this:
>=20
> CREATE TABLE `log` (
>   `id` int(14) NOT NULL auto_increment,
>   `VarName` varchar(255) NOT NULL,
>   `TimeString` datetime NOT NULL,
>   `VarValue` decimal(25,6) NOT NULL,
>   `Validity` int(1) NOT NULL,
>   `Time_ms` decimal(25,6) NOT NULL,
>   PRIMARY KEY  (`id`),
>   KEY `timestamp` (`TimeString`),
>   KEY `name` (`VarName`),
>   KEY `nametimevalue` (`VarName`,`VarValue`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8;
>=20
>=20
> My existing query which works just fine for our purposes:
>=20
> SELECT CONCAT('[', ((UNIX_TIMESTAMP(TimeString)+(".$tz_offset."*3600)))
> * 1000, ' ,', TRUNCATE(VarValue,0), ']') AS value FROM log WHERE
> VarName =3D '04_Set21_SWOS\_085_1300CI' AND SUBSTR(TimeString,1,10) =3D
> CURDATE() ORDER BY TimeString ASC
>=20
> The only issue is that VarValue tends to vary every minute and create a
> graph that is not as smooth as we would like. Therefore, instead of
> retuning VarValue for each minute in the above query, we want to return
> the average of the last 5 values for that VarName.
>=20
> I have been searching around the web all morning and haven't hit on the
> proper solution. Help would be much appreciated.
> <<<<<<<<
> (It would be neater to write DATE(TimeString) =3D CURDATE())
>=20
> I suspect that there is good reason for that, and that that is better
> done afterwards, with "TimeString" and "VarValue" until then separated.
> In MySQL one could in a subquery find the five latest "TimeString"s not
> greater than a given "TimeString" and key off that (if they are not
> unique this is hopeless), but the amount of repetition is enormous. One
> could do the smoothing within SQL with a procedure that reads a cursor,
> or in a script language on the output. ("tz_offset" would become an
> argument to the MySQL procedure.)
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql