SQL - 在计划中查找开放空间

Question

我正在使用一个 SQLite 数据库，我有三个表来描述建筑物、房间和预定事件。

表格如下所示:

建筑物(ID,名称)

房间(ID,BuildingID,Number)

事件(ID,BuildingID,RoomID,Days,s_time,e_time)

因此，每个事件都与建筑物和房间相关联。 Days 列包含一个整数，它是对应于星期几的素数的乘积(值 21 表示事件发生在星期二 = 3 和星期四 = 7)。

我希望找到一种方法来生成特定建筑物中的房间报告，这些房间将在接下来的几个小时内开放，以及它们将开放多长时间。

这是我到目前为止所拥有的:

SELECT Rooms.Number 
FROM Rooms
INNER JOIN Buildings on ( Rooms.BuildingID = Buildings.ID )
WHERE 
  Buildings.Name = "BuildingName"

EXCEPT

SELECT Events.RoomID
FROM Events
INNER JOIN Buildings on ( Events.BuildingID = Buildings.ID )
WHERE
  Buildings.Name = "BuildingName" AND
  Events.days & 11 = 0 AND
  time("now", "localtime" BETWEEN events.s_time AND events.e_time;

在这里，我找到特定建筑物的所有房间，然后删除当前正在进行预定事件的房间。

我期待所有有用的提示/评论。

Answer 1

如果您将日期存储为素数的乘积，则模 (%) 运算符可能更有用:

SELECT * FROM Events
INNER JOIN Buildings on (Events.BuildingID = Buildings.ID)
WHERE
    (Events.Days % 2 = 0 AND Events.Days % 5 = 0)

将选择在星期一或星期三发生的事件。

不过我必须指出，存储素数的乘积在计算上和存储上都很昂贵。存储两个的幂的总和要容易得多(周一 = 1，周二 = 2，周三 = 4，周四 = 8，周五 = 16，周六 = 32，周日 = 64)。

当前实现的最大可能值为 510,510。存储此类数字的最小数据类型是 int(每行 32 位)，检索编码数据最多需要 7 次模 (%) 运算。

2^n 求和方法的最大可能值为 127，它可以存储在一个 tinyint(每行 8 位)中，检索编码数据将使用按位和 (&)，这会更便宜(因此更快)。

对于您正在使用的东西来说可能不是问题，但是选择任何可以为您提供最佳空间和性能效率的方法是一个好习惯，以免在您的解决方案在更大范围内实现时遇到严重问题。