How to average a value in a time window for multiple days, in Mysql?

user_231382 0
Viewed 476

Having this simple log table

id  |val   |dt
-------------------------
 A  100     2014-01-15 00:00:00     
 A  160     2014-01-15 00:00:00      
 A  100     2014-01-15 01:00:00     
 A  160     2014-01-15 02:00:00
 A  200     2014-01-15 03:00:00
 A  80      2014-01-16 01:00:00
 B  100     2014-01-16 02:00:00
 B  200     2014-01-16 01:00:00
 B  100     2014-01-15 02:00:00
 and so on...

I can average a SINGLE day(15), of a SINGLE given id (A), in a specified range (0-2) by doing this

select id, 
       date(dt),
       AVG(val) as av
from (SELECT id, val, dt
      FROM test
      WHERE id = 'A' AND 
            (date(dt) BETWEEN '2014-01-15' AND '2014-01-15') AND 
            ( (time(dt) BETWEEN '00:00' AND '02:00'))) as outerTable

I get this SINGLE result 

id  |date   | average 00:00 to 2:00
-------------------------
A   2014-01-15  120.0000

But how to solve that average of MULTIPLE days in same time period, but in many days in range of months? years? like

id  |date   | average 00:00 to 2:00
-------------------------
A   2014-01-15  120.0000
A   2014-01-16  80.0000 
A   2014-01-17  35.0000
and so on...

Need of loop the same query on different days for the whole month or year, of course the literal loop can be done in php, but it is SLOW, Also I could loop for every ID, making it slower.

mysql average sql
1 Answers

You do not need to use Subquery, to get the data and then calculate the average in the Outer query. It can be done without the subquery itself.

In order to get the average value for different dates, you can remove the WHERE condition on date, to get the data for all the date(s). You can also change the WHERE condition on date to include a range instead.

You also seem to want results for different id values. We can get rid of WHERE condition on id as well.

Eventually, you can do a GROUP BY on the id and date, to get individual rows idwise, and then datewise.

SELECT 
  id, 
  DATE(dt)
  AVG(val) 
FROM test
WHERE 
  TIME(dt) BETWEEN '00:00' AND '02:00'
GROUP BY id, DATE(dt)
user_24693080
Related