检查时间范围重叠，守卫问题[SQL]

小编典典

检查时间范围重叠，守卫问题[SQL]

algorithm

我遇到了更大的难题。

作为大型查询的一部分，我需要解决“守夜人”问题。我有一张表，上面有时间表的变化：

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个值班员。

因此，如果我将范围指定2009-1-106:00为2009-1-112:00，则结果为true，因为班次1和班次2合并以覆盖该时间段-实际上，可以将任意数量的班次链接起来以保持手表运转。但是，如果我检查2009-2-1 22:00到2009-1-210:00，则结果为假，因为第二天早上4点至6点之间有休息。

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

我发现的最接近的方法是如何使用SQLServer为数字范围[分组范围值，但是这取决于在下一个范围开始之前结束的每个范围。如果我可以构造手表的相同统一视图，仅考虑重叠的手表，那么检查是否涵盖了特定时间将是微不足道的。统一视图如下所示：

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

注意：只需拉出所有数据并对其运行一些手动循环，这相对容易实现，但是这是当前系统，并且由于移位次数和所需的时间范围数而相当慢被检查。

阅读 274

2020-07-28

共1个答案

小编典典

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

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

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

当前行的开始日期介于上一行的日期范围之间。
当前行的结束日期介于下一行的日期范围之间。

替代文字

使用以上代码，实现UDF如下所示。

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

2020-07-28