Oracle函数提取单日多轮同代码序列首尾值的技术问题

要区分单日每个坐席的多轮辅导时段，核心是先给连续的同状态时段标记独立分组，再基于分组提取首尾值，具体实现如下：

核心思路：间隙与岛屿问题解法 #

连续的辅导（Coach）时段属于同一个“岛屿”，非辅导时段是“间隙”，通过标记状态变化的节点，给每个岛屿分配唯一分组ID，之后就能精准区分不同轮次。

具体SQL实现 #

假设你的日程表表结构为：agent_schedule(date date, agent_id varchar, event_time datetime, status_code varchar)

步骤1：生成每轮时段的分组ID #

WITH grouped_schedule AS (
    SELECT 
        date,
        agent_id,
        event_time,
        status_code,
        -- 累加状态变化点，生成分组ID
        SUM(CASE WHEN prev_status = status_code THEN 0 ELSE 1 END) OVER (
            PARTITION BY date, agent_id 
            ORDER BY event_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS session_group
    FROM (
        SELECT 
            date,
            agent_id,
            event_time,
            status_code,
            -- 获取上一条记录的状态，判断是否为连续同状态
            LAG(status_code) OVER (
                PARTITION BY date, agent_id 
                ORDER BY event_time
            ) AS prev_status
        FROM agent_schedule
    ) t
)

步骤2：提取每轮辅导的首尾时间 #

方法1：分组聚合（推荐，性能更优）

SELECT 
    date,
    agent_id,
    status_code,
    session_group,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end
FROM grouped_schedule
WHERE status_code = 'Coach' -- 过滤出辅导时段
GROUP BY date, agent_id, status_code, session_group
ORDER BY date, agent_id, session_start;

方法2：窗口函数（适合保留原表其他字段场景）

SELECT DISTINCT
    date,
    agent_id,
    status_code,
    session_group,
    FIRST_VALUE(event_time) OVER (
        PARTITION BY date, agent_id, status_code, session_group
        ORDER BY event_time
    ) AS session_start,
    LAST_VALUE(event_time) OVER (
        PARTITION BY date, agent_id, status_code, session_group
        ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS session_end
FROM grouped_schedule
WHERE status_code = 'Coach'
ORDER BY date, agent_id, session_start;

关键说明 #

• 先通过LAG函数对比当前与上一条的状态，标记状态变化的节点；
• 用SUM累加变化节点，生成唯一的session_group，确保同一轮连续辅导属于同一分组；
• 之后无论是聚合还是窗口函数，都能基于分组精准提取每轮的首尾值，不会把所有辅导时段归为一组。

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