mysql - 如何从MySQL中获取每分钟两次的平均值

Question

你好，我的 mysql 表看起来像这样:

select 
distinct(TimeStamp) , 
TP_bmp183 as "t:emperature_;_p:reassure" 
from 
gadbdfm 
where 
TimeStamp 
BETWEEN  '2016-12-10 08:10%' AND '2016-12-10 08:15%' ;
    +----------------------------+----------------------------+
    | TimeStamp                  | t:emperature_;_p:reassure  |
    +----------------------------+----------------------------+
    | 2016-12-10 08:10:15.087235 | t: -0.700000;p: 998.470000 |
    | 2016-12-10 08:10:52.553992 | t: -0.500000;p: 998.460000 |
    | 2016-12-10 08:11:44.747368 | t: 0.100000;p: 998.520000  |
    | 2016-12-10 08:12:14.647513 | t: 0.200000;p: 998.450000  |
    | 2016-12-10 08:12:47.107094 | t: 0.200000;p: 998.410000  |
    | 2016-12-10 08:13:14.439774 | t: 0.200000;p: 998.430000  |
    | 2016-12-10 08:13:44.367472 | t: 0.000000;p: 998.440000  |
    | 2016-12-10 08:14:14.254476 | t: -0.300000;p: 998.550000 |
    | 2016-12-10 08:14:44.171212 | t: -0.600000;p: 998.420000 |
    +----------------------------+----------------------------+

因此可以看出我采用了两个值每分钟两次。我确实需要为一分钟条目设置一个值 2016-12-10 08:10 这意味着 t: -0.600000;p: 998.465000。

• 温度:-0.70000 和 -0.500000 为 -0.60000
• 压力:998.470000 和 998.460000 是 998.465000

Answer 1

select 
    date_format(TimeStamp, '%Y-%m-%d %H:%i') timeStampRoundedToMinute
    , concat(TP_bmp183) as debugPrint 
    , avg(substring_index(substring_index(TP_bmp183, ';', 1),  ':', -1)) avgTemp
    , avg(substring_index(substring_index(TP_bmp183, ';', -1), ':', -1)) avgPressure
from 
    gadbdfm 
where 
    TimeStamp BETWEEN  '2016-12-10 08:10%' AND '2016-12-10 08:15%' 
group by date_format(TimeStamp, '%Y-%m-%d %H:%i');