BigQuery中从结果序列创建唯一分组的可行方案

核心思路 #

利用窗口函数LAG()识别连续序列的变化点，再通过累积求和生成唯一分组ID，实现每段连续的Pass/Fail序列拥有独立ID。

分步实现代码 #

假设你的数据表为your-project.your-dataset.test_results，包含顺序列（如自增ID、时间戳或自定义seq，用于保证行的顺序）和Test_Result列（值为Pass/Fail）。

WITH group_change_marker AS (
  SELECT
    seq, -- 替换为你的实际顺序列
    Test_Result,
    -- 标记当前行与前一行结果是否不同，不同则记为1，否则0
    CASE 
      WHEN LAG(Test_Result) OVER (ORDER BY seq) != Test_Result THEN 1 
      ELSE 0 
    END AS is_group_change
  FROM `your-project.your-dataset.test_results`
),
generate_group_id AS (
  SELECT
    seq,
    Test_Result,
    -- 对变化标记做累积求和，加1保证分组ID从1开始
    SUM(is_group_change) OVER (ORDER BY seq) + 1 AS Desired_Outcome
  FROM group_change_marker
)
SELECT * FROM generate_group_id

运行逻辑说明 #

1. 标记变化点：

   • LAG(Test_Result) OVER (ORDER BY seq)获取当前行的上一行结果。第一行无前置行，返回NULL，因此CASE返回0。
   • 当当前行结果与前一行不同时，is_group_change记为1，否则为0。

2. 生成分组ID：

   • 对is_group_change做累积求和（SUM() OVER (ORDER BY seq)），每遇到一个变化点（值为1），求和结果就会递增。
   • 最后加1是为了让分组ID从1开始（默认累积求和从0开始），确保分组ID更符合常规计数习惯。

示例验证 #

假设原始数据（按seq排序）：

运行后输出：

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