mysql 实现每半个小时/每小时/每2小时获得一次均值数据

每半小时

select avg(aa),addtime from (
select pm25 , DATE_FORMAT( concat(date(addtime),' ',hour(addtime),':',floor( minute(addtime)/30 )*30) ,'%Y-%m-%d %H:%i')  as addtime from bb 
 where addtime >= '2019-9-20 06:00:00' and addtime <= '2019-9-20 12:00:00') a
group by DATE_FORMAT( addtime ,'%Y-%m-%d %H:%i');

结果展示：

 

每一小时获得一次均值

select avg(aa), DATE_FORMAT( addtime ,'%Y-%m-%d %H')  as addtime from bb where addtime >= '2019-9-20 06:00:00' and addtime <= '2019-9-20 12:00:00'  group by DATE_FORMAT( addtime ,'%Y-%m-%d %H')

结果展示：

每2小时获得一次均值

select avg(aa),addtime from (
select pm25 , DATE_FORMAT( concat(date(addtime),' ',floor( hour(addtime)/2 )*2),'%Y-%m-%d %H')  as addtime from bb
 where addtime >= '2019-9-20 06:00:00' and addtime <= '2019-9-20 12:00:00') a
group by DATE_FORMAT( addtime ,'%Y-%m-%d %H')

结果展示：