如何用SQL查询调整日期范围解决药物供应区间重叠问题？

这个问题的核心是计算患者药物库存的连续覆盖周期，而非简单合并重叠区间——当患者在药物有效期内、有效期截止当日，或截止次日领取新批次药物时，都会延长连续供应的结束日期；只有当领取日期晚于上一次药物用完日期+1天时，才会开启新的连续区间。

解法步骤 #

1. 排序并标记记录：按领取日期start_dt排序，给每条记录分配行号，方便后续逐行计算累积有效期。
2. 计算累积结束日期：逐行计算每个批次领取后的总药物结束日期：
   • 第一条记录的结束日期为start_dt + days_supply - 1（包含start_dt在内共days_supply天）。
   • 后续记录：若领取日期在上一次累积结束日期或其次日范围内，则总结束日期为「上一次累积结束日期（或当前领取日期，取较晚者） + days_supply -1」；否则按新批次单独计算结束日期。
3. 标记新区间起点：当领取日期晚于上一次累积结束日期+1天时，标记为新的连续区间起点。
4. 分组合并区间：按标记的区间分组，取每组的最早领取日期和最晚累积结束日期，得到最终的连续供应区间。

示例SQL代码（以MySQL为例） #

WITH ranked_supply AS (
    -- 按领取日期排序并标记行号
    SELECT 
        start_dt,
        days_supply,
        ROW_NUMBER() OVER (ORDER BY start_dt) AS rn
    FROM Supply
),
cumulative_end AS (
    -- 计算每个批次领取后的累积结束日期
    SELECT 
        start_dt,
        days_supply,
        rn,
        CASE 
            WHEN rn = 1 THEN DATE_ADD(start_dt, INTERVAL days_supply - 1 DAY)
            ELSE 
                IF(
                    start_dt <= DATE_ADD(LAG(cumulative_end_dt) OVER (ORDER BY rn), INTERVAL 1 DAY),
                    DATE_ADD(
                        GREATEST(start_dt, LAG(cumulative_end_dt) OVER (ORDER BY rn)),
                        INTERVAL days_supply - 1 DAY
                    ),
                    DATE_ADD(start_dt, INTERVAL days_supply - 1 DAY)
                )
        END AS cumulative_end_dt
    FROM ranked_supply
),
interval_markers AS (
    -- 标记新的连续区间起点
    SELECT 
        start_dt,
        cumulative_end_dt,
        rn,
        CASE 
            WHEN rn = 1 THEN 1
            WHEN start_dt > DATE_ADD(LAG(cumulative_end_dt) OVER (ORDER BY rn), INTERVAL 1 DAY) THEN 1
            ELSE 0
        END AS is_new_interval
    FROM cumulative_end
),
grouped_intervals AS (
    -- 按区间分组
    SELECT 
        start_dt,
        cumulative_end_dt,
        SUM(is_new_interval) OVER (ORDER BY rn) AS interval_group
    FROM interval_markers
)
-- 输出最终的连续供应区间
SELECT 
    MIN(start_dt) AS start_dt,
    MAX(cumulative_end_dt) AS end_dt
FROM grouped_intervals
GROUP BY interval_group
ORDER BY start_dt;

结果验证 #

针对示例输入执行上述代码，将得到期望的输出：

适配其他数据库说明 #

• PostgreSQL：将DATE_ADD(xxx, INTERVAL n DAY)替换为xxx + INTERVAL 'n day'，GREATEST函数保持不变。
• SQL Server：将DATE_ADD(xxx, INTERVAL n DAY)替换为DATEADD(DAY, n, xxx)，GREATEST替换为MAX。

内容的提问来源于stack exchange，提问作者Whiskeyjack