PHP/MYSQL用户日期时间范围重叠问题

Question

PHP/MYSQL用户日期时间范围重叠问题

8

请帮我解决这个问题（如需更好的理解，请参见附图），因为我完全无助。

如您所见，我的用户在我的数据库中存储其开始和结束的日期时间为YYYY-mm-dd H:i:s。现在，我需要根据最常见的时间范围重叠（对于大多数用户）找出所有用户的重叠部分。我想获得大多数用户的前3个最常见的datatime重叠。我该怎么做？

我不知道应该使用哪个mysql查询，或者从数据库选择所有日期时间（开始和结束）并在php中处理它（但是如何处理呢？）。如图所示，结果应该例如时间8:30 - 10:00是用户A + B + C + D的结果。

Table structure:
UserID | Start datetime | End datetime
--------------------------------------
A | 2012-04-03 4:00:00 | 2012-04-03 10:00:00
A | 2012-04-03 16:00:00 | 2012-04-03 20:00:00
B | 2012-04-03 8:30:00 | 2012-04-03 14:00:00
B | 2012-04-06 21:30:00 | 2012-04-06 23:00:00
C | 2012-04-03 12:00:00 | 2012-04-03 13:00:00
D | 2012-04-01 01:00:01 | 2012-04-05 12:00:59
E | 2012-04-03 8:30:00 | 2012-04-03 11:00:00
E | 2012-04-03 21:00:00 | 2012-04-03 23:00:00

- peter

请发布表结构。您想要运行当前的日/周/月/年/所有时间吗？ - user1191247

从我的角度来看，当前的日期/周等并不重要，因为例如用户D的开始日期时间是2012-04-01 01:00:01，结束日期时间是2012-04-05 12:00:59，而用户A的开始日期时间是2012-04-03 4:00:00，结束日期时间是2012-04-03 10:00:00，用户B的开始日期时间是2012-04-03 8:30:00，结束日期时间是2012-04-03 14:00:00。但是我的脚本应该检测到这些用户的时间范围重叠，即用户A+B+D的开始日期时间是2012-04-03 8:30:00，结束日期时间是2012-04-03 10:00:00。 - peter

我还没有遇到过能够精确到微秒级别管理他们的约会的人;) 尽管使用我的建议解决方案，您确实会遇到预订在time_slot上开始和结束的问题。它只是旨在提供一个简单的示例，让OP思考如何解决他的问题。 - user1191247

抱歉，@nnichols，我是在向原帖作者提问，而不是向你提问。 - Mike Sherrill 'Cat Recall'

在上面的例子中，为什么10:00-11:00（B+D+E）不是预期结果集的一部分？ - James Holwell

显示剩余2条评论

4个回答

0

我不会在 SQL 中做太多事情，用编程语言要简单得多，SQL 不适合这种情况。

当然，将一天分成“时间段”是很明智的——这是统计学。但是，一旦你开始处理跨越 00:00 边界的日期时，使用连接和内部查询时会变得棘手。尤其是对于 MySQL，它不太喜欢内部查询。

以下是可能的 SQL 查询：

