mysql - 在SQL中选择交叉数据

Question

我有一个表格如下:

id      int(20)
ip      varchar(25)
temp    float
batt    int(4)
date    datetime

此表由收集数据的传感器提供，每 15 秒填充一次表。

1   fe80::212:4b00:60d:b27f 17.62   3256    2017-03-20 01:58:31
2   fe80::212:4b00:60d:6215 19.524  3264    2017-03-20 01:58:43
3   fe80::212:4b00:60d:b27f 17.62   3256    2017-03-20 01:58:46
4   fe80::212:4b00:60d:6215 19.524  3263    2017-03-20 01:58:58
5   fe80::212:4b00:60d:b27f 17.62   3256    2017-03-20 01:59:01

我希望通过传感器 (ip) 和按小时 (date) 获取平均温度 (temp)

类似下面的输出:

           IP              00:00   01:00   02:00   ... 
fe80::212:4b00:60d:b27f    18.16   18.20   18.23   ...
fe80::212:4b00:60d:6215    19.54   20.12   20.30   ...

知道如何在 mysql 中获取交叉数据输出吗？

Answer 1

您可以使用条件聚合:

select ip,
       avg(case when hour(date) = 00 then temp end) as temp_00,
       avg(case when hour(date) = 01 then temp end) as temp_01,
       avg(case when hour(date) = 02 then temp end) as temp_02,
       . . .
from t
where date >= '2017-03-20' and
      date < '2017-03-21'
group by ip;