mysql - 从列和 GROUP BY 中获取 AVG 的 SQL 查询

Question

我有一个这样的表格，其中包含几个气候指标(降雨率、温度等)

mysql> select rain_rate, temperature, datetime from weather limit 10; 
+--------------+---------------+----------------------+
| rain_rate    | temperature   | datetime             |
+--------------+---------------+----------------------+
| 5.0000000000 | 24.4000000000 | 2017-02-08 16:00:56  |
| 1.0000000000 | 22.4000000000 | 2017-02-06 12:10:36  |
| 2.0000000000 | 28.7000000000 | 2017-02-02 13:57:15  |
| 5.0000000000 | 24.7000000000 | 2017-02-01 14:14:16  |
| 1.0000000000 | 16.1000000000 | 2017-01-08 06:01:26  |
| 2.0000000000 | 18.2000000000 | 2017-01-12 05:10:43  |
| 3.0000000000 | 11.9000000000 | 2017-01-10 06:20:54  |
| 4.0000000000 | 16.8000000000 | 2017-01-25 16:10:14  |
| 5.0000000000 | 24.4000000000 | 2016-12-18 23:10:56  |
| 4.0000000000 | 26.6000000000 | 2016-12-30 09:03:54  |
...

可以看出，时间戳(日期时间字段)不遵循任何模式。

我想获取温度和降雨率按小时的最近 24 个平均值，以及一个包含相关小时数值的列，采用 24 小时格式, 按小时升序排列。

例如，如果我在今天晚上 18:30 执行查询，它应该返回这 24 行:

+-----------------+------------------+-------+
| avg(rain_rate)  | avg(temperature) | hour  |
+-----------------+------------------+-------+
| 3.5000000000    | 23.1000000000    | 19    |  |
| 1.0000000000    | 22.6000000000    | 20    |  |
| 3.5000000000    | 24.7000000000    | 21    |  |-> hours of "yesterday"
| 4.5000000000    | 23.8000000000    | 22    |  |
                    ...
| 2.0000000000    | 26.3000000000    | 13    |  |
| 1.5000000000    | 21.6000000000    | 14    |  |
| 7.0000000000    | 23.4000000000    | 15    |  |-> hours of "today"
| 2.5000000000    | 21.4000000000    | 16    |  | 
| 7.0000000000    | 21.2000000000    | 17    |  |
| 3.0000000000    | 25.3000000000    | 18    |  |

到目前为止我最好的尝试:

select avg(rain_rate), avg(temperature), hour(datetime) as hour
from weather
where (datetime >= now() - interval 24 hour)
group by hour(datetime) 
order by max(datetime) asc

看起来该查询返回了字段的正确平均值，但是小时字段似乎没有按我需要的那样排序，也没有对应于平均值...

非常感谢任何帮助。提前致谢。

Answer 1

您想在过去 24 小时内按小时计算平均值。

好的。这是一种方法:

select date(datetime), hour(datetime),
       avg(rain_rate), avg(temperature)
from weather 
where (datetime >= now() - interval 24 hour)
group by date(datetime), hour(datetime) 
order by min(datetime);

注意:距当前时间 24 小时可能有点奇怪。您可以获得 25 行记录(两个部分小时)。您可能需要这个 where:

where datetime < curdate() + interval hour(now()) hour and
      datetime >= curdate() + interval hour(now()) - 24 hour