确定重叠 DATETIME 范围的最大数量 [英] Determine max number of overlapping DATETIME ranges

问题描述

我得到了一个包含一些 DATETIME 范围的表格，比如

I got a table with some DATETIME ranges, something like

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 天，范围跨越如下:

So for day 2011-12-19 the ranges spans like this:

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.

The goal is, when inserting new record, to find the max number of overlapping ranges already present: i.e.: when inserting the new range 2011-12-19 12:00:00 - 2011-12-19 15:00:00 i would like to receive 3, because the max number of overlapping ranges is 3, from 13:00 to 14:00.

自从现在我设法拥有这个

Since now i managed to have this

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 因为它检测到除了第一个之外的所有范围，但是是错误的.

But this return 4 because it detects all ranges except the first, but is wrong.

我已经找到了

• 确定两个日期范围是否重叠
• 如何在 SQL 中选择重叠的日期范围
• 检查表格的时间重叠?

但所有这些问题都略有不同.

But all these questions are slightly different.

我使用的是 MysQL 5.7，但如有必要，可以升级到 8.

I'm on MysQL 5.7, but upgrading to 8 is possibile if necessary.

推荐答案

此答案适用于包含窗口函数的 MySQL 8.0.该解决方案的核心将是以下查询，该查询为数据中的每个感兴趣的区间找到多个重叠区间:

This answer is for MySQL 8.0 that contains window functions. The core of the solution will be the following query that finds a number of overlapping intervals for every interesting interval in the data:

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 DEMO

一旦你得到这个结果(我们称之为重叠表)，那么你可以很容易地找到输入间隔的最大值，如下所示

Once you have this result (let call it Overlap table) then you may easily find the maximum for an input interval as follows

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

这篇关于确定重叠 DATETIME 范围的最大数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！