Adding a UV 10 minute average to MySQL realtime table

[mcrossley]

I have just added a rolling 10 minute average to my realtime SQL table. The only place I am using at the mo is on this 'play' page
http://weather.wilmslowastro.com/highch ... raphs2.htm
The UV Index is normally quoted as a rolling 10 or 15 minute average value to even out the 'spikes' and lows caused by clouds.

Rather than calculate a rolling average for every record each time it is required which is quite expensive, I decided add the average to the real time table.

Anyway, to calculate the average I used a pre-insert trigger on the real time table, so the value gets calculated every time a new 1 minute entry is added.

First I added a new table column "UV_AVG" as decimal(3,1).

Then created a new trigger:

Code: Select all

DELIMITER $$
DROP TRIGGER RT_INSERT;
CREATE TRIGGER RT_INSERT BEFORE INSERT ON `realtime`
FOR EACH ROW
BEGIN
    SET NEW.UV_AVG = (SELECT ROUND(AVG(t1.UV),1)
                      FROM (SELECT UV
                            FROM realtime
                            ORDER BY LogDateTime DESC LIMIT 10) t1);
END;

This is very simplistic in that it assumes that a new row is added every minute which is good enough for my usage, but it should really count back 10 minutes from entry being added. That would probably be something like (untested)...

Code: Select all

...
FROM realtime
WHERE LogDateTime >= DATE_SUB(NEW.LogDateTime, INTERVAL 10 MINUTE) ) t1

Add "UV_AVG" to the realtimeLogSql.php $rfields array and Bob's some relation or other.

If any of that is incomprehensible, then it probably isn't for you, wait for Steve to plough through 100's of feature requests to get to the UV average one, MySQL support isn't something I am qualified or up for