SQL查询问题（预订软件）

Question

SQL查询问题（预订软件）

3

我有以下的代码段。

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cd3e6d07208978a987503953b29a1b7e


CREATE TABLE shifts (
    id int NOT NULL,
    unique_id varchar(255) NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE appointments (
    id int NOT NULL,
    unique_id varchar(255) NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    shift_id int NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (shift_id) REFERENCES shifts(id)
);

INSERT INTO `shifts` (`id`, `unique_id`, `start_date`, `end_date`)
VALUES
    ('4596', '614fc0c5dab2485bba8cbbd9eb98aa53', '2022-02-28 10:00:00', '2022-03-15 19:00:00');


INSERT INTO `appointments` (`id`, `unique_id`, `start_date`, `end_date`, `shift_id`)
VALUES
    (18352, 'accdc12943954ee2bd69ad116c2fef3d', '2022-02-28 18:15:00', '2022-02-28 19:00:00', 4596),
    (18351, '6a0d56ad43894b60b4f8a289fbbfff73', '2022-02-28 17:30:00', '2022-02-28 18:15:00', 4596),
    (18545, '4a49c054e59e4514ae2a521b55a7715c', '2022-02-28 16:50:00', '2022-02-28 17:15:00', 4596),
    (18622, '02ca48b35ca3462f9030bcb97ea1dbab', '2022-02-28 16:30:00', '2022-02-28 16:50:00', 4596),
    (18544, 'd27bc7ba34a74f078966ebe1567d3181', '2022-02-28 16:00:00', '2022-02-28 16:30:00', 4596),
    (18685, '4057b87c089b463188a26bc45d85388a', '2022-02-28 15:00:00', '2022-02-28 15:10:00', 4596),
    (18462, '3270929ae6e546fb824e6d3219917b8e', '2022-02-28 14:30:00', '2022-02-28 16:00:00', 4596),
    (18235, 'deac969701ae47f0a78696f12c267475', '2022-02-28 14:00:00', '2022-02-28 14:30:00', 4596),
    (18540, 'e16af8a053594e8b8340ede302398a22', '2022-02-28 13:30:00', '2022-02-28 14:00:00', 4596),
    (18537, 'e1946add03cd412da357b58c93d58c6e', '2022-02-28 12:30:00', '2022-02-28 13:15:00', 4596),
    (18355, 'ec570f9365bb4ad68c7b5ab3b7d9aeea', '2022-02-28 12:00:00', '2022-02-28 12:30:00', 4596),
    (18523, 'aff5abe6e8f84a64ace3277d3cd6dbd1', '2022-02-28 11:00:00', '2022-02-28 12:00:00', 4596),
    (18517, '124baaa1aeab421cb288f2b7c7abe89b', '2022-02-28 10:30:00', '2022-02-28 11:00:00', 4596),
    (18382, '1371e41a7cf342bfa279a8b120cc5f43', '2022-02-28 10:00:00', '2022-02-28 10:30:00', 4596);



SELECT Available_from, Available_to
  FROM (
    SELECT COALESCE(@lasttime_to, '2022-02-28 10:00:00') AS Available_from, start_date AS Available_to, @lasttime_to := end_date
    FROM (SELECT start_date, end_date
             FROM appointments
             WHERE end_date <= '2022-02-28 19:00:00'
             AND start_date >= '2022-02-28 10:00:00'
             UNION ALL (
               SELECT '2022-02-28 19:00:00', '2022-02-28 19:00:00'
             )
             UNION ALL (
               SELECT '2022-02-28 10:00:00', '2022-02-28 10:00:00'
             )
               ORDER BY start_date, end_date
           ) e
    JOIN (SELECT @lasttime_to := NULL) init) x
    WHERE Available_to > DATE_ADD(Available_from, INTERVAL 14 MINUTE);

我该如何让这个查询忽略像这种情况一样的边缘案例？

2022-02-28 13:15:00 2022-02-28 13:30:00
2022-02-28 15:10:00 2022-02-28 16:00:00 ( EDGE CASE )
2022-02-28 17:15:00 2022-02-28 17:30:00

这是由于持续时间为

'2022-02-28 15:00:00' TO '2022-02-28 15:10:00'

理想结果：

2022-02-28 13:15:00 2022-02-28 13:30:00
2022-02-28 17:15:00 2022-02-28 17:30:00

- Filip Stojkovski

如果您正在使用MySQL 8.x，则应该使用窗口函数而不是@lasttime_to。 - Barmar

4

什么是"边缘案例"？请解释一下这个查询的目的是什么，为什么那一行不应该包括在内？"边缘案例"指的是极端、不寻常的情况。这个查询的目的是什么需要进一步了解上下文才能确定，但是那一行不应该被包括在内可能是因为它不符合特定条件或者被认为是一个异常值。 - Barmar

这个查询应该获取当天的空闲时间。它说16是一个空闲时间段，但实际上有一个预约在那个时间。 - Filip Stojkovski

约会可以从一分钟到八个小时，所以很自由。 - Filip Stojkovski

你可能认为，可以通过检查start_date和end_date之间的差异来识别空闲的“插槽”，但是您提供的示例似乎没有特定顺序，如果按id排序，则存在重叠，例如id 18352作为'2022-02-28 19:00:00'的end_date，而18355的开始时间为'2022-02-28 12:00:00'，导致了-7小时的负差异。您的示例有问题吗？还是您没有告诉我们某些信息？ - P.Salmon

显示剩余4条评论

1个回答

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

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

- Serg · Answer 1

您可以使用有关所讨论时间间隔内所有分钟的时间戳计数来找到空闲分钟并将它们分组为可用间隔。

with t1(c) as (
   select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
), dtally as (
   select  TIMESTAMPADD(minute, row_number() over() - 1, '2022-02-28 10:00:00') t,  row_number() over() rn
   from t1, t1 as t2, t1 as t3, t1 as t4
), freeMins as ( 
   select *, rn - row_number() over(order by rn)  grp
   from dtally
   where t <= '2022-02-28 19:00:00'
   and not exists (
          select 1 
          from appointments a
          where t between a.start_date and a.end_date)
)
select min(t) Available_from, max(t) Available_to
from freeMins
group by grp

返回

Available_from  Available_to
2022-02-28 13:16:00 2022-02-28 13:29:00
2022-02-28 17:16:00 2022-02-28 17:29:00

db<>fiddle

程序设计相关。