BigQuery技术实现：按ID统计每个'approved'事件前的'pending'事件数量

这需求我之前帮同事处理过，核心是要把每个approved事件和它之前的pending事件对应成独立的批次，然后统计每个批次里的pending数量。下面是具体的BigQuery实现方案：

思路拆解 #

• 给事件排序分组：同一id下，按事件发生的先后顺序，把每个approved以及它之前到上一个approved（或该id的起始事件）的pending归为一组。
• 统计每组pending数量：对每个分组统计其中pending事件的数量，只保留包含approved的分组（因为我们只关心每个approved对应的pending数）。

具体SQL代码 #

WITH numbered_events AS (
  SELECT
    id,
    event,
    -- 替换成你的实际时间字段（比如event_time），确保事件按发生顺序排列
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY event_time) AS row_num,
    -- 生成分组ID：每遇到一个approved，分组ID递增，把当前approved和之前的pending归为一组
    SUM(CASE WHEN event = 'approved' THEN 1 ELSE 0 END) 
      OVER(PARTITION BY id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
  FROM
    `your-project.your-dataset.events` -- 替换成你的表路径
),
grouped_pending_counts AS (
  SELECT
    id,
    group_id,
    -- 统计当前分组内的pending数量
    COUNT(CASE WHEN event = 'pending' THEN 1 END) AS count_events
  FROM
    numbered_events
  GROUP BY
    id, group_id
  HAVING
    -- 只保留包含approved的分组（每个分组对应一个approved事件）
    SUM(CASE WHEN event = 'approved' THEN 1 END) > 0
)
SELECT
  id,
  count_events
FROM
  grouped_pending_counts
ORDER BY
  id, group_id;

代码说明 #

• numbered_events CTE：这里的group_id是关键——它会为每个id下的事件按顺序累积统计approved的数量，这样每个approved事件所在的组，就包含了它之前所有未被统计到上一个approved的pending事件。
• grouped_pending_counts CTE：分组统计每个组内的pending数量，并用HAVING子句过滤掉没有approved的组（避免统计多余的pending批次）。
• 如果你的表没有时间字段，可以把ORDER BY event_time替换成ORDER BY (SELECT NULL)，但这依赖BigQuery的存储顺序，建议优先使用实际的时间戳字段保证准确性。

执行这段SQL后，就能得到你预期的结果：每个approved事件对应其之前的pending数量，按id和approved的顺序排列。

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