假设我们有这样的资源可用性表:
+-----------+-----------------------------------------------------------+
| date | obvious, the date |
| timeslot | we have 12 fixed 2-hour timeslots so this will be 1 to 12 |
| r1 | number of resource type 1 available during this timeslot |
| r2 | same, for resource type 2 |
| r3 | same, for resource type 3 |
+-----------+-----------------------------------------------------------+
现在,我想查看我可以用来执行job #43
的所有可用时隙。对于这项工作,我需要两个r1单位、一个r2单元和三个1r3。假设作业需要一个时隙,我可以使用以下查询:
SELECT `date`, `timeslot` FROM `resource_availability`
WHERE
`r1` > '1' AND
`r2` > '0' AND
`r3` > '2'
ORDER BY 'date`, `timeslot`;
但是,如果我有另一个作业job #86
,它需要3个时隙才能完成,并且无法停止重新启动,那么是否可以通过查询获得安全的启动时间?
我目前正在检查while
循环中的连续性,但我认为让查询这样做是可能的。
如果可能的话,我想知道哪种更快、更高效。对于功效评估,应该注意的是,作为一种位图,该表会非常频繁地更新——即,随着每个作业的调度,资源可用性列会更新。
此外,很明显,该系统的目的是允许检查如果。如果我的方法不是最优的,还有什么更好的选择?
如果最后一个问题是太多,请忽略它,或者在评论中告诉我,我会删除它。
Whew。。。我想出了一个主意,可能会得到你想要的。如果这需要一点理解,请原谅我,但我希望你看到,这实际上是一个相当简单的解决中等复杂问题的方法。
我会构建查询(在PHP中),使其具有n个自联接,其中n是作业所需的时隙数。自联接加入下一个连续时隙,并且基于所有时隙中可用的资源来细化结果。请注意,您可以将动态创建的WHERE子句移动到JOIN条件中。。。我已经看到MySQL的版本,它将以这种方式提高速度。
php代码:
// $r1, $r3, and $r3 are the required resources for this job.
$join_format = 'JOIN timeslots AS %s ON %date = %s.date AND %s.timeslot+1 = %s.timeslot';
$where_format = '(%s.r1 >= '.$r1.' AND %s.r2 >= '.$r2.' AND %s.r3 >= '.$r3.')';
$joins = array();
$wheres = array("block1.date > CURDATE()",
sprintf($where_format, "block1", "block1", "block1")
);
$select_list = 'block1.date, block1.timeslot as starting_time, block' . $slots_needed . '.timeslot as ending_time';
for($block = 2; $block <= $slots_needed; $block++) {
$join_alias = "block" . $block;
$previous_alias = "block" . ($block-1);
$joins[] = sprintf($join_format, $join_alias, $previous_alias,$join_alias, $previous_alias, $join_alias);
$wheres[] = sprintf($where_format, $join_alias, $join_alias, $join_alias);
}
$query_format = 'SELECT %s FROM timeslots as block1 %s WHERE %s GROUP BY block1.date, block1.timeslot ORDER BY block1.date ASC, block1.timeslot ASC';
$joins_string = implode(' ', $joins);
$wheres_string = implode(' AND ', $wheres);
$query = sprintf($query_format, $select_list, $joins_string, $wheres_string);
就我的意图而言,这应该会产生这样的查询(对于2个所需的块,每个块需要1个资源:
结果SQL:
SELECT
block1.date,
block1.timeslot as starting_time,
block2.timeslot as ending_time
FROM
timeslots AS block1
JOIN timeslots AS block2
ON block1.date = block2.date AND block1.timeslot+1 = block2.timeslot
WHERE
block1.date > CURDATE()
AND (block1.r1 >= 1 AND block1.r2 >= 1 AND block1.r3 >= 1)
AND (block2.r1 >= 1 AND block2.r2 >= 1 AND block2.r3 >= 1)
GROUP BY
block1.date, block1.timeslot
ORDER BY
block1.date ASC, block1.timeslot ASC
它应该产生这样的结果:
预期结果集:
+------------+---------------+-------------+
| date | starting_time | ending_time |
+------------+---------------+-------------+
| 2001-01-01 | 1 | 2 |
+------------+---------------+-------------+
| 2001-01-01 | 2 | 3 |
+------------+---------------+-------------+
| 2001-01-01 | 7 | 8 |
+------------+---------------+-------------+
| 2001-01-01 | 8 | 9 |
+------------+---------------+-------------+
| 2001-01-02 | 4 | 5 |
+------------+---------------+-------------+
请注意,如果需要2个块,但有3个可用(连续),则查询将返回两个选项(第一个和第二个或第二个和第三个可用时间)。