如何在MySQL中分组不同平均的三个变量?

+-----------------+---------------------+
|    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及其各自的值，现在如何根据日、周和小时计算平均值如下:

+-----------------+-----------------+-----------+----------+
|    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 |
+-----------------+-----------------+-----------+----------+

2 个解决方案

#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

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     ║
╚═════════════════════════════╩═══════════╩══════════╩══════════╝

#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

sqlfiddle

#1