Google BigQuery实现按用户提取flag相邻两个操作的SQL方法

前置逻辑对齐 #

• 原始数据核心必备字段：用户唯一标识（user_id）、操作发生时间（event_time）、操作标记字段（flag）
• 核心计算规则：单用户维度下，按操作发生的时间先后顺序排序，依次提取序列中相邻两个操作的flag值，组成配对记录输出
• 边界值处理：单用户操作数不足2条时，无有效相邻配对，直接过滤即可

分步实现流程 #

1. 给单用户操作按时间排序 #

用BigQuery内置窗口函数，按用户分区、操作时间升序生成连续行号，为后续相邻配对提供有序基础：

WITH ordered_user_events AS (
  SELECT
    user_id,
    event_time,
    flag,
    ROW_NUMBER() OVER (
      PARTITION BY user_id 
      ORDER BY event_time ASC
    ) AS event_order_seq
  FROM 你的原始事件表
)

2. 错位生成相邻配对 #

利用LAG/LEAD窗口函数直接错位取值，避免自连接带来的全表重复扫描开销，适配BigQuery的引擎特性：

• LAG(flag) 取当前操作的上一步操作的flag值
• LEAD(flag) 取当前操作的下一步操作的flag值
• 过滤掉上一步值为空的边界行（即每个用户的第一条操作，没有前置相邻值）

, adjacent_pairs AS (
  SELECT
    user_id,
    event_order_seq,
    flag AS current_operation_flag,
    LAG(flag) OVER (
      PARTITION BY user_id 
      ORDER BY event_order_seq
    ) AS prev_operation_flag
  FROM ordered_user_events
)
-- 输出有效相邻配对
SELECT
  user_id,
  prev_operation_flag AS adjacent_first_flag,
  current_operation_flag AS adjacent_second_flag
FROM adjacent_pairs
WHERE prev_operation_flag IS NOT NULL

3. 适配业务自定义规则 #

如果需要按flag的特定匹配规则筛选输出（比如仅保留某类flag组合的配对），直接在最终筛选条件中追加判断即可，例如只提取“登录->浏览商品”这类固定顺序的flag配对：

-- 自定义规则筛选示例
WHERE prev_operation_flag IS NOT NULL
  AND prev_operation_flag = 'user_login'
  AND current_operation_flag = 'browse_product'

引擎适配提示 #

• 大表计算时优先使用内置窗口函数而非自连接，BigQuery的列式存储对这类错位计算的优化程度更高，执行成本更低
• 如果单用户操作量极大（单用户百条以上操作），可在数据扫描阶段提前过滤非目标用户的操作记录，减少分区计算开销
• 可保留原始操作的时间戳字段在最终结果中，方便后续校验配对的时间间隔合理性

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