基于SQL实现按小时统计学生不可用人数

需求说明 #

需要制作报表，基于课程表统计指定小时段内不可用的学生人数：

• 若学生课程时段为8:00-9:15，需同时计入「8:00-9:00」和「9:00-10:00」两个时段的不可用人数
• 需处理7:30-15:30这类跨多个小时的课程，确保每个被覆盖的小时段都统计到该学生
• 最终结果需适配柱状图展示，即每个时段对应一条统计记录

问题分析 #

仅用CASE语句无法解决跨多时段课程的拆分需求——CASE只能为每条课程记录返回单个值，无法将一条课程记录映射到多个时段行。正确的思路是先生成目标时段列表，再将课程数据与时段列表关联匹配，最后统计每个时段的唯一学生数。

实现方案 #

以下是基于你提供的查询逻辑优化后的完整SQL：

WITH time_slots AS (
    -- 生成需要统计的小时段：示例为7:00到18:00，可按需调整
    SELECT
        TO_CHAR(TRUNC(SYSDATE) + (LEVEL-1)/24, 'HH24:MI') AS slot_start,
        TO_CHAR(TRUNC(SYSDATE) + LEVEL/24, 'HH24:MI') AS slot_end,
        TO_CHAR(TRUNC(SYSDATE) + (LEVEL-1)/24, 'HH24') || ':00-' || TO_CHAR(TRUNC(SYSDATE) + LEVEL/24, 'HH24') || ':00' AS slot_label
    FROM DUAL
    CONNECT BY LEVEL <= 12 -- 7到18点共12个小时段
)
SELECT
    ts.slot_label AS 时段,
    COUNT(DISTINCT s.SGBSTDN_PIDM) AS 不可用学生人数
FROM time_slots ts
LEFT JOIN (
    -- 你的原始查询，获取学生及对应的课程时段
    SELECT DISTINCT
        SGBSTDN.SGBSTDN_PIDM,
        SSRMEET.SSRMEET_BEGIN_TIME,
        SSRMEET.SSRMEET_END_TIME
    FROM SATURN.SGBSTDN SGBSTDN
    JOIN SATURN.SFRSTCR SFRSTCR ON SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
    JOIN SATURN.SSBSECT SSBSECT ON SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE 
                               AND SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
    JOIN SATURN.SSRMEET SSRMEET ON SSBSECT.SSBSECT_TERM_CODE = SSRMEET.SSRMEET_TERM_CODE 
                               AND SSBSECT.SSBSECT_CRN = SSRMEET.SSRMEET_CRN
    JOIN SATURN.SGRSPRT SGRSPRT ON SGBSTDN.SGBSTDN_PIDM = SGRSPRT.SGRSPRT_PIDM 
                               AND SFRSTCR.SFRSTCR_TERM_CODE = SGRSPRT.SGRSPRT_TERM_CODE
    WHERE SFRSTCR.SFRSTCR_TERM_CODE = '202330'
      AND SGRSPRT.SGRSPRT_ACTC_CODE = 'NCAASB'
      AND SGRSPRT.SGRSPRT_ELIG_CODE = 'Y'
      AND SSBSECT.SSBSECT_SUBJ_CODE || SSBSECT.SSBSECT_CRSE_NUMB || SSBSECT.SSBSECT_SEQ_NUMB NOT LIKE 'ES0%'
      AND SGBSTDN.SGBSTDN_TERM_CODE_EFF = (
          SELECT MAX(N1.SGBSTDN_TERM_CODE_EFF)
          FROM SATURN.SGBSTDN N1
          WHERE N1.SGBSTDN_PIDM = SGBSTDN.SGBSTDN_PIDM
      )
) s ON TO_DATE(s.SSRMEET_BEGIN_TIME, 'HH24:MI') < TO_DATE(ts.slot_end, 'HH24:MI')
   AND TO_DATE(s.SSRMEET_END_TIME, 'HH24:MI') > TO_DATE(ts.slot_start, 'HH24:MI')
GROUP BY ts.slot_label, ts.slot_start
ORDER BY ts.slot_start;

关键逻辑说明 #

1. 生成时段列表：

   • 用CONNECT BY生成连续的小时段，slot_label用于柱状图展示（格式如「07:00-08:00」）
   • 可通过调整LEVEL <= 12和起始时间，修改统计的时段范围

2. 课程与时段的匹配：

   • 核心条件：课程开始时间 < 时段结束时间 AND 课程结束时间 > 时段开始时间，确保所有与时段有重叠的课程都被关联
   • 这条逻辑会自动拆分跨时段课程，比如7:30-15:30的课程会匹配07:00-08:00到14:00-15:00的所有时段

3. 去重统计：

   • 使用COUNT(DISTINCT SGBSTDN_PIDM)避免同一学生因多门课程在同一时段被重复计数，保证数据准确性

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