我有一张这样的表格,其中包含几种气候措施(降雨率、温度等(
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 |
...
可以看出,时间戳(日期时间字段(不遵循任何模式。
我想按小时获取温度和rain_rate的最后 24 个平均值,以及一列带有相关小时数值的列,采用 24 小时格式,按小时 asc 排序。
例如,如果我今天在下午 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
看起来该查询返回字段的正确平均值,但小时字段似乎没有像我需要的那样排序,也没有对应于平均值......
任何帮助都非常感谢。提前谢谢。
按小时平均过去 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
你必须尝试order by datetime
.
查询必须如下所示:
select avg(rain_rate), avg(temperature), hour(datetime) as hour
from weather
where (datetime >= now() - interval 24 hour)
group by hour(datetime)
order by datetime asc;
我希望我能得到帮助。