mysql - 查询以找到可用的时隙

Question

我有一个名为 appointments 的表和一个名为 shifts 的表。在表约会中，有 shift_id，所以它看起来像这样:

编号 |开始日期 |结束日期 | shift_id(FK)

类次示例是 09:00 到 17:00，其中包含几个约会:

09:30 - 10:10 | 11:30 - 11:50 | 13:00 - 14:00

我目前正在使用这个查询来获取可用的时间段，但它有一个边缘案例问题。它找不到 9 - 9:30 的开始。

SELECT Available_from, Available_to
        FROM (
        SELECT @lasttime_to AS Available_from, start_date AS Available_to, @lasttime_to := end_date
        FROM (SELECT start_date, end_date
               FROM appointments
                WHERE shift_id = " . $shiftData->id . "
                AND end_date <= '" . $shiftData->end_date->toDateTimeString() . "'
                AND start_date >= '" . $shiftData->start_date->toDateTimeString() . "'
               UNION ALL
             SELECT '". $shiftData->start_date->toDateTimeString() ."', '". $shiftData->start_date->toDateTimeString() ."'
                   ORDER BY end_date 
        
             ) e
        JOIN (SELECT @lasttime_to := NULL) init) x
        WHERE Available_to > DATE_ADD(Available_from, INTERVAL " . $serviceDurations . " MINUTE)

这里面缺少什么？我怎样才能开始显示在查询中？

http://sqlfiddle.com/#!9/e5292d/2

Answer 1

首先，解决您的原始 SQL(在 fiddle 中)。

对于第一行，@lasttime_to为 NULL 并导致问题。

需要为空情况(第一行)有条件地使用“类次开始时间”。

参见 LAG另一种方法。这真的是@variable hack 正在尝试替换 LAG 之前在 MySQL 中可用。

试试这个:

SELECT Available_from, Available_to
  FROM (
    SELECT COALESCE(@lasttime_to, '2022-03-15 10:00:00') AS Available_from, start_date AS Available_to, @lasttime_to := end_date
    FROM (SELECT start_date, end_date
             FROM appointments
             WHERE shift_id = 4600
             AND end_date <= '2022-03-15 17:00:00'
             AND start_date >= '2022-03-15 10:00:00'
             UNION ALL
               SELECT '2022-03-15 17:00:00', '2022-03-15 17:00:00'
               ORDER BY start_date 
           ) e
    JOIN (SELECT @lasttime_to := NULL) init) x
    WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

结果:

<表类="s-表"><头>Available_fromAvailable_to<正文>2022-03-15 10:00:002022-03-15 10:30:002022-03-15 12:15:002022-03-15 15:00:002022-03-15 15:40:002022-03-15 17:00:00

这里还有一些窗口函数。我重新安排了逻辑以避免与您的类次开始/结束细节相关的多个魔术常量。然而，我并没有移除所有魔法。

我仍然更喜欢我删除的更动态的方法。

WITH shift (shift_start, shift_end) AS (
       SELECT '2022-03-15 10:00:00', '2022-03-15 17:00:00'
     )
   , e0 AS (
       SELECT shift_id, start_date, end_date
         FROM appointments
        WHERE shift_id = 4600
          AND end_date   <= (SELECT shift_end   FROM shift)
          AND start_date >= (SELECT shift_start FROM shift)
        UNION ALL
       SELECT 4600, shift_end, shift_end FROM shift
        ORDER BY start_date 
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
         CROSS JOIN shift
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

略有不同的版本:

WITH e0 AS (
       SELECT a.shift_id, a.start_date, a.end_date
            , s.start_date AS shift_start
            , s.end_date   AS shift_end
         FROM appointments AS a
         JOIN shifts       AS s
           ON a.shift_id = 4600
          AND a.shift_id = s.id
          AND a.end_date   <= s.end_date
          AND a.start_date >= s.start_date
        UNION
       SELECT id, end_date, end_date
            , start_date  , end_date
         FROM shifts WHERE id = 4600
     )
   , e AS (
      SELECT e0.*
           , LAG(end_date) OVER (PARTITION BY shift_id ORDER BY start_date) AS lasttime_to
        FROM e0
     )
SELECT shift_id, Available_from, Available_to
  FROM (
       SELECT shift_id
            , CAST(COALESCE(lasttime_to, shift_start) AS datetime) AS Available_from
            , start_date AS Available_to
         FROM e
      ) x
 WHERE Available_to > DATE_ADD(Available_from, INTERVAL 20 MINUTE)
;

The fiddle