调度医疗预约挑战

Question

我在做一个医疗预约系统项目。

获得医生的第一个空闲时间并为刚刚预约的病人安排这个时间的最佳方式是什么？

数据库结构：

任用表：

• doctor_id
• patient_id
• appointment_start_time
• appointment_end_time

doctors_working_hours表：

• doctor_id
• day_of_week
• start_time
• end_time

当病人要求预约医生时，我想根据医生的工作时间安排医生的下一个空闲时间。

每个约会的默认持续时间为15分钟。

做这件事最有效的方法是什么？

Answer 1

我要做的是类似于下面概述的过程。我留下了一些页面的链接，我觉得这很有帮助，我还留下了一些严格意义上不需要的字段。在我走的时候，我将概述我的思维过程。

CREATE并填充doctor和patient表：

CREATE TABLE doctor
(
  doctor_id   INTEGER NOT NULL AUTO_INCREMENT,
  doctor_name VARCHAR (50) NOT NULL,
  CONSTRAINT doctor_pk PRIMARY KEY (doctor_id)
);
INSERT INTO doctor (doctor_name)
VALUES ('Doctor_1'), ('Doctor_2'), ('Doctor_3'), ('Doctor_4');
SELECT * FROM doctor;

以及：

CREATE TABLE patient
(
  patient_id INTEGER NOT NULL AUTO_INCREMENT,
  patient_name VARCHAR (50),
  CONSTRAINT patient_pk PRIMARY KEY (patient_id)
);
INSERT INTO patient (patient_name)
VALUES ('p1'), ('p2'), ('p3'), ('p4'), ('p5'), ('p6'), ('p7'), ('p8'), ('p9'), ('p10'), 
('p11'), ('p12'), ('p13'), ('p14'), ('p15');
SELECT * FROM patient;

然后，我们必须构造一个日期范围-我选择了2019-10-10 09:00:00到2019-10-15 21:00:00。这个范围涵盖了医生不工作的一个周末。最好是用一个可管理的记录进行原型，而不是让自己被淹没。

我利用一个RECURSIVE CTE -非常强大和值得了解。PostgreSQL有一个GENERATE_SERIES函数，对于这类事情非常方便(参见PostgreSQL 小提琴，它重复了MySQL在这里所做的工作)。MariaDB有一个序列引擎，它也具有类似的功能。

-- https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql
WITH RECURSIVE date_range AS 
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts < STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
)
SELECT
  DATE_FORMAT(ts, '%a') AS DOW,
  WEEKDAY(ts), 
  WEEKDAY(ts) % 6,
  ts from date_range
WHERE CAST(ts AS DATE) >= CAST('2019-10-10 09:00:00' AS DATE) 
AND   CAST(ts AS DATE) <= CAST('2019-10-15 21:00:00' AS DATE)
-- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
AND DATE_FORMAT(ts, '%H:%i:%s') >= '09:00:00'
AND DATE_FORMAT(ts, '%H:%i:%s') <= '21:00:00'
AND DATE_FORMAT(ts, '%a') != 'Sat'
AND DATE_FORMAT(ts, '%a') != 'Sun';

结果(前10项记录)：

DOW WEEKDAY(ts) WEEKDAY(ts) % 6 ts
Thu 3   3   2019-10-10 09:00:00
Thu 3   3   2019-10-10 09:15:00
Thu 3   3   2019-10-10 09:30:00
Thu 3   3   2019-10-10 09:45:00
Thu 3   3   2019-10-10 10:00:00
Thu 3   3   2019-10-10 10:15:00
Thu 3   3   2019-10-10 10:30:00
Thu 3   3   2019-10-10 10:45:00
Thu 3   3   2019-10-10 11:00:00
Thu 3   3   2019-10-10 11:15:00
…
10 rows of 196

所以，现在我们有一系列的时间范围，我们想要的范围。但是，约会有start_time和end_time。

因此，我们在这个日期表上执行一个自定义( JOIN )：

首先，为了存储这些插槽时间，我们创建了一个表：

CREATE TABLE slot
(
  slot_id    INTEGER NOT NULL AUTO_INCREMENT,
  slot_begin TIMESTAMP NOT NULL,
  slot_end   TIMESTAMP NOT NULL,
  CONSTRAINT slot_pk PRIMARY KEY (slot_id),
  CONSTRAINT slot_begin_end_uq UNIQUE (slot_begin, slot_end)
);

然后以如下方式导出时隙数据：

