SQL PARTITION BY多层分组：如何识别日志内技术人员多段工作时间区间

DevOps 小满

2026-10-8

多段工作记录SQL拆分方案

结论

可以通过新增段标识分区的SQL逻辑实现需求，无需保留原有程序遍历方案。

实现思路

核心采用「间隙与孤岛（Gaps and Islands）」算法拆分同事件、同技术人员下的多段工作记录：

先给任务码打类型标签：D/A/E为开始码，C/X为结束码
按EventCode、TechCode分区、按Time排序，用LAG窗口函数取同分区内上一条记录的任务码
对段起点标记做累加求和得到唯一段号：上一条记录为结束码/当前为分区首条记录时，段号+1
最终按EventCode、TechCode、段号三个维度分组，取每组最小时间为起始时间、最大时间为结束时间

可运行SQL实现

WITH step1 AS (
    SELECT 
        EventCode,
        TechCode,
        TaskCode,
        Time,
        -- 取同事件同技术人员下，按时间排序的上一条任务码
        LAG(TaskCode) OVER (PARTITION BY EventCode, TechCode ORDER BY Time) AS prev_task
    FROM 你的日志表名
),
step2 AS (
    SELECT 
        *,
        -- 计算段号：每次遇到结束码后的第一条记录/首条记录，段号+1
        SUM(CASE WHEN prev_task IN ('C','X') OR prev_task IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY EventCode, TechCode ORDER BY Time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM step1
)
-- 分组获取每段工作的起止时间
SELECT 
    EventCode AS Event,
    TechCode AS Tech,
    MIN(Time) AS Start,
    MAX(Time) AS Stop
FROM step2
GROUP BY EventCode, TechCode, group_id
ORDER BY Event, Tech, Start;