我有一张包含重叠时间段的表格。我想对连续的重叠时间事件进行分组(即没有时间间隔分开)。

ID  StartDate            EndDate
1   2013-01-30  2013-01-31
2   2013-01-31  2013-01-31
3   2013-01-29  2013-01-31
4   2013-01-25  2013-01-28
5   2013-01-29  2013-01-30
6   2013-02-01  2013-02-01
7   2013-01-31  2013-02-02
8   2013-02-04  2013-02-05
9   2013-02-05  2013-02-06
10  2013-02-08  2013-02-09

       01-24   01-25   01-26   01-27   01-28   01-29   01-30    01-31   02-01   02-02   02-03   02-04   02-05   02-06   02-07   02-08   02-09  
1                                                          --------------    
2                                                                   -----                                                                                                  
3                                                   ---------------------
4                  -----------------------------
5                                                   ------------ 
6                                                                           -----                            
7                                                                    --------------------                             
8                                                                                                    -------------                                              
9                                                                                                             -------------                                                       
10                                                                                                                                   --------------                                                                    

因此我想有以下四个时间组:

第 1 组(ID:1、2、3、5、6、7)

第 2 组(Id:4)

第 3 组(ID:8、9)

第 4 组:(编号:10)

在 Sql 中有没有一种简单的方法可以做到这一点？这是我的示例表的创建 sql:

DROP TABLE IF EXISTS tb_data_log;
CREATE TABLE tb_data_log (
  `event_id` int(10) unsigned NOT NULL,
  `startdate` date DEFAULT NULL,
  `enddate` date DEFAULT NULL
);

INSERT INTO tb_data_log VALUES (1, '2013-01-30', '2013-01-31');
INSERT INTO tb_data_log VALUES (2, '2013-01-31', '2013-01-31');
INSERT INTO tb_data_log VALUES (3, '2013-01-29', '2013-01-31');
INSERT INTO tb_data_log VALUES (4, '2013-01-25', '2013-01-28');
INSERT INTO tb_data_log VALUES (5, '2013-01-29', '2013-01-30');
INSERT INTO tb_data_log VALUES (6, '2013-02-01', '2013-02-01');
INSERT INTO tb_data_log VALUES (7, '2013-01-31', '2013-02-02');
INSERT INTO tb_data_log VALUES (8, '2013-02-04', '2013-02-05');
INSERT INTO tb_data_log VALUES (9, '2013-02-05', '2013-02-06');
INSERT INTO tb_data_log VALUES (10, '2013-02-08', '2013-02-09');

编辑#1:

看起来问题有点难以理解，这里是期望的输出:

GroupID StartDate   EndDate     Overlapped Id
      1 2013-01-29  2013-02-02  1, 2, 3, 5, 6, 7
      2 2013-01-25  2013-01-28  4      
      3 2013-02-04  2013-02-06  8,9
      4 2013-02-08  2013-02-09  10          

最佳答案

这是一个解决方案。它应该可以工作并且不使用存储过程:

select per_start,per_end,group_concat(contained.event_id) from tb_data_log contained,(
select distinct start.startdate as per_start,
finish.enddate as per_end
 from tb_data_log start join tb_data_log finish
on start.startdate <= finish.enddate -- first find all possible periods
where not exists (-- make sure there are two events in the period which do not overlap and between whom there is no event.
select * from tb_data_log a, tb_data_log b where 
a.enddate < b.startdate and
a.startdate>=start.startdate and
b.enddate<=finish.enddate and not exists 
(
select * from tb_data_log inside where
inside.event_id <> a.event_id
and inside.event_id<> b.event_id
and inside.enddate > a.enddate and inside.startdate < b.startdate
)
)
and not exists (-- make sure there is no longer period
select * from tb_data_log later where later.startdate<=finish.enddate and later.enddate >finish.enddate
)
and not exists (-- make sure there is no longer period
select * from tb_data_log earlier where earlier.startdate<start.startdate and earlier.enddate >=start.startdate
)
) periods where contained.enddate<=per_end and contained.startdate>=per_start
group by per_start,per_end

想法是首先通过将表与自身连接起来找到所有可能的周期。然后对于每个周期 P，确保没有周期 A、B 对，以便 A 在 B 之前(不重叠)，两者都包含在 P 中，并且它们之间没有事件。还要确保这不是可能的最长期限。

这里是我之前发的解决方案，比较差。留作引用

这可能不是很有效。我使用了从这里选择的答案: How to get list of dates between two dates in mysql select query 所以请注意，这个查询将在 300 年后停止工作!!!

select per_start,per_end,group_concat(contained.event_id) from tb_data_log contained,(
select distinct start.startdate as per_start,
finish.enddate as per_end
 from tb_data_log start, tb_data_log finish
where start.startdate <= finish.enddate -- first find all possible periods
and not exists (-- make sure there are no two consecutive days that are not contained in some      event period.
select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) day1, adddate('1970-01-     01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0+1) day2 from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union   select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union  select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union   select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union  select 6 union select 7 union select 8 union select 9) t4) v
where day1 between start.startdate and finish.enddate and day2 between start.startdate and  finish.enddate 
and not exists (
select * from tb_data_log where tb_data_log.startdate <= cast(day1 as date) and    tb_data_log.enddate >= cast(day2 as date)
)
)
and not exists (-- make sure there is no longer period
select * from tb_data_log later where later.startdate<=finish.enddate and later.enddate    >finish.enddate
)
and not exists (-- make sure there is no longer period
select * from tb_data_log earlier where earlier.startdate<start.startdate and earlier.enddate    >=start.startdate
)
) periods where contained.enddate<=per_end and contained.startdate>=per_start
group by per_start,per_end

想法是首先通过将表与自身连接起来找到所有可能的周期。然后对于每个周期，确保没有包含在该周期中但未被表中的某个事件周期覆盖的连续几天。还要确保这不是可能的最长期限。

我认为这个查询的性能可以有所提高。

关于mysql - 如何在 MySQL 中获取一系列重叠事件，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/25865144/