-- https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql
WITH RECURSIVE date_range AS 
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts < STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
),
xrange AS
(
  SELECT
    DATE_FORMAT(ts, '%a') AS DOW,
    WEEKDAY(ts), 
    WEEKDAY(ts) % 6,
    ts from date_range
  WHERE CAST(ts AS DATE) >= CAST('2019-10-10 09:00:00' AS DATE) 
  AND   CAST(ts AS DATE) <= CAST('2019-10-15 21:00:00' AS DATE)
  -- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
  AND DATE_FORMAT(ts, '%H:%i:%s') >= '09:00:00'
  AND DATE_FORMAT(ts, '%H:%i:%s') <= '21:00:00'
  AND DATE_FORMAT(ts, '%a') != 'Sat'
  AND DATE_FORMAT(ts, '%a') != 'Sun'
)
SELECT t1.ts, t2.ts FROM xrange t1
JOIN xrange t2
  ON t1.ts = DATE_ADD(t2.ts, INTERVAL - 15 MINUTE);

结果(前10项记录)：

slot_begin          slot_end
2019-10-10 09:00:00 2019-10-10 09:15:00
2019-10-10 09:15:00 2019-10-10 09:30:00
2019-10-10 09:30:00 2019-10-10 09:45:00
2019-10-10 09:45:00 2019-10-10 10:00:00
2019-10-10 10:00:00 2019-10-10 10:15:00
2019-10-10 10:15:00 2019-10-10 10:30:00
2019-10-10 10:30:00 2019-10-10 10:45:00
2019-10-10 10:45:00 2019-10-10 11:00:00
2019-10-10 11:00:00 2019-10-10 11:15:00
2019-10-10 11:15:00 2019-10-10 11:30:00
…
10 rows of 192

此外，我们向下滚动到最后，检查是否存在边界问题：

2019-10-15 20:15:00 2019-10-15 20:30:00
2019-10-15 20:30:00 2019-10-15 20:45:00
2019-10-15 20:45:00 2019-10-15 21:00:00
192 rows

所以太完美了。我们可以看到最后一个插槽是从20:45到21:00，这是想要的结果。

--这个语法的起源在下面的一个链接中得到了解释--看看PostgreSQL --小提琴，看看它应该怎么做！

INSERT INTO slot (slot_begin, slot_end)
WITH RECURSIVE date_range AS 
(
  SELECT STR_TO_DATE('2019-10-10 09:00:00','%Y-%m-%d %H:%i:%s') AS ts
  UNION  ALL
  SELECT ts + INTERVAL 15 MINUTE
  FROM   date_range
  WHERE  ts < STR_TO_DATE('2019-10-15 21:00:00','%Y-%m-%d %H:%i:%s')
),
xrange AS
(
  SELECT
    DATE_FORMAT(ts, '%a') AS DOW,
    WEEKDAY(ts), 
    WEEKDAY(ts) % 6,
    ts from date_range
  WHERE CAST(ts AS DATE) >= CAST('2019-10-10 09:00:00' AS DATE) 
  AND   CAST(ts AS DATE) <= CAST('2019-10-15 21:00:00' AS DATE)
  -- https://stackoverflow.com/questions/12337195/how-to-part-date-and-time-from-datetime-in-mysql
  AND DATE_FORMAT(ts, '%H:%i:%s') >= '09:00:00'
  AND DATE_FORMAT(ts, '%H:%i:%s') <= '21:00:00'
  AND DATE_FORMAT(ts, '%a') != 'Sat'   -- <<======== no work on the weekends
  AND DATE_FORMAT(ts, '%a') != 'Sun'   -- <<========
)
SELECT t1.ts, t2.ts FROM xrange t1
JOIN xrange t2
  ON t1.ts = DATE_ADD(t2.ts, INTERVAL - 15 MINUTE);

只是一个快速检查-有各种边界条件的问题-始终检查当你走！

