SQL Server 如何从事件日志中生成工单工序的开始与结束时间

1. 核心思路：会话分组标记法 #

多个停止事件对应同个启动事件的本质是：两个相邻启动事件之间的所有停止事件，都归属于前一个启动事件。我们可以给每一组「1个启动事件 + N个后续停止事件」打同一个分组ID，就能灵活匹配一对多的对应关系，规避LEAD函数只能一对一匹配的局限性。

2. 具体实现步骤 #

• 第一步：事件类型预处理，统一归类停止事件
  按照需求把Rework事件按停止事件处理，先明确两类事件的判定规则：
  • 启动事件：status = 'Started'
  • 停止事件：status != 'Started'（包含正常停止、Rework等所有需要生成记录的停止类事件）
• 第二步：生成分组ID
  按order、operation、equipment三个维度分区（同一个工单、同一道工序、同一台设备的事件才属于同一业务流），按timeRecorded升序排序，遇到启动事件就给分组计数+1，停止事件计数不变，这样同一个组内的所有事件都对应同一个启动事件。
• 第三步：关联启动时间，生成最终结果
  取每个分组内唯一的启动事件的timeRecorded作为startTime，分组内每个停止事件的timeRecorded作为endTime，输出要求的字段即可。

3. 通用SQL实现示例（适配MySQL 8.0+/PostgreSQL/Spark SQL等大多数主流数据库） #

WITH event_preprocess AS (
    -- 预处理：标记是否为启动事件
    SELECT 
        `order`, -- order是SQL保留关键字，需要用反引号/双引号转义
        operation,
        equipment,
        status,
        timeRecorded,
        CASE WHEN status = 'Started' THEN 1 ELSE 0 END AS is_start
    FROM your_original_log_table -- 替换为你的原始表名
),
session_group AS (
    -- 生成分组ID：同业务流下，每遇到一个启动事件，分组ID加1
    SELECT 
        *,
        SUM(is_start) OVER (
            PARTITION BY `order`, operation, equipment 
            ORDER BY timeRecorded 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM event_preprocess
)
-- 输出停止事件的最终结果
SELECT 
    s.`order`,
    s.operation,
    s.equipment,
    s.status,
    -- 取当前分组对应的启动事件时间作为startTime
    MAX(CASE WHEN s.is_start = 1 THEN s.timeRecorded END) OVER (PARTITION BY s.`order`, s.operation, s.equipment, s.group_id) AS startTime,
    s.timeRecorded AS endTime
FROM session_group s
-- 只保留停止事件，过滤启动事件本身
WHERE s.is_start = 0
-- 可选：过滤还未匹配到启动事件的脏停止数据
-- AND startTime IS NOT NULL

4. 特殊场景适配 #

• 如果存在连续多个启动事件没有中间停止的情况，可以在预处理步骤中过滤掉重复的连续启动事件，仅保留最新的一个启动事件即可，避免分组ID错误累加。
• 如果业务要求同一个启动事件下的重复停止事件只保留最后一条，可以在最终输出前按分组ID去重，取最大的endTime即可。

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