日期交集和空间可用性

Question

我目前正在尝试检查一个日期范围内的“空间”的可用性，而这个日期范围可以无限长。 表格如下：

空间：

id  available_spaces    name
1   20                  Space 1
2   40                  Space 2
3   10                  Space 3

预订（结束日期可以为空，这意味着无休止的预订）：

^{pr2}$

然后我希望能够进行搜索，例如：

from:   11/12-2016
to:     null (again meaning endless)
spaces: 2

这个查询应该返回空格：space2，space3，因为它们在这个时间间隔内都有足够的可用性。在

通过将搜索中所需的空间量更改为1而不是2，将得到以下结果： 搜索：

from:   11/12-2016
to:     null (again meaning endless)
spaces: 1

空格1，空格2，空格3。 我很难从月数中找到可供选择的无限空间。在

Answer 1

首先使用SQL解释查询的工作原理，然后再构建SQLAlchemy，这样就更容易了。我假设预订和搜索总是有一个开始，或者换句话说，最终只能是无限的。使用range types和operators，应该首先查找与搜索重叠的预订。在

select *
from booking
where daterange(start_date, end_date, '[)')
   && daterange('2016-12-11', null, '[)');

从已找到的预订中，您需要找到交叉点并对已用空间求和。要查找交叉口，请使用预订的开头并查找包含它的预订。对手头的所有预订重复此操作。例如：

^{pr2}$

在查询表中：

with bs as (
    select *
    from booking
    where daterange(start_date, end_date, '[)')
       && daterange('2016-12-11', null, '[)')
)
select distinct
       b1.space_id,
       sum(b2.spaces) as sum
from bs b1
join bs b2
  on b1.start_date <@ daterange(b2.start_date, b2.end_date, '[)')
 and b1.space_id = b2.space_id
group by b1.id, b1.space_id;

给出了示例数据的结果

 space_id | sum 
     +  -
        1 |  19
(1 row)

因为只有两个预订，而且他们有相同的开始日期。查询远不是最优的，对于每个范围都必须扫描所有的范围，因此至少O（n^2）。在过程设置中，您将使用interval tree或类似的方法进行查找，并且可能通过一些适当的索引和更改来改进SQL。在

通过交叉预订金额，您可以检查是否存在剩余空间小于搜索所需空间的总和：

with bs as (
        select *
        from booking
        where daterange(start_date, end_date, '[)')
           && daterange('2016-12-11', null, '[)')
), cs as (
        select distinct
               b1.space_id,
               sum(b2.spaces) as sum
        from bs b1
        join bs b2
          on b1.start_date <@ daterange(b2.start_date, b2.end_date, '[)')
         and b1.space_id = b2.space_id
          Could also use distinct & sum() over (partition by b1.id) instead
        group by b1.id, b1.space_id
)
select *
from space
where not exists(
        select 1
        from cs
        where cs.space_id = space.id
                Check if there is not enough space
          and space.available_spaces - cs.sum < 2
);

由此可以直接形成SQLAlchemy版本：

from functools import partial
from sqlalchemy.dialects.postgresql import DATERANGE

# Hack. Proper type for passing daterange values is
# psycopg2.extras.DateRange, but that does not have
# the comparator methods.
daterange = partial(func.daterange, type_=DATERANGE)

bs = session.query(Booking).\
    filter(daterange(Booking.start_date, Booking.end_date, '[)').
           overlaps(daterange('2016-12-11', None, '[)'))).\
    cte()

bs1 = bs.alias()
bs2 = bs.alias()

cs = session.query(bs1.c.space_id,
                   func.sum(bs2.c.spaces).label('sum')).\
    distinct().\
    join(bs2, (bs2.c.space_id == bs1.c.space_id) &
              daterange(bs2.c.start_date,
                        bs2.c.end_date).contains(bs1.c.start_date)).\
    group_by(bs1.c.id, bs1.c.space_id).\
    cte()

query = session.query(Space).\
    filter(~session.query(cs).
           filter(cs.c.space_id == Space.id,
                  Space.available_spaces - cs.c.sum < 2).
           exists())