sql - 获取日期时间范围之间的类可用时间

Question

我正在使用 Oracle 11g，我遇到了这个问题。我还没有想出任何想法来解决它。

我有一张教室被占用的 table 。我需要找到的是日期时间范围之间的可用时间。例如，我有 A、B 和 C 房间，占用教室的表格是这样的:

Classroom        start                 end  
   A         10/10/2013 10:00      10/10/2013 11:30  
   B         10/10/2013 09:15      10/10/2013 10:45  
   B         10/10/2013 14:30      10/10/2013 16:00  

我需要得到的是这样的:
with date time range between '10/10/2013 07:00' and '10/10/2013 21:15'

Classroom    avalailable_from        available_to  
   A         10/10/2013 07:00      10/10/2013 10:00  
   A         10/10/2013 11:30      10/10/2013 21:15  
   B         10/10/2013 07:00      10/10/2013 09:15  
   B         10/10/2013 10:45      10/10/2013 14:30  
   B         10/10/2013 16:00      10/10/2013 21:15  
   C         10/10/2013 07:00      10/10/2013 21:15  

有没有办法用 sql 或 pl/sql 来完成？

Answer 1

我正在寻找一个至少在概念上与 Wernfried 相似的解决方案，但我认为它的不同之处也足以发布。开始是相同的想法，首先生成可能的时间段，并假设您正在查看 15 分钟的窗口:我正在使用 CTE，因为我认为它们比嵌套选择更清晰，尤其是在这么多级别的情况下。

with date_time_range as (
  select to_date('10/10/2013 07:00', 'DD/MM/YYYY HH24:MI') as date_start,
    to_date('10/10/2013 21:15', 'DD/MM/YYYY HH24:MI') as date_end
  from dual
),
time_slots as (
  select level as slot_num,
    dtr.date_start + (level - 1) * interval '15' minute as slot_start,
    dtr.date_start + level * interval '15' minute as slot_end
  from date_time_range dtr
  connect by level <= (dtr.date_end - dtr.date_start) * (24 * 4) -- 15-minutes
)
select * from time_slots;

这为您提供了您指定的开始日期和结束日期之间的 57 个 15 分钟时段。 date_time_range 的 CTE不是绝对必要的，您可以将日期直接放入 time_slots条件，但是您必须重复它们，然后引入可能的故障点(并且意味着从 JDBC 或任何地方多次绑定(bind)相同的值)。

然后可以将这些插槽交叉连接到教室列表中，我假设它已经在另一个表中，它为您提供了 171 (3x57) 个组合；并且可以将这些与现有预订进行比较 - 一旦这些预订被淘汰，您将剩下 153 个没有预订的 15 分钟时段。

with date_time_range as (...),
time_slots as (...),
free_slots as (
  select c.classroom, ts.slot_num, ts.slot_start, ts.slot_end,
    lag(ts.slot_end) over (partition by c.classroom order by ts.slot_num)
      as lag_end,
    lead(ts.slot_start) over (partition by c.classroom order by ts.slot_num)
      as lead_start
  from time_slots ts
  cross join classrooms c
  left join occupied_classrooms oc on oc.classroom = c.classroom
    and not (oc.occupied_end <= ts.slot_start 
      or oc.occupied_start >= ts.slot_end)
  where oc.classroom is null
)
select * from free_slots;

但是你必须将它们折叠成连续的范围。有多种方法可以做到这一点；在这里，我正在查看前一行和下一行以确定特定值是否是范围的边缘:

with date_time_range as (...),
time_slots as (...),
free_slots as (...),
free_slots_extended as (
  select fs.classroom, fs.slot_num,
    case when fs.lag_end is null or fs.lag_end != fs.slot_start
      then fs.slot_start end as slot_start,
    case when fs.lead_start is null or fs.lead_start != fs.slot_end
      then fs.slot_end end as slot_end
  from free_slots fs
)
select * from free_slots_extended
where (fse.slot_start is not null or fse.slot_end is not null);

现在我们减少到 12 行。 (外部 where 子句消除了上一步中 153 个插槽中的所有 141 个中等范围，因为我们只关心边缘):

CLASSROOM   SLOT_NUM SLOT_START       SLOT_END       
--------- ---------- ---------------- ----------------
A                  1 2013-10-10 07:00                  
A                 12                  2013-10-10 10:00 
A                 19 2013-10-10 11:30                  
A                 57                  2013-10-10 21:15 
B                  1 2013-10-10 07:00                  
B                  9                  2013-10-10 09:15 
B                 16 2013-10-10 10:45                  
B                 30                  2013-10-10 14:30 
B                 37 2013-10-10 16:00                  
B                 57                  2013-10-10 21:15 
C                  1 2013-10-10 07:00                  
C                 57                  2013-10-10 21:15 

所以那些代表边缘，但在不同的行上，最后一步将它们组合起来:

...
select distinct fse.classroom,
  nvl(fse.slot_start, lag(fse.slot_start)
    over (partition by fse.classroom order by fse.slot_num)) as slot_start,
  nvl(fse.slot_end, lead(fse.slot_end)
    over (partition by fse.classroom order by fse.slot_num)) as slot_end
from free_slots_extended fse
where (fse.slot_start is not null or fse.slot_end is not null)

或者把所有这些放在一起:

with date_time_range as (
  select to_date('10/10/2013 07:00', 'DD/MM/YYYY HH24:MI') as date_start,
    to_date('10/10/2013 21:15', 'DD/MM/YYYY HH24:MI') as date_end
  from dual
),
time_slots as (
  select level as slot_num,
    dtr.date_start + (level - 1) * interval '15' minute as slot_start,
    dtr.date_start + level * interval '15' minute as slot_end
  from date_time_range dtr
  connect by level <= (dtr.date_end - dtr.date_start) * (24 * 4) -- 15-minutes
),
free_slots as (
  select c.classroom, ts.slot_num, ts.slot_start, ts.slot_end,
    lag(ts.slot_end) over (partition by c.classroom order by ts.slot_num)
      as lag_end,
    lead(ts.slot_start) over (partition by c.classroom order by ts.slot_num)
      as lead_start
  from time_slots ts
  cross join classrooms c
  left join occupied_classrooms oc on oc.classroom = c.classroom
    and not (oc.occupied_end <= ts.slot_start
      or oc.occupied_start >= ts.slot_end)
  where oc.classroom is null
),
free_slots_extended as (
  select fs.classroom, fs.slot_num,
    case when fs.lag_end is null or fs.lag_end != fs.slot_start
      then fs.slot_start end as slot_start,
    case when fs.lead_start is null or fs.lead_start != fs.slot_end
      then fs.slot_end end as slot_end
  from free_slots fs
)
select distinct fse.classroom,
  nvl(fse.slot_start, lag(fse.slot_start)
    over (partition by fse.classroom order by fse.slot_num)) as slot_start,
  nvl(fse.slot_end, lead(fse.slot_end)
    over (partition by fse.classroom order by fse.slot_num)) as slot_end
from free_slots_extended fse
where (fse.slot_start is not null or fse.slot_end is not null)
order by 1, 2;

这使:

CLASSROOM SLOT_START       SLOT_END       
--------- ---------------- ----------------
A         2013-10-10 07:00 2013-10-10 10:00 
A         2013-10-10 11:30 2013-10-10 21:15 
B         2013-10-10 07:00 2013-10-10 09:15 
B         2013-10-10 10:45 2013-10-10 14:30 
B         2013-10-10 16:00 2013-10-10 21:15 
C         2013-10-10 07:00 2013-10-10 21:15 

SQL Fiddle .