确定最大重叠日期时间范围数

envsm3lx 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(375)

我有一张table 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中选择重叠的日期范围
检查表是否有时间重叠？
但所有这些问题都略有不同。
我使用的是MySQL5.7，但是如果需要的话可以升级到8。

sql mysql DateTime range overlap

来源：https://stackoverflow.com/questions/53720067/determine-max-number-of-overlapping-datetime-ranges

2条答案

按热度按时间

lskq00tm1#

这个答案适用于包含窗口函数的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

dbfiddle演示
一旦有了这个结果（称之为重叠表），就可以很容易地找到输入间隔的最大值，如下所示

with Overlap as
(
   -- the query above
)
select max(overlaps_count)
from Overlap 
where @start < endDt and @end > startDt

赞(0）回复(0）举报 2021-06-18

wn9m85ua2#

考虑到起点和终点永远不会相同：

select
    count(*) as cnt
from
    mytable as p
where
    # check if new renge overlap existings ones
        (@start < end and @end > start);

赞(0）回复(0）举报 2021-06-17

我来回答

确定最大重叠日期时间范围数

2条答案

相关问题

热门标签

最新问答