How to group three variable with different averages in MySQL?

Question

+-----------------+---------------------+
|    date_time    |                vale |
+-----------------+---------------------+
| 12/13/2015 0:00 |        56.75        |
| 12/13/2015 0:15 |        208.75       |
| 12/13/2015 0:30 |        58.8         |
| 12/13/2015 0:45 |        61.79        |
| 12/13/2015 1:00 |        288.65       |
| 12/13/2015 1:15 |        89.1         |
| 12/13/2015 1:30 |        28.9         |
| 12/13/2015 1:45 |        57.04        |
| 12/14/2015 1:00 |        63.87        |
| 12/14/2015 1:15 |        219.83       |
| 12/14/2015 1:30 |        64.95        |
| 12/14/2015 1:45 |        65.24        |
| 12/14/2015 2:00 |        55.67        |
| 12/14/2015 2:15 |        21.63        |
| 12/14/2015 2:30 |        56.75        |
| 12/14/2015 2:45 |        57.04        |
+-----------------+---------------------+

I have date_time and its respective value , now how to take averages based on day,week and hour wise like below:

+-----------------+-----------------+-----------+----------+
|    date_time    |        hour_avg |   day_avg | week_avg |
+-----------------+-----------------+-----------+----------+
| 12/13/2015 0:00 | 96.52           |   106.2   |     90.9 |
| 12/13/2015 1:00 | 115.9           |   106.2   |     90.9 |
| 12/14/2015 1:00 | 103.4           |   75.6    |     90.9 |
| 12/14/2015 2:00 | 47.7            |   75.6    |     90.9 |
+-----------------+-----------------+-----------+----------+

Answer 1

One way to achieve it is to use GROUP BY date and hour + correlated subqueries for entire day/week:

SELECT 
   DATE_ADD(CAST(date_time AS DATE), INTERVAL  HOUR(date_time) HOUR) AS date_time
   ,ROUND(AVG(vale),1) AS hour_avg
   ,ROUND((SELECT AVG(vale) FROM tab t2 WHERE DATE(t2.date_time) = DATE(t.date_time) GROUP BY DATE(date_time)),1) AS  day_avg
   ,ROUND((SELECT AVG(vale) FROM tab t2 WHERE WEEK(t2.date_time) = WEEK(t.date_time) AND YEAR(t.date_time) = YEAR(t2.date_time)  GROUP BY WEEK(date_time)),1) AS  week_avg
FROM tab t
GROUP BY DATE(date_time), HOUR(date_time);

SqlFiddleDemo

Output:

╔═════════════════════════════╦═══════════╦══════════╦══════════╗
║         date_time           ║ hour_avg  ║ day_avg  ║ week_avg ║
╠═════════════════════════════╬═══════════╬══════════╬══════════╣
║ December, 13 2015 00:00:00  ║ 96.5      ║ 106.2    ║ 90.9     ║
║ December, 13 2015 01:00:00  ║ 115.9     ║ 106.2    ║ 90.9     ║
║ December, 14 2015 01:00:00  ║ 103.5     ║ 75.6     ║ 90.9     ║
║ December, 14 2015 02:00:00  ║ 47.8      ║ 75.6     ║ 90.9     ║
╚═════════════════════════════╩═══════════╩══════════╩══════════╝

Answer 2

plan

• compute average at each grouping granularity
• join grains together at hour level

query

select ha.grain, ha.hour_avg, da.day_avg, wa.week_avg
from
(
select date(date_time) + interval hour(date_time) hour as grain, avg(vale) hour_avg
from temperature
group by date(date_time), hour(date_time)
) ha
inner join
(
select date(date_time) as day, avg(vale) as day_avg
from temperature
group by date(date_time)
) da
on date(grain) = da.day
inner join
(
select year(date_time) as year, week(date_time) as week, avg(vale) as week_avg
from temperature
group by year(date_time), week(date_time)
) wa
on wa.year = year(ha.grain)
and wa.week = week(ha.grain)
;

output

+----------------------------+----------+----------+----------+
|           grain            | hour_avg | day_avg  | week_avg |
+----------------------------+----------+----------+----------+
| December, 13 2015 00:00:00 | 96.5225  | 106.2225 | 90.9225  |
| December, 13 2015 01:00:00 | 115.9225 | 106.2225 | 90.9225  |
| December, 14 2015 01:00:00 | 103.4725 | 75.6225  | 90.9225  |
| December, 14 2015 02:00:00 | 47.7725  | 75.6225  | 90.9225  |
+----------------------------+----------+----------+----------+

sqlfiddle