一、按日期分组查询
1.可以使用substring_index截取比如2021-03-01 00:00:00空格前的数据
select SUBSTRING_INDEX(warningdate," “,1) as ‘日期’,count(*) as ‘告警次数’ from alarmmessdata where warningdate between ‘2021-03-01 00:00:00’ and ‘2021-04-28 23:59:59’ group by SUBSTRING_INDEX(warningdate,” ",1);
2.可以使用cast把比如2021-03-01 00:00:00转化成date格式2021-03-01
select CAST(warningdate AS date) as ‘日期’,count(*) as ‘告警次数’ from alarmmessdata where warningdate between ‘2021-03-01 00:00:00’ and ‘2021-04-28 23:59:59’ group by CAST(warningdate AS date);
二、求平均值、最大值、最小值、中值
#根据where条件求measured_data的平均值
select avg(measured_data) as ‘15:00的本次测量值-平均值’ from final_data where create_date between ‘2020-07-08 15:00:00’ and ‘2020-07-