SELECT count(*) FROM `times`
WHERE
  ( DATEDIFF(`Start`,`End`) = 0 AND
    TIME(`Start`) < TIME('$SLOT_HIGH') AND
    TIME(`End`) > TIME('$SLOT_LOW'))
  OR
  ( DATEDIFF(`Start`,`End`) > 0 AND
    TIME(`Start`) < TIME('$SLOT_HIGH') OR
    TIME(`End`) > TIME('$SLOT_LOW')

这里是一些伪代码

granularity = 30*60; // 30 minutes
numslots = 24*60*60 / granularity;
stats = CreateArray(numslots);
for i=0, i < numslots, i++ do
  stats[i] = GetCountFromSQL(i*granularity, (i+1)*granularity); // low, high
end

是的，这样做会产生numslots个查询，但没有连接等操作，因此应该非常快。而且您可以轻松更改分辨率。

另一个积极的事情是，您可以“问自己”，“我有两个可能的时间段，我需要人数更多的那个，我应该使用哪个？”然后只需使用相应的范围运行查询两次，您不会被预定义的时间段所限制。

要仅查找完全重叠（仅当条目覆盖整个时间段时才计算），您必须在查询中切换低和高范围。

您可能已经注意到，我没有添加可能跨越多天的条目之间的时间，但是添加整天将使所有时间段增加一天，这样就变得相当无用了。但是，您可以通过选择sum(DAY(End) - DAY(Start))并将返回值添加到所有时间段来添加它们。

- dualed

0

这个表看起来相当简单。我建议你的SQL查询保持简单：

SELECT * FROM tablename

然后，当你在PHP对象中保存了信息后，使用循环和比较进行处理。

最简单的形式如下：

for($x, $numrows = mysql_num_rows($query); $x < $numrows; $x++){

     /*Grab a row*/
     $row = mysql_fetch_assoc($query);

     /*store userID, START, END*/
     $userID = $row['userID'];
     $start = $row['START'];
     $end = $row['END'];

     /*Have an array for each user in which you store start and end times*/  

     if(!strcmp($userID, "A")
     {
        /*Store info in array_a*/
     }
     else if(!strcmp($userID, "B")
     {
        /*etc......*/
     } 
}
 /*Now you have an array for each user with their start/stop times*/

 /*Do your loops and comparisons to find common time slots. */

 /*Also, use strtotime() to switch date/time entries into comparable values*/

当然，这只是非常基本的形式。在执行上面的循环之前，您可能需要通过数组进行一次循环以首先获取所有用户ID，然后再进行比较。

- davepmiller

0

以下类似的内容应该能帮助你入门 -

SELECT slots.time_slot, COUNT(*) AS num_users, GROUP_CONCAT(DISTINCT user_bookings.user_id ORDER BY user_bookings.user_id) AS user_list
FROM (
    SELECT CURRENT_DATE + INTERVAL ((id-1)*30) MINUTE AS time_slot
    FROM dummy
    WHERE id BETWEEN 1 AND 48
) AS slots
LEFT JOIN user_bookings
    ON slots.time_slot BETWEEN `user_bookings`.`start` AND `user_bookings`.`end`
GROUP BY slots.time_slot
ORDER BY num_users DESC

该想法是创建一个派生表，其中包含一天的时间段。在这个例子中，我使用了虚拟表（可以是具有所需集合的连续AI id的任何表）通过逐步添加30分钟来创建时间段列表。然后将此结果与预订表连接以便能够计算每个时间段的书籍数量。

更新对于整个日期/时间范围，您可以使用此查询获取所需的其他数据-

SELECT MIN(`start`) AS `min_start`, MAX(`end`) AS `max_end`, DATEDIFF(MAX(`end`), MIN(`start`)) + 1 AS `num_days`
FROM user_bookings

然后这些值可以被替换到原始查询中，或者两者可以合并 -

SELECT slots.time_slot, COUNT(*) AS num_users, GROUP_CONCAT(DISTINCT user_bookings.user_id ORDER BY user_bookings.user_id) AS user_list
FROM (
    SELECT DATE(tmp.min_start) + INTERVAL ((id-1)*30) MINUTE AS time_slot
    FROM dummy
    INNER JOIN (
        SELECT MIN(`start`) AS `min_start`, MAX(`end`) AS `max_end`, DATEDIFF(MAX(`end`), MIN(`start`)) + 1 AS `num_days`
        FROM user_bookings
    ) AS tmp
    WHERE dummy.id BETWEEN 1 AND (48 * tmp.num_days)
) AS slots
LEFT JOIN user_bookings
    ON slots.time_slot BETWEEN `user_bookings`.`start` AND `user_bookings`.`end`
GROUP BY slots.time_slot
ORDER BY num_users DESC

编辑：我已在GROUP_CONCAT()中添加了DISTINCT和ORDER BY子句，以回应您的最后一个查询。

请注意，您需要在虚拟表中拥有更大范围的ID。我尚未测试此查询，因此可能存在语法错误。

- user1191247

所以我的默认表是user_bookings，包含user_id、start和end。然后我创建了一个带有id（AI）的dummy表。但请问我还应该对dummy表做什么？我正在尝试让它工作。 - peter

您不需要创建表dummy，尽管我在服务器上有一个来防止id序列中留下的空洞。您可以使用任何具有覆盖所需范围的id的表。我已经基于48 * 30分钟的时间段覆盖了当前日期的示例，因此您只需要从1到48的id即可。 - user1191247

谢谢。它涵盖了48*30，但仅限今天。我认为这是一个不错的开始，但不知道如何根据我的需求进行调整。 - peter

在这种情况下，你需要更好地解释你的需求。我开始时问过你：“你想要运行这个程序是针对当前的日/周/月/年/全部时间吗？”你可以增加用于创建时间段的范围，只要你有足够大的ID范围来传递进去。 - user1191247

@peter - 我的解决方案的问题在于它基于创建日期时间值列表，然后连接到预订。日期时间值的分辨率可以增加到分钟甚至秒级，但这会导致更大、更慢的临时表。这会产生几个不同的问题。首先，假设一个值为'2012-01-01 10:30:00'的值将连接到一个在那个时间结束的预订和另一个在那个时间开始的预订，尽管它们实际上并不重叠。您在这张表中有多少条记录，您预计它会增长到多大？ - user1191247

显示剩余4条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- tazer84 · Accepted Answer

你实际上拥有一组集合，并想确定它们中是否有任何一个具有非零交集。这正是在尝试查找嵌套集合中节点的所有祖先时所问的确切问题。

我们可以证明，对于每个重叠，至少一个时间窗口将具有开始时间落在所有其他重叠时间窗口之内。利用这个小技巧，我们不需要在一天内实际构造人工时间段。只需取一个开始时间，看它是否与其他时间窗口相交，然后计算交集的数量即可。

那么这个查询是什么？

/*SELECT*/
SELECT DISTINCT
    MAX(overlapping_windows.start_time) AS overlap_start_time,
    MIN(overlapping_windows.end_time) AS overlap_end_time ,
    (COUNT(overlapping_windows.id) - 1) AS num_overlaps
FROM user_times AS windows
INNER JOIN user_times AS overlapping_windows
ON windows.start_time BETWEEN overlapping_windows.start_time AND overlapping_windows.end_time
GROUP BY windows.id
ORDER BY num_overlaps DESC;

根据您的表大小和计划运行此查询的频率，可能值得在其上删除空间索引（请参见下文）。

更新

如果您经常运行此查询，则需要使用空间索引。由于基于范围的遍历（即开始时间是否落在开始/结束范围之间），B树索引对您没有任何作用。它必须是空间索引。

ALTER TABLE user_times ADD COLUMN time_windows GEOMETRY NOT NULL DEFAULT 0;
UPDATE user_times SET time_windows = GeomFromText(CONCAT('LineString( -1 ', start_time, ', 1 ', end_time, ')'));
CREATE SPATIAL INDEX time_window ON user_times (time_window);

然后，您可以更新上述查询中的ON子句，使其为：

ON MBRWithin( Point(0,windows.start_time), overlapping_windows.time_window )

这将为您获取查询的索引遍历。如果您计划经常运行查询，请执行此操作。

空间索引的信用归功于Quassoni's博客。