如何用Oracle SQL计算每位讲师的最大并发授课量？

可以通过Oracle SQL实现该需求，以下是具体实现方案：

需求说明 #

计算每位讲师的最大并发授课数，只要两门课程的时间范围存在至少1天重叠，即视为并发。

数据准备（用户提供的脚本） #

CREATE TABLE instructor_schedule(
    instructor_id VARCHAR2(5), 
    course_id VARCHAR2(5), 
    course_start_dt DATE, 
    course_end_dt DATE
);

-- 插入讲师I1的课程数据
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I1', 'C1', TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), TO_DATE('30-JAN-2022', 'DD-MON-YYYY'));
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I1', 'C2', TO_DATE('25-DEC-2021', 'DD-MON-YYYY'), TO_DATE('15-JAN-2022', 'DD-MON-YYYY'));
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I1', 'C3', TO_DATE('25-JAN-2022', 'DD-MON-YYYY'), TO_DATE('05-FEB-2022', 'DD-MON-YYYY'));
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I1', 'C4', TO_DATE('26-JAN-2022', 'DD-MON-YYYY'), TO_DATE('26-JAN-2022', 'DD-MON-YYYY'));
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I1', 'C5', TO_DATE('01-MAR-2022', 'DD-MON-YYYY'), TO_DATE('05-MAR-2022', 'DD-MON-YYYY'));

-- 插入讲师I2的课程数据
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I2', 'C1', TO_DATE('20-AUG-2022', 'DD-MON-YYYY'), TO_DATE('22-AUG-2022', 'DD-MON-YYYY'));
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I2', 'C2', TO_DATE('03-SEP-2022', 'DD-MON-YYYY'), TO_DATE('04-SEP-2022', 'DD-MON-YYYY'));
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I2', 'C3', TO_DATE('02-SEP-2022', 'DD-MON-YYYY'), TO_DATE('02-SEP-2022', 'DD-MON-YYYY'));
INSERT INTO instructor_schedule(instructor_id, course_id, course_start_dt, course_end_dt) 
VALUES('I2', 'C4', TO_DATE('01-SEP-2022', 'DD-MON-YYYY'), TO_DATE('05-SEP-2022', 'DD-MON-YYYY'));

实现方案1：关联统计法 #

通过自关联查询，统计每门课程对应的并发课程数，再取每个讲师的最大值：

SELECT 
    instructor_id,
    MAX(concurrent_courses) AS max_concurrent_capacity
FROM (
    SELECT 
        s1.instructor_id,
        COUNT(s2.course_id) AS concurrent_courses
    FROM instructor_schedule s1
    JOIN instructor_schedule s2 
        ON s1.instructor_id = s2.instructor_id
        -- 判断时间重叠：s2的开始<=s1的结束，且s2的结束>=s1的开始
        AND s2.course_start_dt <= s1.course_end_dt
        AND s2.course_end_dt >= s1.course_start_dt
    GROUP BY s1.instructor_id, s1.course_id
) t
GROUP BY instructor_id;

实现方案2：事件点累计法（更高效） #

将课程的开始/结束转化为事件点，通过窗口函数累计并发数，取最大值：

WITH events AS (
    -- 课程开始：并发数+1
    SELECT instructor_id, course_start_dt AS event_dt, 1 AS delta FROM instructor_schedule
    UNION ALL
    -- 课程结束次日：并发数-1（结束当天仍算并发）
    SELECT instructor_id, course_end_dt + 1 AS event_dt, -1 AS delta FROM instructor_schedule
),
sorted_events AS (
    SELECT 
        instructor_id,
        event_dt,
        -- 按时间累计并发数
        SUM(delta) OVER (PARTITION BY instructor_id ORDER BY event_dt) AS concurrent
    FROM events
)
SELECT 
    instructor_id,
    MAX(concurrent) AS max_concurrent_capacity
FROM sorted_events
GROUP BY instructor_id;

结果验证 #

执行上述SQL后，将得到以下结果：

• 讲师I1的最大并发出现在1月25日-1月26日，涉及课程C1、C3、C4
• 讲师I2的最大并发出现在9月3日-9月4日（涉及C2、C4）及9月2日（涉及C3、C4），最终最大并发数为3

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