我有一张桌子,上面有一些DATETIME
范围,比如
id | start | end
----------------------------------------------
1 | 2011-12-18 16:00:00 | 2011-12-18 17:00:00
2 | 2011-12-19 08:00:00 | 2011-12-19 10:00:00
3 | 2011-12-19 11:00:00 | 2011-12-19 13:00:00
4 | 2011-12-19 12:00:00 | 2011-12-19 14:00:00
5 | 2011-12-19 13:00:00 | 2011-12-19 15:00:00
6 | 2011-12-19 13:00:00 | 2011-12-19 14:00:00
7 | 2011-12-20 13:00:00 | 2011-12-20 14:00:00
因此,2011 年 12 月 19 日的范围如下:
8 9 10 11 12 13 14 15
<-------->
<-------->
<-------->
<-------->
<---->
目标是,在插入新记录时,找到已存在的重叠范围的最大数量:即:在插入新范围时2011-12-19 12:00:00 - 2011-12-19 15:00:00
我想收到3
,因为重叠范围的最大数量为 3,从 13:00 到 14:00。
从现在起我设法拥有了这个
select
count(*) as cnt
from
mytable as p
where
( # check if new renge overlap existings ones
(@start >= start and @start < end)
or
(@end > start and @end <= end)
)
or
( # check if existing range is included by new one
start between @start and @end
and
end between @start and @end
)
但这次回归4
因为它检测到除第一个范围之外的所有范围,但这是错误的。
我已经找到了
- 确定两个日期范围是否重叠
- 如何在 SQL 中选择重叠的日期范围
- 检查表的时间重叠?
但所有这些问题都略有不同。
我使用的是 Mysql 5.7,但如果需要的话可以升级到 8。
这个答案适用于包含窗口函数的 MySQL 8.0。该解决方案的核心是以下查询,该查询为数据中每个感兴趣的区间查找多个重叠区间:
select t2.startDt, t2.endDt, count(*) overlaps_count
from
(
select lag(t1.dt) over (order by t1.dt) startDt, t1.dt endDt
from
(
select startt dt from data
union
select endt dt from data
) t1
) t2
join data on t2.startDt < data.endt and t2.endDt > data.startt
group by t2.startDt, t2.endDt
数据库小提琴演示
一旦获得此结果(我们称之为重叠表),您就可以轻松找到输入间隔的最大值,如下所示
with Overlap as
(
-- the query above
)
select max(overlaps_count)
from Overlap
where @start < endDt and @end > startDt
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)