mysql - 求和日期范围而不计算mysql中的重叠

Question

我有一个简单的表格，其中包含 start_date 和 end_date 列。这些日期值可能重叠

id    start_date    end_date
1     2011-01-01    2012-04-01
2     2012-05-01    2013-10-01
3     2013-09-01    2014-09-01
4     2013-10-01    2014-08-01
5     2013-12-01    2014-11-01
6     2013-09-01    2014-09-01
7     2015-01-01    2015-11-01

问题是求以月为单位的总和。例子:id: 2,3,4,5,6 重叠，因此想法是取 2,3 的 MAX end_date 和 MIN start_date ,4,5,6 并添加日期差 1 和 7。

此时:我已经找到了如何估计月份的日期差异:

PERIOD_DIFF( DATE_FORMAT(end_date, '%Y%m') , DATE_FORMAT(start_date, '%Y%m') )

我知道这里的想法是:

1. 了解两个日期是否重叠。如果是，则相应地合并日期(如果需要，调整结束和开始日期)
2. 循环遍历所有日期，估计日期差异(以月为单位)，求和并返回最终结果。

我一直在寻找类似的问题，但无法解决和问题，如果您能帮助我，那就太好了。我知道可以使用某种编程语言并在那里进行估计，但想使用 MySQL 查询来编写它。

谢谢

Answer 1

这很忙碌，但应该可以满足您的需求:

SELECT SUM(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM a.end_date), EXTRACT(YEAR_MONTH FROM a.start_date))) months
  FROM (
    SELECT MIN(g.start_date) start_date, MAX(g.end_date) end_date 
      FROM (
        SELECT @group_id := @group_id + (@end_date IS NULL OR o.start_date > @end_date) group_id,
               start_date,
               @end_date := DATE(CASE 
                 WHEN (@end_date IS NULL OR o.start_date > @end_date) THEN o.end_date
                 ELSE GREATEST(o.end_date, @end_date)
               END) end_date  
          FROM overlap o
          JOIN (SELECT @group_id := 0, @end_date := NULL) init
      ORDER BY o.start_date ASC  
            ) g
  GROUP BY  g.group_id  
        ) a

最里面的查询将您的经期分组到重叠的组中，在适当的情况下拉伸(stretch) end_date。 end_date 会发生变化，因为我假设可能存在完全被前一个日期包围的时期。

下一个包装查询从每个组中提取完整范围。

外部查询汇总了每个组的整月差异。所有组差异均按 PERIOD_DIFF 向下舍入到最接近的完整月份。

不幸的是，我无法测试这个，因为 SQLFiddle 已经死了。