List:General Discussion« Previous MessageNext Message »
From:Albert Padley Date:July 7 2012 6:10pm
Subject:Trouble with Average
View as plain text  
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.

Thanks.

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