如何对不同的重叠时间段求和?

Question

我试图创建一个UDF，它接受一个日期范围，并询问一个表以返回该表中记录的唯一重叠之和。

CREATE TABLE dbo.Shifts (
Id INT IDENTITY(1,1) NOT NULL,
StartTime DATETIME2(0) NOT NULL,
EndTime DATETIME2(0) NOT NULL
CONSTRAINT [PK_Shifts] PRIMARY KEY CLUSTERED ([Id] ASC)
)

Problem Statement: Given @Start and @End parameters,
write a UDF that returns the percentage of unique temporal overlap by
records in the dbo.Shifts table
Round start and stop times to the nearest 15-minutes
E.g.,
Timeline     : <---------------------------------------------------------->
Input Params :      @Start-----------------------------------@End
Input Params :      !-------------------------------------------!
Overlapping Shifts
Shift 1      :   |----------------|
Shift 2      :        |----------------|
Shift 3      :                                             |--------------|

Unique overlap
Input Params :      !-------------------------------------------!
Shifts       :      |--------------------|                   |--|
% overlap    :    ~ 58%

Answer 1

这有两个组成部分。更有趣的是"缺口与孤岛"问题。第二个是重叠问题。您希望将缺口和孤岛限制为跨越范围的数据，然后将它们组合起来。因此，要获得相关分组:

select min(starttime) as island_start, max(endtime) as island_end
from (select s.*,
sum(case when prev_endtime >= starttime then 0 else 1 end) over (order by starttime) as grp
from (select t.*,
max(endtime) over (order by starttime rows between unbounded preceding and 1 preceding) as prev_endtime
from shifts s
where endtime > @start or
starttime < @end
) s
) s
group by grp;

这个最里面的子查询计算所有移位的前一个末端。如果没有重叠，那么这一行就是一个岛的开始。然后，累积和标识岛上的所有行。外部查询聚合到每个岛的一行。

然后，你想要与这个和你关心的区间重叠:

with i as (
select min(starttime) as island_start, max(endtime) as island_end
from (select s.*,
sum(case when prev_endtime >= starttime then 0 else 1 end) over (order by starttime) as grp
from (select s.*,
max(endtime) over (order by starttime rows between unbounded preceding and 1 preceding) as prev_endtime
from shifts s
where endtime > @start or
starttime < @end
) s
) s
group by grp
)
select sum(datediff(second, interval_start, interval_end)) as shift_seconds,
datediff(second(second, @start, @end) as overall_seconds
from (select i.*,
(case when island_end > @endtime then @end else island_end end) as interval_end,
(case when island_start < @start then @start else island_start end) as interval_start
from i
) i;

您想要的比率是两个值的比率。