Running mean in MySQL

So I wanted a graph, which showed the actual temperature for the last 48 hours, and a running mean to go with it:

SELECT convert_tz(date,'gmt','cet') AS date, temperature, 
(CASE WHEN (@n := @n + 1) IS NULL THEN NULL 
    WHEN (@cumval := @cumval + temperature) IS NULL THEN NULL
    ELSE (@cumval / @n) END) AS runningmean 
FROM weather 
CROSS JOIN (SELECT @n := 0, @cumval := 0) AS vars 
WHERE convert_tz(date,'gmt','cet') >= now() - interval 48 hour 
ORDER BY 1

Speaks for itself

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.