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

234567891011121314 -- 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 那个等价)

可以写出从数据上可以直观看出分组的sql, 另外对于"分组的时间间隔小于30分钟"这句话可能引申出两种理解:

数据先按时间排序, 只要相邻数据DEAL_TIME在30分钟以内则归组, 比如2019-06-06 13:00:00, 2019-06-06 13:05:00和2019-06-06 13:32:00可以归为一组

数据先按时间排序, 对于理解1的3条数据, 此时2019-06-06 13:32:00和2019-06-06 13:00:00的间隔超过30分钟, 需要另起一组

对于上述两个理解我这边都给出例子, 原理和MySql的rownumber实现有些类似, 排序后加上条件滚动计算, 方案有不足的地方, 1是多一层排序嵌套子查询, 2是结果如果不再嵌套会多出计算过程列

以下为代码:

-- by sleest 2019/06/06 临近分组

-- 制造临时表数据用于验证

DROP TEMPORARY TABLE IF EXISTS TMP_DEAL_TIME

CREATE TEMPORARY TABLE TMP_DEAL_TIME(DEAL_TIME DATETIME) AS

SELECT DATE_ADD(CURDATE(), INTERVAL 600 * RAND() MINUTE) AS DEAL_TIME

   FROM (SELECT 1 UNION ALL SELECT 2 UNION SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) A,

        (SELECT 1 UNION ALL SELECT 2 UNION SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) B

-- 查看临时表数据

SELECT DEAL_TIME FROM TMP_DEAL_TIME ORDER BY DEAL_TIME

-- 理解1: 只要相邻时间为指定分钟数以内则聚合

SELECT DEAL_TIME, 

       CASE WHEN @lastDealTime IS NULL THEN @minLevel:=1

            WHEN DEAL_TIME > DATE_ADD(@lastDealTime, INTERVAL 30 MINUTE) 

              THEN @minLevel:=@minLevel+1 END AS COMPUT_CONDITION,

              

       @lastDealTime:=DEAL_TIME AS LAST_DEAL_TIME,

       @minLevel AS GROUP_LEVEL

  FROM (SELECT DEAL_TIME FROM TMP_DEAL_TIME ORDER BY DEAL_TIME) E,

       (SELECT @lastDealTime:=NULL) F,

       (SELECT @minLevel:=0) G

-- 理解2: 只和开始聚合的第一条时间比较, 只有当该条数据处在聚合第一条指定分钟数以内才归组

SELECT DEAL_TIME, 

       CASE WHEN @lastDealTime IS NULL THEN @minLevel:=1

            WHEN DEAL_TIME > DATE_ADD(@lastDealTime, INTERVAL 30 MINUTE) 

              THEN @minLevel:=@minLevel+1 END AS COMPUT_CONDITION,

              

       CASE WHEN @lastDealTime IS NULL THEN @lastDealTime:=DEAL_TIME

            WHEN DEAL_TIME > DATE_ADD(@lastDealTime, INTERVAL 30 MINUTE)

              THEN @lastDealTime:=DEAL_TIME END AS LAST_DEAL_TIME,

       @minLevel AS GROUP_LEVEL

  FROM (SELECT DEAL_TIME FROM TMP_DEAL_TIME ORDER BY DEAL_TIME) E,

       (SELECT @lastDealTime:=NULL) F,

       (SELECT @minLevel:=0) G

对于理解1的执行结果如下:

对于理解2的执行结果如下:

后续可以根据结果中的GROUP_LEVEL进行分组显示

---