BigQuery中如何扁平化SQL关联的多行用户导航事件序列，准确拆分单轮无错配流程

核心逻辑 #

• 流程隔离：按用户分区、事件时间排序，通过累计start事件的计数为每个用户的不同流程生成唯一组ID，从根源避免跨用户、跨轮次的流程数据交叉。
• 最终选择匹配：取每个流程组内最后一次choose事件的选项，自动覆盖back操作后的重选行为，避免中途选项错配。

落地实现SQL #

WITH user_events_grouped AS (
  -- 给每个事件绑定所属流程ID
  SELECT
    user,
    timestamp,
    event_name,
    event_choice,
    -- 按用户累计start事件次数，生成单轮流程的唯一标识
    COUNTIF(event_name = 'start') OVER (
      PARTITION BY user 
      ORDER BY timestamp 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS flow_id
  FROM `your_project.your_dataset.your_event_table` -- 替换为实际业务表路径
)
-- 按流程ID聚合生成单条流程记录
SELECT
  user,
  MIN(IF(event_name = 'start', timestamp, NULL)) AS start_at,
  MIN(IF(event_name = 'end', timestamp, NULL)) AS end_at,
  -- 取流程内最后一次选择的选项
  ARRAY_AGG(IF(event_name = 'choose', event_choice, NULL) IGNORE NULLS ORDER BY timestamp DESC)[OFFSET(0)] AS choice
FROM user_events_grouped
GROUP BY user, flow_id
-- 可选：过滤未走完完整流程（无end事件）的无效数据
HAVING end_at IS NOT NULL

错配问题规避说明 #

• 跨用户/跨轮次流程交叉错配：所有事件先按user分区隔离，同一用户的多轮流程通过累计start生成的flow_id再次拆分，聚合操作仅在user + flow_id的最小分组内完成，不会出现跨范围数据匹配。
• back事件导致的选项错配：聚合时取每个流程组内最新的choose事件选项，用户back后重新选择的行为会自动覆盖之前的中途选项，匹配最终结果。

注意事项 #

• 如果存在同时间戳的事件，需在窗口函数的ORDER BY后追加事件唯一标识（如事件自增ID、毫秒级时间戳）作为第二排序键，避免排序混乱导致的流程分组错误。
• 若需统计中途退出的无效流程，可删除最后一行的HAVING过滤条件。

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