日期范围的总和，不计算MySQL中的重叠 [英] Summing range of dates without counting overlaps in mysql

问题描述

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

I have simple table with start_date and end_date columns in it. These date values may overlap

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,4,5,6的MAX end_date和MIN start_date并加上1和7的日期差.

Problem is to find sum in months. Example: id: 2,3,4,5,6 overlap so idea is to take MAX end_date and MIN start_date of 2,3,4,5,6 and add date difference of 1, and of 7.

这时:我已经找到了如何估算月份中的日期差异:

At this time: I've found how to estimate date difference in months:

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

我知道这里的想法是:

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

我一直在寻找类似的问题，但无法解决和提出问题，如果您能帮助我，那将是很好的.我知道可以使用某种编程语言并在那里进行估算，但想使用MySQL查询编写它.

I've been looking for similar questions and couldn't resolve and issue, would be nice if you could help me. I know it is possible to do using some programming language and estimate it there, but wanted to write it using MySQL query.

谢谢

推荐答案

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

This is hectic as anything but should get you what you need:

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

最里面的查询在适当的情况下将您的时间段分成重叠的组，并延长了end_date.我认为end_date会弯曲，因为我认为前者可能完全封闭了一段时间.

The inner-most query groups together your periods in overlapping groups stretching the end_date where appropriate. The end_date flexes as I assumed that there could be periods completely enclosed by the previous.

下一个换行查询从每个组中提取全部范围.

The next wrapping query extracts the full range from each group.

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

The outer query sums up the full month diffs for each group. All group diffs are rounded down to the nearest full month by PERIOD_DIFF.

不幸的是，由于SQLFiddle对我死亡，我无法对此进行测试.

Unfortunately I couldn't test this as SQLFiddle has died on me.

这篇关于日期范围的总和，不计算MySQL中的重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！