MySQL合并具有重叠日期跨度的表行 [英] MySQL consolodating table rows with overlapping date spans

问题描述

我有一个具有重叠可用日期的临时表.这些日期可以以任意组合的方式重叠，因为它们适用于多个可用房间.

I have a temporary table with overlapping available dates. These dates can be overlapping in any combination of ways, as they are for multiple available rooms.

我想创建一个查询，返回一组合并的可用日期.(开盘日期为可用日期；关闭日期不可用.)

I would like to create a query that returns a consolidated set of available dates. (The opens date is an available day; closes is unavailable.)

比如下面的数据

+------------+------------+
|   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 |
+------------+------------+

感谢您的帮助！

推荐答案

一种方法是使用相关子查询:

One way of doing it is by the use of correlated subqueries:

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 日期，而结束日期是最大 结束日期.

return all overlapping records related to the current record. Performing aggregation one these records we can find the start / end dates of each interval: the start date of the interval is the minimum opens date between all overlapping records, whereas the end date is the maximum closes date.

此处演示

上述解决方案不适用于如下一组间隔:

The above solution won't work with a set of intervals like the following:

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

记录号2、处理时，会产生有缺陷的开始/结束间隔.

Record no. 2, when processed, will produce a flawed start/end interval.

这是使用变量的解决方案:

Here's a solution using variables:

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

这个想法是从最左边的 opens/closes 间隔开始.变量 @start、@end 用于沿区间链向下传播增量扩展(因为正在处理新的重叠行)合并区间.一旦遇到非重叠区间，[@start - @end] 被初始化以匹配这个新区间，并且 grp 加一.

The idea is to start from left-most opens/closes interval. Variables @start, @end are used to propagate the incrementally expanding (as new overlapping rows are being processed) consolidated interval down the interval chain. Once a non-overlapping interval is encountered, [@start - @end] is initialized so as to match this new interval and grp is incremented by one.

此处演示

这篇关于MySQL合并具有重叠日期跨度的表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！