代码之家 › 专栏 › 技术社区 › Andy Baker

在预订系统中查找免费时段

date django mysql sql

Andy Baker · 技术社区 · 16 年前

在 my question about searching for date ranges 我试着简化问题,无意中提出了一个不同的简单问题。

与其用编辑把问题复杂化,我倒不如问我真正想问的问题。

我有两张桌子,房地产和预订。预订具有属性的外键以及开始和结束日期。

用户正在搜索空闲插槽,并以天为单位提供所需的持续时间。他们还提供了一系列他们感兴趣的开始日期。因此,搜索将沿着以下行进行: “给我找一个所有的房产,我想要一个3天的时段,5月的任何时候开始。”

现在我可以通过: 1。为每个可能的开始日运行31个查询 2。找到5月份的所有预订,将它们浓缩成一组31个布尔值,代表天数,并循环查找时段。

我认为(2)在大多数情况下更有效。有更好的算法吗?有纯SQL解决方案吗?

我将使用Django,我的数据集很小,所以我可能会同意“哑”的方法,但我很想知道最好的算法是什么样子的。

2 回复 | 直到 16 年前

Stringent Software 16 年前

可能对您的应用程序造成了过度破坏-但是:

以使“写入”过程更复杂为代价改进搜索的相对简单的方法是更改预订表,使其成为“可用性”表。

在一个布尔列中添加一个值,以指示插槽是空闲的还是已预订的(或者更好的是,仍然输入已预订插槽的客户的ID,如果插槽是空闲的,则使用0)。

从2009年1月1日至12月31日的一个免费时段开始??

当你得到一个预订时,把免费的时段分成3个(两个插入和一个更新)、预订的时段和两个可用的时段。

继续这样做,随着时间框架变得越来越零碎,预订过程将包括以下内容之一:

将整个“可用插槽”分配给某人(一个更新)
将“可用插槽”拆分为两个(一个更新和一个插入)
如果有人从可用的槽中预订了中间部分,则将槽拆分为3(如上所述)。

管理起来并不复杂,搜索过程变成了一个简单的查询:在所需的时间范围内查找任何可用的时段(booked=false或customerid=0,无论采用哪种方式),其中enddate-startdate>=所需的天数。

它使预订/可用性表的大小翻了一番,并使预订变得不那么简单,但需要权衡的是,搜索过程尽可能简单。

Tom H zenazn 16 年前

表定义会有帮助,但这里是。这应该适用于MS SQL Server,但一旦理解了其背后的想法,将其转换为MySQL应该是一项微不足道的任务。

日历表只是一个标准实用程序表,其中包含数据库中有用的所有日期。如果您还没有,我建议您创建一个并填充它。

CREATE TABLE Calendar
(
     date        DATETIME     NOT NULL,
     is_holiday  BIT          NOT NULL,
     -- any other columns that might be relevant for your business
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (date)
)

然后,您需要用可能对您的业务有意义的任何日期填充该表。即使你追溯到100年前100年后,表中的行数仍然少于75K,而且它在日期上聚集在一起,所以它应该是快速且容易处理的。它使许多基于日期的查询更加简单。

SELECT
     P.property_id,
     C.date
FROM
     Calendar C
JOIN Properties P ON 1=1
WHERE
     C.date BETWEEN @search_start_date AND @search_end_date AND
     NOT EXISTS
     (
          SELECT
               *
          FROM
               Bookings B
          WHERE
               B.property_id = P.property_id AND
               B.start_date <= DATEADD(dy, @slot_length, C.date) AND  -- You would use MySQLs date function
               B.end_date   >= C.date
     )

或者:

SELECT
     P.property_id,
     C.date
FROM
     Calendar C
JOIN Properties P ON 1=1
LEFT OUTER JOIN Bookings B ON
               B.property_id = P.property_id AND
               B.start_date <= DATEADD(dy, @slot_length, C.date) AND  -- You would use MySQLs date function
               B.end_date   >= C.date
WHERE
     C.date BETWEEN @search_start_date AND @search_end_date AND
     B.booking_id IS NULL