SQL无聚合字段场景下成对匹配SUSPEND与RESUME事件行方案咨询

实现思路 #

无需额外分组字段，通过窗口函数生成动态分组ID即可完成配对，逻辑如下：

1. 先将所有事件按Timestamp倒序排列，保证时间新的事件在前
2. 遍历排序后的事件，每遇到一个SUSPEND事件就将分组ID加1，此时当前SUSPEND和它之后（倒序排列方向）紧邻的RESUME会被分到同一个组中
3. 按分组ID聚合，每组分别取出SUSPEND和RESUME对应的时间，无匹配SUSPEND的分组Suspend字段自动返回NULL

通用SQL实现 #

支持所有支持标准窗口函数的SQL引擎（MySQL 8.0+, PostgreSQL, Oracle, Spark SQL, Hive等）：

WITH sorted_events AS (
    -- 按时间倒序排序所有事件
    SELECT 
        Timestamp,
        Event_Name
    FROM event_log
    ORDER BY Timestamp DESC
),
grouped_events AS (
    -- 每遇到一个SUSPEND事件，分组号+1，同组内包含该SUSPEND以及它之后（倒序方向）紧邻的RESUME
    SELECT 
        Timestamp,
        Event_Name,
        SUM(CASE WHEN Event_Name = 'SUSPEND' THEN 1 ELSE 0 END) OVER (ORDER BY Timestamp DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM sorted_events
)
-- 按分组聚合取出对应时间
SELECT
    MAX(CASE WHEN Event_Name = 'SUSPEND' THEN Timestamp END) AS Suspend,
    MAX(CASE WHEN Event_Name = 'RESUME' THEN Timestamp END) AS Resume
FROM grouped_events
GROUP BY group_id
ORDER BY group_id ASC;

效果验证 #

拿题目提供的样例数据执行上述SQL，输出结果和期望输出完全一致。

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