SELECT -- A check - I had a few wierd errors and this helped!
       -- There are 4 slots/hour over the whole day 12 working
       -- hours (docs do shifts of 8 hours, so, in the case of 4 days = 48 slots!
    COUNT(DAYNAME(s.slot_begin)) AS cnt,
    DATE_FORMAT(s.slot_begin, '%a') AS the_day
FROM slot s
GROUP BY  DATE_FORMAT(s.slot_begin, '%a');

结果：

cnt the_day
48  Thu
48  Fri
48  Mon
48  Tue

因此，这是12个工作小时/天4时隙/小时= 48 -到目前为止，还不错。

-- Double checking - in particular the boundaries between the days,
-- making sure there are no problems with edge cases.

SELECT  DATE_FORMAT(s.slot_begin, '%a') AS the_day, WEEKDAY(s.slot_begin) AS dow, 
        DATE_FORMAT(slot_begin, '%Y-%m-%d %H:%i') AS start, 
        DATE_FORMAT(slot_end,   '%Y-%m-%d %H:%i') AS end
FROM slot s -- WORKS!
ORDER BY slot_begin;

-- https://stackoverflow.com/questions/6718759/how-to-select-date-and-time-without-the-seconds-in-mysql
-- SELECT DATE_FORMAT(`date`, '%Y-%m-%d %H:%i')`date`, '%Y-%m-%d %H:%i')
-- Result  No days x 12 hours x 4 15 minute slots (4 days = 192 = 4 x 48) 

Result:

the_day dow start   end
Thu 3   2019-10-10 09:00    2019-10-10 09:15
Thu 3   2019-10-10 09:15    2019-10-10 09:30
Thu 3   2019-10-10 09:30    2019-10-10 09:45
Thu 3   2019-10-10 09:45    2019-10-10 10:00
Thu 3   2019-10-10 10:00    2019-10-10 10:15
Thu 3   2019-10-10 10:15    2019-10-10 10:30
Thu 3   2019-10-10 10:30    2019-10-10 10:45
Thu 3   2019-10-10 10:45    2019-10-10 11:00
Thu 3   2019-10-10 11:00    2019-10-10 11:15
Thu 3   2019-10-10 11:15    2019-10-10 11:30
…
10 rows of 192

4x48=192个✓结束条件也检查-没有显示。

我们现在创建了一个Associative Entity --更著名的是医生和插槽之间的连接或连接表。

CREATE TABLE doc_slot
(
  slot_id   INTEGER NOT NULL,
  doctor_id INTEGER NOT NULL,
  CONSTRAINT doc_slot_pk PRIMARY KEY (doctor_id, slot_id),
  CONSTRAINT ds_doctor_id_fk FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id),
  CONSTRAINT ds_slot_id_fk   FOREIGN KEY (slot_id)   REFERENCES slot   (slot_id)
);

然后用一个CROSS JOIN把它弄平。

因此，医生1和2都与每一个可能的时段联系在一起，从09:00到17:45 --也就是说，工作到18:00。他们的午餐时间也是从13点到14点。

-- A CROSS JOIN between slot and doctor for doctors 1 & 2. They work from 09:00 till
-- 18:00 - last appointment at 17:45. They also have an hour off between 13:00
-- and 14:00 - no appointments!
INSERT INTO doc_slot (slot_id, doctor_id)  -- no. of days x 32/slots/day x 2 docs (ids 1 & 2)
SELECT s.slot_id, d.doctor_id
FROM  slot s, doctor d
WHERE EXTRACT(HOUR FROM s.slot_begin) BETWEEN 9 AND 17  -- 17:45 last appt.
AND   EXTRACT(HOUR FROM s.slot_begin) != 13  -- no appts. between 13 and 13:45 - lunch
AND   d.doctor_id IN (1, 2)                  -- docs 1 & 2 work 09 - 18
ORDER BY slot_begin, doctor_id;

现在，我们也为医生做同样的工作，从12:00到20:45 -- 21:00完成工作。他们还可以从16点到17点休息一个小时。

-- For a bit of realism (or maybe not! - variety is the...), the other two 
-- doctors (3 $ 4) are scheduled to work from 12:00 to 21:00 - last appointment
-- 20:45 - and a break between 16:00 and 17:00. Legistation/practices &c. may
-- vary from country to country.

INSERT INTO doc_slot (slot_id, doctor_id)  -- no. of days x 32/slots/day x 2 docs (ids 3 & 4)
SELECT s.slot_id, d.doctor_id              -- = 256 for 4 days
FROM  slot s, doctor d
WHERE EXTRACT(HOUR FROM s.slot_begin) BETWEEN 12 AND 20  -- 20:45 last appt.
AND   EXTRACT(HOUR FROM s.slot_begin) != 16  -- no appts. between 16 and 16:45
AND   d.doctor_id IN (3, 4)                  -- docs 3 & 4 work 12 - 21
ORDER BY slot_begin, doctor_id;

这两位INSERTs的支票是在小提琴里给的。

现在，我们CREATE一个约会表：

