Calculating total quantity of equipments for a date range
项目：我正在做一个关于一些房间和房间内使用的设备的项目。该软件是关于安排房间内的设备。换句话说，它是一个预订软件，可以在不同的房间里为需要的日期和时间范围预订选定的设备。我在使用 PHP 的 MYsSQL 数据库中有很多表，但我会提到我的问题所涉及的表。我将与我的问题相关的表格是设备表(表 A)、时间表表(表 B)和相关时间表中使用的设备(表 C)。
表A：设备清单表
|
1
2 3 4 5 6 7 |
eqid | eqName | available|
1 | book | 90 | 2 | pen | 82 | 3 | computer | 25 | |
在表 A 中； eqid 表示设备的唯一标识，eqName 表示设备的名称，available 表示现有的可用设备总数。
表 B：日程表
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
scheduleid | startDate | endDate | startTime | endTime | office |
1 | 2012–08–27 | 2012–08–27 | 08:30:00 | 10:00:00 | room1 | 2 | 2012–08–27 | 2012–08–27 | 09:30:00 | 11:00:00 | room3 | 3 | 2012–08–28 | 2012–08–30 | 08:30:00 | 12:00:00 | room2 | 4 | 2012–08–29 | 2012–08–31 | 11:30:00 | 14:00:00 | room1 | 5 | 2012–08–28 | 2012–08–28 | 10:30:00 | 14:00:00 | room3 | 6 | 2012–08–27 | 2012–08–30 | 08:30:00 | 10:00:00 | room4 | 7 | 2012–08–27 | 2012–08–27 | 10:30:00 | 12:00:00 | room4 | 8 | 2012–08–27 | 2012–08–30 | 08:30:00 | 11:00:00 | room6 | 9 | 2012–08–27 | 2012–08–27 | 10:30:00 | 12:00:00 | room5 | |
在表 B 中； scheduleid 表示时间表的唯一 id，startDate 和 endDate 是时间表的日期范围，startTime 和 endTime 是时间表的时间范围，office 表示时间表将发生的位置。让我在这里举个例子。 Scheduleid 1 表示 2012 年 8 月 27 日星期一有预订，时间为 08.30 至 10:00。由于它在同一天开始和结束，这只是房间 1 的一天预订。但是，Scheduleid 3 表示预订从 2012 年 8 月 28 日星期二开始，一直持续到 2012 年 8 月 30 日星期四 08:30-12:00… 换句话说，它持续 3 天，每天从08:30 到 12:00… 所以周二到周四 08:30 到 12:00 房间 2 有预订… 我希望这很清楚。
表C：相关时间表中使用的设备
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Autoid | scheduleid | eqid | amountInSch|
1 | 1 | 1 | 2 | 2 | 1 | 2 | 3 | 3 | 1 | 3 | 1 | 4 | 2 | 1 | 1 | 5 | 2 | 2 | 1 | 6 | 2 | 3 | 2 | 7 | 3 | 2 | 1 | 8 | 3 | 3 | 3 | 9 | 4 | 2 | 1 | 10 | 4 | 3 | 1 | 11 | 5 | 1 | 1 | 12 | 6 | 1 | 1 | 13 | 6 | 3 | 2 | 14 | 6 | 2 | 4 | 15 | 7 | 1 | 5 | 16 | 7 | 2 | 6 | 17 | 8 | 2 | 1 | 18 | 9 | 1 | 8 | 19 | 9 | 2 | 5 | 20 | 9 | 3 | 6 | |
表C中：Autoid表示自增产生的唯一自动id，scheduleid来自表B，eqid来自表A，amountInSch表示相关调度中将使用多少(数量)设备。我想在这里举个例子。表C中的Scheduleid 1，有3行。这意味着表 B 中相关的 scheduleid 1 将在表 B 中指定日期和时间的 room1 中使用 2 本书 (eqid 1)、3 支笔 (eqid 2) 和 1 台计算机 (eqid 3)。另一个例子是表 C 中的 scheduleid 3 是相关的 2 行。这意味着 1 支笔 (eqId 2) 和 3 台电脑 (eqId 3) 将在 2012 年 8 月 27 日至 30 日每天 08:30 至 12:00 在 room2 中使用。
以上是解释并提供了有关该项目的一些信息。表行不是永久的。当您预订时，B 表中会出现新的一行，如果选择了设备，C 表中会出现新的行…
问题：
当我提供 eqId、startDate、endDate、startTime 和 endTime 时，我想计算特定设备的剩余量…
一个例子：
eqId：1(书)
开始日期：2012-08-27
结束日期：2012-08-27
开始时间：08:30:00
结束时间：12:00:00
结果应该是：计划中使用了 14 本书，剩余 76 本书可用
因为：如果您查看 scheduleIds 和相关的 eqIds，您只会看到与我的查询相关的 1、2、6、7、9 个 scheduleIds(日期和 eqId)。如果将表 C 中相关的所有数量相加，您将得到错误的结果。换言之，eqId(1-book) 和 1、2、6、7、9 scheduleId 的相关数量分别为 2、1、1、5、8。因此，如果将它们相加，您将得到 17，这是错误的。因为，1 和 9 的时间表在开始和结束时间方面不相交，而 6 和 7 也不相交。由于他们 2 保持孤独，可以单独计算。我们必须将 1 和 9 视为总和 8，因为 8 大于 2。6 和 7 相同，因为 5 大于 1，所以视为 5…
所以伙计们！我不确定如何在编程算法中求和/这个。有没有办法在 SQL 中做，还是我必须一起使用 PHP 和 Mysql？以及如何？
干杯！
SQLFiddle 记录
我从以下 SQL 开始收集与给定范围相交的所有日期范围：
|
1
2 3 4 5 6 7 |
小提琴
这只是第一部分。接下来，您必须找出可能的重叠；这对于 SQL 来说是不切实际的，所以我建议在 PHP 中执行此操作。
不幸的是，我选择的通用算法是 O(n**2)，它是这样的：
- 创建以时间为横轴的时间线(按每天划分)
- 遍历每个日期/时间范围并标记其左右边缘的时间以创建每个可能排列的时间段。
- 使用这些段，您可以对重叠进行垂直求和，然后获取每日最大值。
希望对您有所帮助。
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/php/268528.html