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