我有一个简单的表,其中有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,4,5,6
的最大end_date
和最小start_date
,并加上1和7的日期差。
此时:我发现了如何估计月份的日期差异:
PERIOD_DIFF( DATE_FORMAT(end_date, '%Y%m') , DATE_FORMAT(start_date, '%Y%m') )
我知道这里的想法是:
- 了解两个日期是否重叠。如果是,则相应地合并日期(如果需要,调整结束日期和开始日期)
- 循环浏览所有日期,以月为单位估计日期差异,求和并返回最终结果
我一直在寻找类似的问题,但无法解决和问题,如果你能帮助我,那就太好了。我知道使用一些编程语言并在那里估计它是可能的,但我想用MySQL查询来写它。
感谢
这一切都很繁忙,但应该能满足您的需求:
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是灵活的,因为我假设可能有完全被前一个周期包围的周期。
下一个包装查询从每个组中提取整个范围。
外部查询汇总每个组的全月差异。PERIOD_DIFF将所有组差异四舍五入到最接近的整月。
不幸的是,我无法测试这一点,因为SQLFiddle已经死在我身上了。
如果您需要包括重叠在内的多条记录的总周期,那么只需将每条记录的周期差异相加即可:
SELECT SUM(PERIOD_DIFF( DATE_FORMAT(end_date, '%Y%m') , DATE_FORMAT(start_date, '%Y%m') )) AS total_periods
FROM table WHERE ...
我用自己的方式在Stackoverflow上检查其他答案,它应该可以工作:
select sum(months)
from (select t.*,
@time := if(@sum = 0, 0, period_diff(date_format(start_date, '%Y%m'), date_format(@prevtime, '%Y%m'))) as months,
@prevtime := start_date,
@sum := @sum + isstart
from ((select start_date, 1 as isstart
from position t
) union all
(select end_date, -1
from position t
)
) t cross join
(select @sum := 0, @time := 0, @prevtime := 0) vars
order by 1, 2
) t