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

4 回答

• 0

  你实际拥有的是一组集合，并希望确定它们中是否有任何非交叉点 . 这是在尝试查找嵌套集中节点的所有祖先时要求的确切问题 .

  我们可以证明，对于每个重叠，至少一个时间窗口将具有落在所有其他重叠时间窗口内的开始时间 . 使用这个小窍门，我们不需要在当天真正构建人工时段 . 只需要一个开始时间，看看它是否与任何其他时间窗口相交，然后只计算交叉点的数量 .

  那么查询是什么？

  /*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;
  

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

  更新

  如果经常运行此查询，则需要使用空间索引 . 由于基于范围的遍历（即start_time落在开始/结束范围之间），BTREE索引不会为您做任何事情 . 它必须是空间的 .

  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 blog .

  回复于 2024-04-19T19:19:34+08:00
• 0

  表似乎很简单 . 我会保持你的SQL查询非常简单：

  SELECT * FROM tablename

  然后，当您在PHP对象中保存信息时 . 使用循环和比较使用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*/
  

  当然，这是非常基本的形式 . 在上面显示的循环中比较它们之前，您可能希望在数组中进行一次循环以首先获取所有userID .

  回复于 2024-04-19T19:19:34+08:00
• 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)) 添加它们，并将返回值添加到所有插槽 .

  回复于 2024-04-19T19:19:34+08:00
• 2

  这样的事情应该让你开始 -

  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
  

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

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

  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
  

  EDIT 我在 GROUP_CONCAT() 中添加了 DISTINCT 和 ORDER BY 条款以响应您的上一次查询 .

  请注意，虚拟表中将需要更大范围的ID . 我没有测试过这个查询，所以它可能有语法错误 .

  回复于 2024-04-19T19:19:34+08:00