Question

mysql按10分钟，分组统计数据，如何统计

Answer 1

2
3
4
5
6
7
8
9
10
11
12
13
14

-- time_str '2016-11-20 04:31:11'
-- date_str 20161120

select concat(left(date_format(time_str, '%y-%m-%d %h:%i'),15),'0') as time_flag, count(*) as count from `security`.`cmd_info` where `date_str`=20161120 group by time_flag order by time_flag; -- 127 rows

select round(unix_timestamp(time_str)/(10 * 60)) as timekey, count(*) from `security`.`cmd_info` where `date_str`=20161120 group by timekey order by timekey; -- 126 rows

-- 以上2个SQL语句的思路类似——使用「group by」进行区分，但是方法有所不同，前者只能针对10分钟(或1小时)级别，后者可以动态调整间隔大小，两者效率差不多，可以根据实际情况选用

select concat(date(time_str),' ',hour(time_str),':',round(minute(time_str)/10,0)*10), count(*) from `security`.`cmd_info` where `date_str`=20161120 group by date(time_str), hour(time_str), round(minute(time_str)/10,0)*10; -- 145 rows

select concat(date(time_str),' ',hour(time_str),':',floor(minute(time_str)/10)*10), count(*) from `security`.`cmd_info` where `date_str`=20161120 group by date(time_str), hour(time_str), floor(minute(time_str)/10)*10; -- 127 rows (和 date_format 那个等价)