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.
> -----Original Message-----
> From: Hal?sz S?ndor [mailto:h...@tbbs.net]
> Sent: Monday, July 09, 2012 7:48 PM
> To: mysql@lists.mysql.com
> Subject: Re: Trouble with Average
>
> >>>> 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.
>
> The table looks like this:
>
> 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=MyISAM DEFAULT CHARSET=utf8;
>
>
> My existing query which works just fine for our purposes:
>
> SELECT CONCAT('[', ((UNIX_TIMESTAMP(TimeString)+(".$tz_offset."*3600)))
> * 1000, ' ,', TRUNCATE(VarValue,0), ']') AS value FROM log WHERE
> VarName = '04_Set21_SWOS\_085_1300CI' AND SUBSTR(TimeString,1,10) =
> CURDATE() ORDER BY TimeString ASC
>
> 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.
>
> 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) = CURDATE())
>
> 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.)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
