php - MySQL/PHP : Search rows by datetime RANGE given overlaps in the table

Question

我有一个表格可以显示人们何时可以工作，如下所示:

+------+---------------------+---------------------+
| name | start               | end                 |
+------+---------------------+---------------------+    
| Odin | 2015-07-01 11:00:00 | 2015-07-01 11:30:00 |
| Thor | 2015-07-01 11:00:00 | 2015-07-01 11:30:00 |
| Odin | 2015-07-01 11:20:00 | 2015-07-01 12:45:00 |
| Odin | 2015-07-01 12:30:00 | 2015-07-01 15:30:00 |
| Thor | 2015-07-01 15:00:00 | 2015-07-01 17:00:00 |
+------+---------------------+---------------------+

我想检查特定人员是否可以在给定范围内工作。例如，我想要一个 PHP 函数来返回给定范围内可以工作的人员的姓名，如下所示:canWork($start, $end)

这个重要的部分是处理重叠，特别是因为表格可能非常非常大。例如，如果我调用 canWork('2015-07-01 11:10:00', '2015-07-01 15:30:00') 我希望得到 Odin 返回表格的第 1、3 和 4 行确实覆盖了该范围。

有没有一种简单的方法可以用 MySQL 做到这一点？还是 PHP？

Answer 1

尽量避免在这种大数据情况下循环数据。在类似的练习中，SQL 能够在几秒钟内交付在代码中需要数小时才能完成的工作。明智地看待数据的值(value)。

这里的明智步骤是:您可以通过检查时间的总和来减少可能匹配的数量:范围内的时间应等于(或小于)记录中时间的总和。

然而，由于输入的开始时间可能小于您要查找的开始时间，而结束时间可能大于您要查找的结束时间，因此您首先必须找到最接近结束时间的结束时间和最接近的开始时间到开始时间。

(end 是一个保留字，所以这段代码不会与columnname 一起工作，endtime 和starttime 是调度检查的变量)

每个用户的开始时间(最后可能的):

SELECT name,MAX(start) AS MAX_start
FROM scheduleTable
WHERE start<=starttime
GROUP BY name;

每个用户的结束时间(第一个可能的)

SELECT name,MIN(`end`) AS MIN_end
FROM scheduleTable
WHERE `end`>=endtime
GROUP BY name;

将这些组合在一起可以得到一个可能用户的子集，而且可以在

SELECT name, MAX_start,MIN_end
FROM 
(SELECT name,MIN(`end`) AS MIN_end
FROM scheduleTable
WHERE `end`>=endtime
GROUP BY name) a
INNER JOIN
(SELECT name,MAX(start) AS MAX_start
FROM scheduleTable
WHERE start<=starttime
GROUP BY name) b ON a.name=b.name;

这将为您提供一个有效的 end 时间表，该时间表尽可能接近为安排目的指示的结束时间，但至少等于指示的结束时间。

应用所有时间范围必须至少等于结束时间-开始时间这一事实:

SELECT st.name
FROM scheduleTable st
INNER JOIN (
  SELECT name, MAX_start AS start,MIN_end AS end
  FROM 
  (SELECT name,MIN(`end`) AS MIN_end
  FROM scheduleTable
  WHERE `end`>=endtime
  GROUP BY name) a
  INNER JOIN
  (SELECT name,MAX(start) AS MAX_start
  FROM scheduleTable
  WHERE start<=starttime
  GROUP BY name) b ON a.name=b.name
) et ON st.name=et.name
WHERE et.start>={starttime} AND `end`<=et.endtime AND et.name=st.name
GROUP BY st.name
HAVING SUM(st.`end`-st.start)>=(endtime-starttime);

You might have to manipulate the start and end time to unix time or use mysql date time functions for the calculations.

仍然可能存在差距:那些需要第二次检查。为此，使用 group_concat 获取一些数据，我们可以将其作为 1 次调用传递给函数。函数结果为 0 表示:未找到间隙，1 表示已找到间隙:

SELECT a.name
FROM (
  SELECT st.name,
    GROUP_CONCAT(start ORDER BY start ASC SEPARATOR ',') starttimelist,
    GROUP_CONCAT(`end` ORDER BY `end` ASC SEPARATOR ',') endtimelist
  FROM scheduleTable st
  INNER JOIN (
    SELECT name, MAX_start AS start,MIN_end AS end
    FROM 
    (SELECT name,MIN(`end`) AS MIN_end
    FROM scheduleTable
    WHERE `end`>=endtime
    GROUP BY name) a
    INNER JOIN
    (SELECT name,MAX(start) AS MAX_start
    FROM scheduleTable
    WHERE start<=starttime
    GROUP BY name) b ON a.name=b.name
  ) et ON st.name=et.name
  WHERE et.start>={starttime} AND `end`<=et.endtime AND et.name=st.name
  GROUP BY st.name
  HAVING SUM(st.`end`-st.start)>=(endtime-starttime);
) a
WHERE gapCheck(starttimelist,endtimelist)=0;

警告:不要将 DISTINCT 添加到 GROUP_CONCAT:开始/结束时间列表将具有不同的长度并且 gaCcheck 函数将失败....

函数gapCheck:在此函数中，第一个开始时间和最后一个结束时间可以忽略:开始时间大于或等于开始时间，结束时间大于或等于结束时间。因此不需要边界检查，而且无论如何也不必检查边界是否存在间隙。

CREATE FUNCTION gapCheck(IN starttimeList VARCHAR(200),endtimeList VARCHAR(200))
BEGIN
  DECLARE helperTimeStart,helperTimeEnd,prevHelperTimeStart,prevHelperTimeEnd DATETIME
  DECLARE c,splitIndex,gap INT
SET c-0;
SET gap=0;
WHILE(c=0) DO
  SET splitIndex=INSTR(starttimeList,',');
  IF(splitIndex>0) THEN
    SET helperTimeStart=SUBSTRING(starttimeList,1,splitIndex-1);
    SET starttimeList=SUBSTRING(starttimeList,splitIndex); /* String for the next iteration */
  ELSE
    SET helperTimeStart=starttimeList; /* End of list reached */
    SET helperTimeEnd=endtimeList; /* end can be set too: Lists are of same length */
    SET c=1;
  END IF;

  IF(splitIndex>0) THEN
    SET splitIndex=INSTR(endtimeList,',');
    SET helperTimeEnd=SUBSTRING(endtimeList,1,splitIndex-1);
  END IF;

  IF prevHelperTimeEnd>=helperTimeEnd THEN /* if prevHelperTimeEnd is not set, this is false and the check is skipped: on the first record we can not check anything */
    /* If previous end time > current start time: We have a gap */
    IF CAST(prevHelperTimeEnd AS DATETIME)>=CAST(helperTimeStart AS DATETIME) THEN
      gap=1;
    END IF;
  END IF;

  /* save some data for the next loop */
  SET prevHelperTimeStart=helperTimeStart;
  SET prevHelperTimeEnd=helperTimeEnd;
END WHILE;
RETURN gap;
END;