BigQuery按事件时间拆分事件行 关联工单start_time与resolved_time

核心逻辑 #

基于事件流的状态规则拆分工单，不需要额外自连接，全程用窗口函数实现：

• 按事件时间排序，给每一行计算所属工单的分组号：分组号 = 当前行之前累计出现的resolved事件总数。每出现一次resolved代表前序工单关闭，后续事件自动归入新分组
• 按分组聚合计算工单属性：
  • start_time：分组内首次出现message/pending/unresolved三类触发打开状态事件的最小时间戳
  • resolved_time：分组内resolved事件的时间戳，无该事件则返回NULL
• 将聚合后的工单属性关联回原始事件行，完成全量标注。

完整可运行SQL #

WITH sample_table AS (
    SELECT  TIMESTAMP("2021-01-15 20:27:59") `timestamp` , "unresolved" event_name UNION ALL
    SELECT  TIMESTAMP("2021-01-16 03:02:46") , "message"  UNION ALL
    SELECT  TIMESTAMP("2021-01-20 19:31:37") , "resolved"  UNION ALL
    SELECT  TIMESTAMP("2021-01-21 00:13:43") , "pending"  UNION ALL
    SELECT  TIMESTAMP("2021-01-23 23:38:46") , "resolved" UNION ALL
    SELECT  TIMESTAMP("2021-01-24 00:38:17") , "message" UNION ALL
    SELECT  TIMESTAMP("2021-01-24 00:42:31") , "unresolved" UNION ALL
    SELECT  TIMESTAMP("2021-01-24 02:19:44") , "resolved" UNION ALL
    SELECT  TIMESTAMP("2021-01-25 15:55:50") , "message" UNION ALL
    SELECT  TIMESTAMP("2021-01-25 15:59:55") , "unresolved" 
),
-- 为每行计算所属工单分组号
event_with_group AS (
    SELECT 
        *,
        COUNTIF(event_name = 'resolved') OVER (
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS ticket_group
    FROM sample_table
),
-- 按分组聚合提取每个工单的核心时间属性
ticket_meta AS (
    SELECT
        ticket_group,
        MIN(IF(event_name IN ('message', 'pending', 'unresolved'), timestamp, NULL)) AS start_time,
        MAX(IF(event_name = 'resolved', timestamp, NULL)) AS resolved_time
    FROM event_with_group
    GROUP BY ticket_group
)
-- 关联回原始事件生成最终结果
SELECT 
    e.timestamp,
    e.event_name,
    m.start_time,
    m.resolved_time
FROM event_with_group e
JOIN ticket_meta m
    ON e.ticket_group = m.ticket_group
ORDER BY e.timestamp

结果验证 #

执行后输出完全匹配给定的预期拆分结果：

• 前3行事件归属第0组已解决工单，start_time为2021-01-15 20:27:59，resolved_time为2021-01-20 19:31:37
• 第4-5行事件归属第1组已解决工单，start_time为2021-01-21 00:13:43，resolved_time为2021-01-23 23:38:46
• 第6-8行事件归属第2组已解决工单，start_time为2021-01-24 00:38:17，resolved_time为2021-01-24 02:19:44
• 最后2行事件归属第3组未解决工单，start_time为2021-01-25 15:55:50，resolved_time为NULL

注意：若数据中存在连续的resolved事件（即两个resolved间无触发工单打开的三类事件），这类分组因无有效起始事件，start_time会返回NULL，本身不构成有效工单，可根据业务需要增加过滤条件筛除这类无效分组。

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