MySQL合并具有重叠日期范围的表行

Question

我有一个临时表格，其中包含重叠的可用日期。这些日期可以以任何组合方式重叠，因为它们适用于多个可用房间。

我想创建一个查询，返回一个汇总的可用日期集合。（开放日期是可用日；关闭日期是不可用的。）

例如，以下数据：

+------------+------------+
|   opens    |   closes   |
+------------+------------+
| 2015-12-03 | 2015-12-05 |
| 2016-01-08 | 2016-01-15 |
| 2016-02-21 | 2016-02-27 |
| 2016-03-13 | 2016-03-24 |
| 2016-03-31 | 2016-04-02 |
| 2016-04-06 | 2016-04-15 |
| 2016-04-21 | 2016-12-03 |
| 2015-12-03 | 2015-12-09 |
| 2016-01-03 | 2016-01-06 |
| 2016-01-16 | 2016-02-08 |
| 2016-03-01 | 2016-03-06 |
| 2016-03-10 | 2016-12-03 |
+------------+------------+

应该返回：

+------------+------------+
|   opens    |   closes   |
+------------+------------+
| 2015-12-03 | 2015-12-09 |
| 2016-01-03 | 2016-01-06 |
| 2016-01-08 | 2016-01-15 |
| 2016-01-16 | 2016-02-08 |
| 2016-02-21 | 2016-02-27 |
| 2016-03-01 | 2016-03-06 |
| 2016-03-10 | 2016-12-03 |
+------------+------------+

谢谢你的帮助！

- Mark Goldberg

---

Giorgos是该网站上最优秀的SQL专家之一。 - Tim Biegeleisen

Answer 1

一种实现方式是使用相关子查询：

SELECT DISTINCT
       (SELECT MIN(opens)
       FROM mytable AS t2
       WHERE t2.opens <= t1.closes AND t2.closes >= t1.opens) AS start,
       (SELECT MAX(closes)
       FROM mytable AS t2
       WHERE t2.opens <= t1.closes AND t2.closes >= t1.opens) AS end       
FROM mytable AS t1
ORDER BY opens

相关子查询的WHERE谓词：

t2.opens <= t1.closes AND t2.closes >= t1.opens

返回与当前记录有重叠的所有记录。对这些记录进行聚合，我们可以找到每个区间的起始/结束日期：区间的起始日期是所有重叠记录中opens日期的最小值，而结束日期是closes日期的最大值。

此处有演示

编辑：

上述解决方案无法处理以下一组时间间隔：

1. |-----------|
2. |----|
3.           |-----|

第二条记录在处理时会产生错误的起始/结束时间间隔。

以下是使用变量的解决方案：

SELECT MIN(start) AS start, MAX(end) AS end
FROM (
  SELECT @grp := IF(@start = '1900-01-01' OR 
                   (opens <= @end AND closes >= @start), @grp, @grp+1) AS grp,        
         @start := IF(@start = '1900-01-01', opens, 
                      IF(opens <= @end AND closes >= @start, 
                         IF (@start < opens, @start, opens), opens)) AS start,
         @end := IF(@end = '1900-01-01', closes, 
                    IF (opens <= @end AND closes >= @start, 
                      IF (@end > closes, @end, closes), closes)) AS end                 
  FROM mytable
  CROSS JOIN (SELECT @grp := 1, @start := '1900-01-01', @end := '1900-01-01') AS vars
  ORDER BY opens, DATEDIFF(closes, opens) DESC) AS t
GROUP BY grp

这段内容涉及IT技术。其意思是从最左边的“开/关”间隔开始。变量“@start”和“@end”用于将逐步扩展（处理新的重叠行时）的合并间隔向下传播到间隔链中。一旦遇到不重叠的间隔，就会初始化“[@start - @end]”，以匹配这个新的间隔，并将“grp”增加一。请参考此处进行演示。

- Giorgos Betsos

---

@Mark Giorgos的查询应该可以很好地适用于您的数据集。只需根据需要更改列/表名称即可。 - Tim Biegeleisen

@TimBiegeleisen，事实证明我的查询有缺陷。它不能在任何重叠日期间隔的组合上工作。例如，它在昨天Mark提出的问题的示例数据中无法工作。 - Giorgos Betsos

@GiorgosBetsos 这很讽刺，因为我对他以前的数据集的查询似乎可以使用该数据集，但它不能处理这个问题的数据。是的，重叠的日期间隔很棘手。 - Tim Biegeleisen

@TimBiegeleisen 这是由于输入数据的高度不规则性造成的。一个通用的解决方案，处理所有可能的情况，肯定比上面的查询要复杂得多！ - Giorgos Betsos

我想出了一种替代方案，可以适用于任何输入数据集。它实际上可以在你提出的另一个问题的数据上运行。 - Giorgos Betsos