CREATE TABLE appointment 
(
  appointment_id SERIAL,
  doctor_id INTEGER NOT NULL,
  patient_id INTEGER NOT NULL,
  appt_begin_time TIMESTAMP NOT NULL,
  appt_end_time TIMESTAMP NOT NULL,
  CONSTRAINT app_pk PRIMARY KEY (appointment_id),
  CONSTRAINT app_doc_uq UNIQUE (doctor_id, appt_begin_time),  -- a doc can only have one appt at a time
  -- CONSTRAINT app_pat_uq UNIQUE (patient_id, appt_begin_time), -- a pat can only have one appt at a time
  -- because of the use of the RAND function, this constraint can only be enabled later!
  CONSTRAINT app_doc_fk FOREIGN KEY (doctor_id) REFERENCES doctor (doctor_id)  -- but this will have to wait
);

人口：

-- I got this strange syntax here:
-- https://mydbops.wordpress.com/2019/01/06/common-table-expression-cte-mysql-8-0/
-- Also useful:
-- https://www.techonthenet.com/mysql/functions/rand.php (RAND())
INSERT appointment (doctor_id, patient_id, appt_begin_time, appt_end_time)
WITH cte1 AS
(
  SELECT             -- the no of doc_slots = no. of days x 32/slots/day x 4 docs
    d.doctor_id AS did,
    FLOOR(RAND()*(15 - 1 + 1)) + 1 AS p_no, 
    -- DATE_TRUNC('MINUTE', s.slot_begin), 
    -- TO_CHAR(s.slot_begin,'YYYY-MM-DD HH24:MM') AS t_mins,
    s.slot_begin AS a_begin,
    s.slot_end AS a_end  
    -- s.slot_id,       -- (= 256 for 4 days) 

    -- ds.slot_id AS ds_sid, ds.doctor_id AS ds_did,
    -- d.doctor_id AS d_did, d.doctor_name AS d_name
  FROM slot s
  JOIN doc_slot ds
    ON s.slot_id = ds.slot_id
  JOIN doctor d
    ON ds.doctor_id = d.doctor_id
  ORDER BY ds.slot_id, ds.doctor_id
)
SELECT * FROM cte1;

现在，我们的预约表中填充了医生和时隙+随机病人(FLOOR(RAND()*(15 - 1 + 1)) + 1 AS p_no,)的所有可能组合。

由于patient_id的随机性，有一些重复的patient_id和约会start_time。我们需要删除这些，首先我们需要识别它们。为此，我们使用ROW_NUMBER()函数。

-- Because of the RAND function, some patients have two appointments at the same time.
-- We need to get rid of these duplicates - scroll down and you'll see some records
-- where rn_p is > 1 - these are duplicate appointment times for the same patient.

SELECT
  appointment_id,
  appt_begin_time,
  doctor_id,
  patient_id,
  ROW_NUMBER() OVER (PARTITION BY appt_begin_time, patient_id
                         ORDER BY appointment_id) AS rn_p
FROM appointment
ORDER BY appointment_id;

向下滚动的结果显示了一些rn_p =2(有时甚至更多)的记录。

-- Now to "cleanse" the appoinment table of duplicates!
-- i.e. patients who (from the RANDOM() above have 2 or more appointments at the same time)
-- Don't have to worry about the doctors because they've been assigned via the 
-- CROSS JOIN.
DELETE FROM appointment
WHERE appointment_id IN
(
  SELECT t1.appointment_id
  FROM
  (
    SELECT
      appointment_id,
      appt_begin_time,
      doctor_id,
      patient_id,
      ROW_NUMBER() OVER (PARTITION BY appt_begin_time, patient_id
                         ORDER BY appointment_id) AS rn_p
    FROM appointment
    ORDER BY appointment_id  
  ) AS t1 WHERE rn_p > 1
);

一个快速的SELECT (未显示，请参阅小提琴)显示，大约40条记录(平均)是以这种方式消除的。

现在我们可以添加一个UNIQUE约束如下：

ALTER TABLE appointment 
ADD CONSTRAINT appt_patient_appt_begin_uq UNIQUE(patient_id, appt_begin_time);

约会表现在将被剪除。大约有40条记录(在512个CROSS JOIN中)已经被删除，但是我们需要删除更多的记录，以便为我们的查询提供合理数量的可用约会。这就需要在SELECT中建立一个奇怪的嵌套DELETE --有关详细信息，请参阅链接：

-- The convoluted nested SELECT syntax below was explained here:
-- https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause
-- I got it by searching for
-- "mysql  - You can't specify target table 'appointment' for update in FROM clause"
-- take a look at the PostgreSQL fiddle for how it **_SHOULD_** be done!

