Mysql根据分组的时间间隔小于30分钟并分组

可以写出从数据上可以直观看出分组的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进行分组显示

你的sql语句中 的 from 和 to 与sql保留关键词 有冲突。

select

a.train_no, a.sequ_no t_from , b.sequ_no t_to

from

(select * from aa where st_name= "xxx" ) as a ,

(select * from aa where st_name= "xx") as b

where

a.train_no = b.train_no and a.sequ_no<b.sequ_no

或者

select

a.train_no, a.sequ_no as `from` , b.sequ_no as `to`

from

(select * from aa where st_name= "xxx" ) a inner join

(select * from aa where st_name= "xx") b on a.train_no =b.train_no

where a.sequ_no<b.sequ_no

2.1 排序方式

数据量小则在内存排序, 数据量大则使用磁盘排序

内存排序 : 直接使用"快速排序"

磁盘排序 : 先将数据分块, 对每个独立的块使用"快速排序", 并将各个块的排序结果存在磁盘上, 然后将各个排好序的块进行合并(merge), 最后返回排序结果

2.2 排序算法

3. 注意点 :

---