List:General Discussion« Previous MessageNext Message »
From:(Hal Date:July 10 2012 2:48am
Subject:Re: Trouble with Average
View as plain text  
>>>> 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.)

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