sql - 计算多个用户之间 SQL 中的重叠时间间隔

Question

在网上花了很长时间试图找到我的问题的答案后，我决定将其张贴在这里。

我有一个表格，其中包含某些资源不可用的时间间隔。我想提取所有资源不可用的所有时间间隔。

在下面的示例中，我会:开始:2018-11-16 12:30:00，结束:2018-11-16 12:45:00

Start                   End                     Resource
-------------------     -------------------     ----------  
2018-11-15 12:00:00     2018-11-15 13:00:00     resource A
2018-11-15 12:00:00     2018-11-15 13:00:00     resource B
2018-11-15 12:30:00     2018-11-15 14:00:00     resource C
2018-11-15 12:00:00     2018-11-15 12:45:00     resource D
2018-11-18 12:00:00     2018-11-18 13:00:00     resource A
2018-11-19 11:40:00     2018-11-19 12:20:00     resource B
2018-11-15 16:00:00     2018-11-15 17:00:00     resource D

有人知道吗？

提前致谢。

Answer 1

如果您知道资源数量(或可以计算)并且资源没有重叠的开始/结束范围(即您获得了干净的数据):

select Start, "End"
from
 ( -- calculate a Cumulative Sum to get the count of resources unavailable at any point in time
   select Start,
      lead(Start) over (order by Start,  x) as "End", 
      sum(x) over (order by Start,  x rows unbounded preceding) as cnt
   from
    ( -- combine starts & ends into a single table and assigne +/-1 to each row
      select Start,  1 as x
      from tab
      union all 
      select "End", -1 as x
      from tab
    ) as dt
 ) as dt
where cnt = 4 -- find the row where all resources are unavailable
--  where cnt = (select count(*) from resources)