检查时间范围重叠，守望者问题 [SQL] [英] Checking for time range overlap, the watchman problem [SQL]

问题描述

我在一个更大的问题上遇到了障碍.

I am running into a road block on a larger problem.

作为大型查询的一部分，我需要解决守夜人"问题.我有一个这样的时间表:

As part of a large query I need to solve a "night watchman" problem. I have a table with schedule shifts as such:

ID | Start          | End
1  | 2009-1-1 06:00 | 2009-1-1 14:00
2  | 2009-1-1 10:00 | 2009-1-1 18:00
3  | 2009-2-1 20:00 | 2009-2-2 04:00
4  | 2009-2-2 06:00 | 2009-2-2 14:00

作为查询的一部分，我需要确定在给定的时间范围内，房间内是否始终有至少 1 个守望者.

As part of a query, I need to determine if there is at least 1 watchman in a room at all times for a given time range.

因此，如果我指定范围 2009-1-1 06:00 到 2009-1-1 12:00，结果为真，因为移位 1 和2 合并以涵盖此时间段 - 实际上可以链接任意数量的班次以保持监视.但是，如果我检查 2009-2-1 22:00 到 2009-1-2 10:00，结果是错误的，因为在凌晨 4 点到 6 点之间有休息时间第二天早上.

So if I specified the range 2009-1-1 06:00 to 2009-1-1 12:00, the result is true, because shifts 1 and 2 merge to cover this time period - in fact any number of shifts could be chained to keep the watch up. However if I checked 2009-2-1 22:00 to 2009-1-2 10:00, the result is false because there is a break between 4 and 6am the following morning.

我想在 LINQ 中实现这个，或者作为 SQL Server (2005) 中的用户定义函数实现，因为在这两种情况下，这只是更大查询逻辑的一部分，必须运行以识别需要注意的元素.真实数据集涉及与任何给定时间段相交的大约一百个班次记录，但并不总是涵盖整个范围.

I would like to implement this either in LINQ, or as a user defined function in SQL Server (2005), as in both cases this is just a part of the logic of a larger query that must be run to identify elements that need attention. The real dataset involves about a hundred shift records intersecting any given time period, but not always covering the whole range.

我发现的最接近的是如何使用 SQL Server 对范围值进行分组对于数字范围，但这取决于在下一个范围开始之前结束的每个范围.如果我可以构建相同的手表统一视图，只考虑重叠手表，那么检查特定时间是否被覆盖将是微不足道的.统一视图如下所示:

The closest I've found is How to group ranged values using SQL Server for number ranges, however it depends on each range ending just before the next range starts. If I could construct the same unified view of the watches, only taking overlapping watches into consideration, then it would be trivial to check if a specific time was covered. A unified view would look like this:

Start          | End
2009-1-1 06:00 | 2009-1-1 18:00
2009-2-1 20:00 | 2009-2-2 04:00
2009-2-2 06:00 | 2009-2-2 14:00

注意:通过提取所有数据并在其上运行一些手动循环，这整个事情相对容易实现，但这是当前系统，并且由于班次和时间数量而相当慢必须检查的范围.

Note: This whole thing would be relatively easy to implement by just pulling all the data and running some manual loop on it, however that is the current system, and its rather slow because of the number of shifts and the number of time ranges that must be checked.

推荐答案

这是一种扁平化日期范围的方法

Here is a way to flatten date range like this

Start          | End
2009-1-1 06:00 | 2009-1-1 18:00
2009-2-1 20:00 | 2009-2-2 04:00
2009-2-2 06:00 | 2009-2-2 14:00

你必须在每一行中比较上一个和下一个日期，看看是否

You have to compare previous and next dates in each row and see whether

• 当前行的开始日期介于前一行的日期范围之间.
• 当前行的结束日期在下一行的日期范围之间.

• Current row's Start date falls between previous row's date range.
• Current row's End date falls between next row's date range.

使用上面的代码，实现UDF就这么简单.

Using above code, implementing UDF is as simple as followed.

create function fnThereIsWatchmenBetween(@from datetime, @to datetime)
returns bit
as
begin
    declare @_Result bit

    declare @FlattenedDateRange table (
        Start   datetime,
        [End]   datetime
    )

    insert  @FlattenedDateRange(Start, [End])
    select  distinct 
            Start = 
                case 
                    when Pv.Start is null then Curr.Start 
                    when Curr.Start between Pv.Start and Pv.[End] then Pv.Start
                    else Curr.Start 
                end,
            [End] = 
                case 
                    when Curr.[End] between Nx.Start and Nx.[End] then Nx.[End] 
                    else Curr.[End] 
                end
    from    shift Curr
            left join shift Pv on Pv.ID = Curr.ID - 1 --; prev
            left join shift Nx on Nx.ID = Curr.ID + 1 --; next

    if exists(  select  1
                from    FlattenedDateRange R
                where   @from between R.Start and R.[End]
                        and @to between R.Start and R.[End]) begin
        set @_Result = 1    --; There is/are watchman/men during specified date range
    end
    else begin
        set @_Result = 0    --; There is NO watchman
    end

    return @_Result
end

这篇关于检查时间范围重叠，守望者问题 [SQL]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！