List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 16 2012 9:25pm
Subject:RE: Trouble with Average
View as plain text  
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:hsv@stripped]
> Sent: Monday, July 09, 2012 7:48 PM
> To: mysql@stripped
> 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

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