mysql间隔15分钟统计数量

mysql间隔15分钟统计数量

创建表：

TRUNCATE table LX_02;

添加数据：

insert into LX_02 values(1,'2020-1-14 09:07:23');
insert into LX_02 values(8,'2020-1-14 09:53:23');
insert into LX_02 values(2,'2020-1-14 09:08:23');
insert into LX_02 values(3,'2020-1-14 09:11:23');
insert into LX_02 values(4,'2020-1-14 09:14:23');
insert into LX_02 values(5,'2020-1-14 09:21:23');
insert into LX_02 values(6,'2020-1-14 09:23:23');
insert into LX_02 values(7,'2020-1-14 09:33:23');

统计间隔15分钟的数量量

select   
   CONCAT_WS(':',DATE_FORMAT( inputtime, '%y-%m-%d %h' ),
	case when FLOOR( DATE_FORMAT( inputtime, '%i' )/ 15 )* 15 =0 then '00' 
	else FLOOR( DATE_FORMAT( inputtime, '%i' )/ 15 )* 15 end,'00') as xtime,
   count(1)
from LX_02
GROUP BY CONCAT_WS(':',DATE_FORMAT( inputtime, '%y-%m-%d %h' ),
			case when FLOOR( DATE_FORMAT( inputtime, '%i' )/ 15 )* 15 =0 then '00' 
					else FLOOR( DATE_FORMAT( inputtime, '%i' )/ 15 )* 15 end,'00')
order by CONCAT_WS(':',DATE_FORMAT( inputtime, '%y-%m-%d %h' ),
			case when FLOOR( DATE_FORMAT( inputtime, '%i' )/ 15 )* 15 =0 then '00' 
					else FLOOR( DATE_FORMAT( inputtime, '%i' )/ 15 )* 15 end,'00')