sql - 在给定日期范围、持续时间和不可用日期列表的情况下查找可用公寓

Question

我有一个难题，希望有人能帮我解决

我有一张公寓列表。每个列表都有一个或多个“不可用”日期单独存储在另一个表中，我们称之为“off_days”。例如从 9 月 1 日到 4 日期间不可用的列表将在“off_days”表中有 4 个条目，每天一个。

我正在寻找最有效的方法来搜索(最好是在数据库级别)在两个日历日之间至少连续 N 天可用的列表(“可用”是指不在“off_days”表中的任何一天具体上市)。例如“显示 9 月至少连续 5 天有空的所有房源”

我一直在考虑如何在现实世界中解决这个问题(通过查看标有 X 的日历并扫描空闲 block )并开始考虑使用二进制来表示可用/不可用天数。即对于给定的一周，0111001 ( = 57) 会告诉我该周最多连续三天可用。

这question一旦我有了给定日期范围的二进制数，这似乎是一个好的开始，但现在我陷入了如何在给定日期范围内动态计算该数字的问题，同样，在数据库级别上……有什么想法吗？或对这种方法或另一种方法的想法？

Answer 1

公寓可在休息日空档。这意味着您想知道每个序列的差距有多大，lag() 函数可以为您提供以下信息:

select od.*,
       lag(unavailable) over (partition by apartmentid order by unavailable) as prev_una
from offdays od;

实际天数是不可用天数与上一个天数之差减 1。现在，假设两个日历日为 v_StartDate 和 v_EndDate。现在你基本上可以得到你想要的了:

select od.*,
       ((case when unavailable is NULL or unavailable > v_EndDate
              then v_EndDate + 1 else unavailable
         end) -
        (case when prev_una is null or prev_una < v_StartDate
              then v_StartDate - 1 else prev_una
         end) - 1
       ) as days_available
from (select od.*, lag(unavailable) over (partition by apartmentid order by unavailable) as prev_una
      from offdays od
     ) od
order by days_available desc;

case 逻辑本质上是在该期间之前和之后放置停止日期。

这不是很完整，因为它存在边界问题:公寓不在 offdays 时的问题以及不可用时段超出范围时的问题。让我们用 union all 和一些过滤来解决这个问题:

select od.*,
       ((case when unavailable is NULL or unavailable > v_EndDate
              then v_EndDate + 1 else unavailable
         end) -
        (case when prev_una is null or prev_una < v_StartDate
              then v_StartDate - 1 else prev_una
         end) - 1
       ) as days_available
from (select od.apartmentId, unavailable,
             lag(unavailable) over (partition by apartmentid order by unavailable) as prev_una
      from offdays od
      where od.unavailable between v_StartDate and v_EndDate
      union all
      select apartmentid, NULL, NULL
      from apartments a
      where not exists (select 1
                        from offdays od
                        where od.apartmentid = a.apartmentid and
                              od.unavailable between v_StartDate and v_EndDate
                       )
     ) od
order by days_available desc;