求医院最大并行研究容量的高效Oracle SQL实现方案咨询

需要计算每个医疗机构（医院）可同时开展的最大并行研究项目数量，判定规则为：两项研究只要存在1天时间重叠即视为并行。

例如测试数据中医院I1存在两批重叠研究：第一批4项重叠，第二批2项，因此最大并行容量为4。

测试数据创建脚本 #

CREATE TABLE TEST_INST_DT(INST_ID VARCHAR2(10), STUDY_ID VARCHAR2(10), STUDY_START_DATE DATE, STUDY_END_DATE DATE);

-- 第一批重叠（4项研究）
INSERT INTO TEST_INST_DT VALUES('I1', 'S1', TO_DATE('31-DEC-2021', 'DD-MON-YYYY'), TO_DATE('02-JAN-2022', 'DD-MON-YYYY'));
INSERT INTO TEST_INST_DT VALUES('I1', 'S2', TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), TO_DATE('05-JAN-2022', 'DD-MON-YYYY'));
INSERT INTO TEST_INST_DT VALUES('I1', 'S3', TO_DATE('02-JAN-2022', 'DD-MON-YYYY'), TO_DATE('03-JAN-2022', 'DD-MON-YYYY'));
INSERT INTO TEST_INST_DT VALUES('I1', 'S4', TO_DATE('04-JAN-2022', 'DD-MON-YYYY'), TO_DATE('10-JAN-2022', 'DD-MON-YYYY'));

-- 第二批重叠（2项研究）
INSERT INTO TEST_INST_DT VALUES('I1', 'S5', TO_DATE('01-FEB-2022', 'DD-MON-YYYY'), TO_DATE('05-FEB-2022', 'DD-MON-YYYY'));
INSERT INTO TEST_INST_DT VALUES('I1', 'S6', TO_DATE('02-FEB-2022', 'DD-MON-YYYY'), TO_DATE('03-FEB-2022', 'DD-MON-YYYY'));

高效Oracle SQL实现方案 #

方法：基于事件点的累加计算 #

这种方法避免了低效的自连接，通过事件点转换+窗口函数实现，适合大数据量场景：

WITH event_points AS (
    -- 研究开始：新增1项并行研究
    SELECT 
        INST_ID,
        STUDY_START_DATE AS event_date,
        1 AS event_change
    FROM TEST_INST_DT
    UNION ALL
    -- 研究结束次日：减少1项并行研究（结束当天仍算重叠）
    SELECT 
        INST_ID,
        STUDY_END_DATE + 1 AS event_date,
        -1 AS event_change
    FROM TEST_INST_DT
),
running_totals AS (
    -- 按日期累加事件变化，得到每个时间点的并行研究数
    SELECT 
        INST_ID,
        event_date,
        SUM(event_change) OVER (PARTITION BY INST_ID ORDER BY event_date) AS concurrent_studies
    FROM event_points
)
-- 取每个医院的最大并行数
SELECT 
    INST_ID,
    MAX(concurrent_studies) AS max_concurrent_capacity
FROM running_totals
GROUP BY INST_ID;

执行结果 #

针对测试数据，返回结果如下：

核心原理 #

1. 事件点转换：将研究开始日期标记为+1（新增并行），结束日期次日标记为-1（停止并行），确保结束当天仍被计入重叠范围。
2. 窗口累加：通过SUM(...) OVER (PARTITION BY INST_ID ORDER BY event_date)按日期顺序累加事件值，实时计算当前并行研究数量。
3. 取最大值：对每个医院的并行数取最大值，即为该医院的最大并行研究容量。

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