如何在MySQL中按LIFO规则配对begin-end事件并计算任务耗时？

要实现题目要求的后进先出（LIFO）配对规则，核心是模拟栈的压入弹出逻辑：按时间顺序遍历同一任务的所有事件，遇到begin就压栈记录，遇到end就弹出栈顶的begin完成配对。这个逻辑可以通过窗口函数拆解为可落地的实现步骤：

核心计算逻辑 #

• 对同一任务的所有事件按时间戳升序排序，逐行计算栈深度：遇到begin则深度+1，遇到end则深度-1。
• 识别独立栈周期：当栈深度回到0时，说明当前的栈已经清空，后续的begin会开启一个新的独立栈周期，避免不同周期的事件交叉配对。
• 为每个事件生成唯一配对标识：
  • begin事件的配对标识 = 所在的栈周期ID + 压栈后的栈深度
  • end事件的配对标识 = 所在的栈周期ID + 弹栈前的栈深度
• 将配对标识完全一致的begin和end事件关联，计算时间差即为单次任务的耗时。

实现SQL（MySQL 8.0+ 支持窗口函数） #

假设任务事件表名为task_events，字段包含Task（任务ID）、Event（事件类型，值为begin/end）、Timestamp（事件时间戳），完整查询语句如下：

WITH event_depth AS (
    SELECT
        Task,
        Event,
        Timestamp,
        -- 计算当前行处理完成后的栈深度
        SUM(CASE WHEN Event = 'begin' THEN 1 ELSE -1 END)
            OVER (
                PARTITION BY Task 
                ORDER BY Timestamp 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS current_depth,
        -- 标记当前行处理完成后栈是否为空
        CASE WHEN SUM(CASE WHEN Event = 'begin' THEN 1 ELSE -1 END)
            OVER (
                PARTITION BY Task 
                ORDER BY Timestamp 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) = 0 THEN 1 ELSE 0 END AS empty_flag
    FROM task_events
),
event_match AS (
    SELECT
        Task,
        Event,
        Timestamp,
        -- 计算当前行所属的栈周期ID：当前行之前累计的栈清空次数
        SUM(empty_flag)
            OVER (
                PARTITION BY Task 
                ORDER BY Timestamp 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) AS segment_id,
        -- 计算配对用的深度值
        CASE
            WHEN Event = 'begin' THEN current_depth
            WHEN Event = 'end' THEN current_depth + 1
        END AS depth_match
    FROM event_depth
),
paired_begins AS (
    SELECT Task, Timestamp AS Begin, segment_id, depth_match
    FROM event_match
    WHERE Event = 'begin'
),
paired_ends AS (
    SELECT Task, Timestamp AS End, segment_id, depth_match
    FROM event_match
    WHERE Event = 'end'
)
SELECT
    b.Task,
    b.Begin,
    e.End,
    (e.End - b.Begin) AS Elapsed
FROM paired_begins b
INNER JOIN paired_ends e
    ON b.Task = e.Task
    AND b.segment_id = e.segment_id
    AND b.depth_match = e.depth_match
ORDER BY b.Task, b.Begin;

结果验证 #

代入题目给出的样例数据，查询输出结果和要求的格式完全一致：

注意事项 #

• 该逻辑严格遵循LIFO规则：每个end事件只会匹配时间上在它之前、最晚压入栈的begin事件，不会出现跨栈周期的错误配对。
• 题目假设同一任务的begin和end事件数量相等，因此无需额外处理未配对的异常数据。
• 如果使用MySQL 5.x版本不支持窗口函数，可以通过用户变量逐行遍历排序后的事件，模拟栈深度、周期ID的累计计算过程实现相同逻辑。

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