DELETE FROM appointment 
WHERE appointment_id IN
(
  SELECT appointment_id FROM
  (
    SELECT 
      appointment_id FROM appointment   -- takes out approx 25 rows - will vary because 
    WHERE                               -- of RANDOM() for patient_id - you can vary this
     -- MOD(appointment_id, 2)  = 0     -- by removing the commented MOD()s.
     -- OR MOD(appointment_id, 3)  = 0 
     -- OR MOD(appointment_id, 4)  = 0
     -- OR MOD(appointment_id, 5)  = 0
     -- OR MOD(appointment_id, 7)  = 0
     -- OR MOD(appointment_id, 11) = 0
     -- MOD(appointment_id, 18) = 0
     appointment_id % 23 = 0
   ) AS t1
);

可以通过添加或删除DELETE条件来更改WHERE d记录的数量。

所以，现在，我们想要删除与appointment表中的预约相对应的插槽，否则就有可能为给定的start_time双预订医生。

-- Here we delete the doc_slots corresponding to the appointments which
-- are filled! This necessitates JOINing to the slot table (for the begin_time)
-- and then deleting the corresponding doc_slots - ~ 450/512 with 
-- MOD(appointment_id, 23) = 0 above. You can increase the amount of
-- available appoinments by adding more MODs to the WHERE conditions.

DELETE FROM doc_slot ds
WHERE (ds.slot_id, ds.doctor_id) IN
(
  SELECT s_id, ds_did FROM
  (
    SELECT  
      a.appt_begin_time, a.doctor_id, 
      t1.s_id, t1.ds_did 
    FROM appointment a 
    JOIN 
    ( 
      SELECT  
        ds.slot_id AS ds_sid, ds.doctor_id AS ds_did,  
        s.slot_id AS s_id, s.slot_begin AS s_begin  
      FROM   
        doc_slot ds  
        JOIN slot s ON ds.slot_id = s.slot_id   
      ORDER BY s.slot_begin, ds.doctor_id  
    )  t1  
    ON a.appt_begin_time = t1.s_begin  
    AND a.doctor_id = t1.ds_did
  ) AS t2
);

这个过程通常会在doc_slot表中留下大约45-55条记录供将来的预订使用。

-- Finally, the answer to the question!

SELECT 
  d.doctor_name, ds.doctor_id ds_di, ds.slot_id ds_is,
  s.slot_id, s.slot_begin
FROM doc_slot ds
  JOIN slot s ON ds.slot_id = s.slot_id
  JOIN doctor d ON ds.doctor_id = d.doctor_id
WHERE CAST(s.slot_begin AS DATE) = CAST('2019-10-11' AS DATE)
-- AND d.doctor_name = 'Whatever' 
-- many people will have a regular/preferred doctor
-- depending on the circumstances.
ORDER BY s.slot_begin ASC, ds.doctor_id ASC;

-- Even though ASC is the default ORDER BY, I included it for clarity,
-- So, we have ~ 15 - 20 appointments with app % 23 and 4 days and 4 doctors

结果(将根据RAND()的输出而变化)：

doctor_name ds_di   ds_is   slot_id slot_begin
Doctor_2    2       53      53      2019-10-11 10:00:00
Doctor_4    4       62      62      2019-10-11 12:15:00
Doctor_1    1       63      63      2019-10-11 12:30:00
Doctor_4    4       70      70      2019-10-11 14:15:00
Doctor_4    4       75      75      2019-10-11 15:30:00
Doctor_3    3       76      76      2019-10-11 15:45:00
Doctor_3    3       81      81      2019-10-11 17:00:00
Doctor_2    2       84      84      2019-10-11 17:45:00
Doctor_3    3       95      95      2019-10-11 20:30:00
9 rows

因此，当您有一个允许手术人员预约预约的应用程序时，您将不得不在一个事务中执行两个操作。

1) CREATE appointment表中的记录

和

2) DELETE doc_slot表中相应的记录。

我不知道预订未来约会的限制是什么，但是一旦您的系统设置完毕，每当您添加一组新的时隙时，就可以使用新的可能可用的时间填充doc_slot表。然后，这些doc_slot记录将被移动到appointment表中，并在预订新约会时从doc_slot表中删除。您的应用程序应该允许医生将自己标记为不可用。

将数据保存在appointment和doc_slot时隙表中供以后的分析使用可能是有用的(当可能没有显示、繁忙/非繁忙期--其他条件时)。

你也可以搜索open source doctor appointment software，看看他们是如何做到的--这就是F/LOSS的美妙之处--你可以选择、混合和借用一些想法。