基于入院/出院时间的IPD床位特定时点占用及单日空置时长查询需求

假设我们有一张名为bed_occupancy的表，结构如下：

• bed_no：床位编号（INT）
• admission_datetime：入院时间（DATETIME）
• discharge_datetime：出院时间（DATETIME，可为NULL，代表未出院）

基于这个表结构，以下是两个需求的具体实现方案：

思路 #

先枚举全部100张床位，再判断每个床位在目标时间点是否存在未出院的入住记录（即入院时间≤目标时间点，且出院时间≥目标时间点或出院时间为空），同时确保目标时间点落在指定月份内。

实现SQL（以MySQL为例） #

-- 生成1-100的床位列表（若有单独的床位维度表可直接替换）
WITH all_beds AS (
    SELECT 1 AS bed_no 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 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL
    SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL
    SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
    SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL
    SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL
    SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL
    SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL
    SELECT 43 UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL
    SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL
    SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL
    SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL
    SELECT 59 UNION ALL SELECT 60 UNION ALL SELECT 61 UNION ALL SELECT 62 UNION ALL
    SELECT 63 UNION ALL SELECT 64 UNION ALL SELECT 65 UNION ALL SELECT 66 UNION ALL
    SELECT 67 UNION ALL SELECT 68 UNION ALL SELECT 69 UNION ALL SELECT 70 UNION ALL
    SELECT 71 UNION ALL SELECT 72 UNION ALL SELECT 73 UNION ALL SELECT 74 UNION ALL
    SELECT 75 UNION ALL SELECT 76 UNION ALL SELECT 77 UNION ALL SELECT 78 UNION ALL
    SELECT 79 UNION ALL SELECT 80 UNION ALL SELECT 81 UNION ALL SELECT 82 UNION ALL
    SELECT 83 UNION ALL SELECT 84 UNION ALL SELECT 85 UNION ALL SELECT 86 UNION ALL
    SELECT 87 UNION ALL SELECT 88 UNION ALL SELECT 89 UNION ALL SELECT 90 UNION ALL
    SELECT 91 UNION ALL SELECT 92 UNION ALL SELECT 93 UNION ALL SELECT 94 UNION ALL
    SELECT 95 UNION ALL SELECT 96 UNION ALL SELECT 97 UNION ALL SELECT 98 UNION ALL
    SELECT 99 UNION ALL SELECT 100
),
target_info AS (
    SELECT '2024-05-15 14:30:00' AS target_datetime -- 替换为目标时间点
)
SELECT
    ab.bed_no,
    CASE
        WHEN bo.bed_no IS NOT NULL THEN '满'
        ELSE '空'
    END AS occupancy_status
FROM all_beds ab
CROSS JOIN target_info ti
LEFT JOIN bed_occupancy bo
    ON ab.bed_no = bo.bed_no
    AND bo.admission_datetime <= ti.target_datetime
    AND (bo.discharge_datetime >= ti.target_datetime OR bo.discharge_datetime IS NULL)
WHERE
    MONTH(ti.target_datetime) = MONTH(bo.admission_datetime)
    OR bo.bed_no IS NULL;

思路 #

1. 确定目标日期的时间范围：@target_date 00:00:00 至 @target_date 23:59:59，总时长为86400秒（24小时）。
2. 计算每个床位在该时间段内的占用时长：根据入住记录与目标时间段的重叠关系，取实际重叠的时间区间计算时长。
3. 空置时长 = 总时长 - 占用时长；无占用记录的床位空置时长为24小时。

实现SQL（以MySQL为例） #

WITH all_beds AS (
    SELECT 1 AS bed_no 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 UNION ALL SELECT 10 UNION ALL
    SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL
    SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL
    SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
    SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL
    SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL
    SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL
    SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL
    SELECT 43 UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL
    SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL
    SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL
    SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL
    SELECT 59 UNION ALL SELECT 60 UNION ALL SELECT 61 UNION ALL SELECT 62 UNION ALL
    SELECT 63 UNION ALL SELECT 64 UNION ALL SELECT 65 UNION ALL SELECT 66 UNION ALL
    SELECT 67 UNION ALL SELECT 68 UNION ALL SELECT 69 UNION ALL SELECT 70 UNION ALL
    SELECT 71 UNION ALL SELECT 72 UNION ALL SELECT 73 UNION ALL SELECT 74 UNION ALL
    SELECT 75 UNION ALL SELECT 76 UNION ALL SELECT 77 UNION ALL SELECT 78 UNION ALL
    SELECT 79 UNION ALL SELECT 80 UNION ALL SELECT 81 UNION ALL SELECT 82 UNION ALL
    SELECT 83 UNION ALL SELECT 84 UNION ALL SELECT 85 UNION ALL SELECT 86 UNION ALL
    SELECT 87 UNION ALL SELECT 88 UNION ALL SELECT 89 UNION ALL SELECT 90 UNION ALL
    SELECT 91 UNION ALL SELECT 92 UNION ALL SELECT 93 UNION ALL SELECT 94 UNION ALL
    SELECT 95 UNION ALL SELECT 96 UNION ALL SELECT 97 UNION ALL SELECT 98 UNION ALL
    SELECT 99 UNION ALL SELECT 100
),
target_period AS (
    SELECT
        '2024-05-15 00:00:00' AS period_start,
        '2024-05-15 23:59:59' AS period_end,
        86400 AS total_seconds
)
SELECT
    ab.bed_no,
    SEC_TO_TIME(
        tp.total_seconds - COALESCE(SUM(
            TIMESTAMPDIFF(SECOND,
                GREATEST(bo.admission_datetime, tp.period_start),
                LEAST(COALESCE(bo.discharge_datetime, tp.period_end), tp.period_end)
            )
        ), 0)
    ) AS vacant_duration
FROM all_beds ab
CROSS JOIN target_period tp
LEFT JOIN bed_occupancy bo
    ON ab.bed_no = bo.bed_no
    AND bo.admission_datetime <= tp.period_end
    AND (bo.discharge_datetime >= tp.period_start OR bo.discharge_datetime IS NULL)
GROUP BY ab.bed_no, tp.total_seconds
ORDER BY ab.bed_no;

内容的提问来源于stack exchange，提问作者Asif Mahmood Meo