Mariadb:选择按小时和其他列的平均值

Question

我在Mariadb 10.3.27版本的数据库中有一个表，看起来像这样：

+----+------------+---------------+-----------------+
| id | channel_id | timestamp     | value           |
+----+------------+---------------+-----------------+
|  1 |          2 | 1623669600000 | 2882.4449252449 |
|  2 |          1 | 1623669600000 | 295.46914369742 |
|  3 |          2 | 1623669630000 |   2874.46365243 |
|  4 |          1 | 1623669630000 | 295.68124546516 |
|  5 |          2 | 1623669660000 | 2874.9638893452 |
|  6 |          1 | 1623669660000 | 295.69561247521 |
|  7 |          2 | 1623669690000 | 2878.7120274678 |

我想得到这样的结果：

+------+-------+-------+
| hour | valhh | valwp |
+------+-------+-------+
|    0 |   419 |   115 |
|    1 |   419 |   115 |
|    2 |   419 |   115 |
|    3 |   419 |   115 |
|    4 |   419 |   115 |
|    5 |   419 |   115 |
|    6 |   419 |   115 |
|    7 |   419 |   115 |
|    8 |   419 |   115 |
|    9 |   419 |   115 |
|   10 |   419 |   115 |
|   11 |   419 |   115 |
|   12 |   419 |   115 |
|   13 |   419 |   115 |
|   14 |   419 |   115 |
|   15 |   419 |   115 |
|   16 |   419 |   115 |
|   17 |   419 |   115 |
|   18 |   419 |   115 |
|   19 |   419 |   115 |
|   20 |   419 |   115 |
|   21 |   419 |   115 |
|   22 |   419 |   115 |
|   23 |   419 |   115 |
+------+-------+-------+

但是valhh(valwp(是channel_id为1(2(的所有天的一天中的小时的值的平均值，而不是总平均值。到目前为止，我已经尝试过：

select h.hour, hh.valhh, wp.valwp from 
(select hour(from_unixtime(timestamp/1000)) as hour from data) h, 
(select hour(from_unixtime(timestamp/1000)) as hour, cast(avg(value) as integer) as valhh from data where channel_id = 1) hh, 
(select hour(from_unixtime(timestamp/1000)) as hour, cast(avg(value) as integer) as valwp from data where channel_id = 2) wp group by h.hour;

这给出了上面的结果(所有值的平均值(。

我可以通过单独查询频道来获得我想要的内容，即：

select hour(from_unixtime(timestamp/1000)) as hour, cast(avg(value) as integer) as value from data where channel_id = 1 group by hour;

给出

+------+-------+
| hour | value |
+------+-------+
|    0 |   326 |
|    1 |   145 |
|    2 |   411 |
|    3 |   142 |
|    4 |   143 |
|    5 |   171 |
|    6 |   160 |
|    7 |   487 |
|    8 |   408 |
|    9 |   186 |
|   10 |   214 |
|   11 |   199 |
|   12 |   942 |
|   13 |   521 |
|   14 |   196 |
|   15 |   247 |
|   16 |   364 |
|   17 |   252 |
|   18 |   392 |
|   19 |   916 |
|   20 |  1024 |
|   21 |  1524 |
|   22 |   561 |
|   23 |   249 |
+------+-------+

但我希望在一个结果集中有两个通道作为单独的列。我该怎么做？谢谢

Answer 1

在经历了一段陡峭的学习曲线后，我想我明白了：

select 
hh.hour, hh.valuehh, wp.valuewp 
from 
(select 
hour(from_unixtime(timestamp/1000)) as hour, 
cast(avg(value) as integer) as valuehh 
from data 
where channel_id=1 
group by hour) hh 
inner join 
(select 
hour(from_unixtime(timestamp/1000)) as hour, 
cast(avg(value) as integer) as valuewp 
from data 
where channel_id=2 
group by hour) wp 
on hh.hour = wp.hour;

给出

+------+---------+---------+
| hour | valuehh | valuewp |
+------+---------+---------+
|    0 |     300 |      38 |
|    1 |     162 |     275 |
|    2 |     338 |     668 |
|    3 |     166 |      38 |
|    4 |     152 |      38 |
|    5 |     176 |      37 |
|    6 |     174 |      38 |
|    7 |     488 |      36 |
|    8 |     553 |      37 |
|    9 |     198 |      36 |
|   10 |     214 |      38 |
|   11 |     199 |     612 |
|   12 |     942 |      40 |
|   13 |     521 |      99 |
|   14 |     187 |      38 |
|   15 |     209 |      38 |
|   16 |     287 |      39 |
|   17 |     667 |      37 |
|   18 |     615 |      39 |
|   19 |     854 |     199 |
|   20 |    1074 |      44 |
|   21 |    1470 |     178 |
|   22 |     665 |      37 |
|   23 |     235 |      38 |
+------+---------+---------+