如何用BigQuery Standard SQL窗口函数实现匹配行连续计数并重置

问题分析 #

你当前的查询仅通过LAG()获取前一行的匹配状态，每次最多将计数器加1，无法累积连续匹配的次数，因此没法得到2以上的计数。要实现连续匹配时递增、不匹配时重置的逻辑，需要先将连续匹配的行划分为同一分组，再在分组内进行计数。

正确实现代码 #

WITH sample_data AS (
  SELECT 1 AS field1, 1 AS field2 UNION ALL
  SELECT 2 AS field1, 2 AS field2 UNION ALL
  SELECT 3 AS field1, 2 AS field2 UNION ALL
  SELECT 5 AS field1, 5 AS field2 UNION ALL
  SELECT 6 AS field1, 6 AS field2 UNION ALL
  SELECT 9 AS field1, 9 AS field2
),
grouped_data AS (
  SELECT
    field1,
    field2,
    -- 标记当前行是否匹配
    CASE WHEN field1 = field2 THEN 1 ELSE 0 END AS is_match,
    -- 生成分组ID：遇到不匹配行或前一行不匹配时，分组ID递增
    SUM(CASE WHEN field1 != field2 OR LAG(field1 = field2) OVER (ORDER BY field1) = FALSE THEN 1 ELSE 0 END) OVER (ORDER BY field1) AS group_id
  FROM sample_data
)
SELECT
  field1,
  field2,
  -- 匹配行在分组内按顺序计数，不匹配行设为0
  CASE WHEN is_match = 1 THEN ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY field1) ELSE 0 END AS counter
FROM grouped_data
ORDER BY field1;

代码解释 #

1. sample_data：替换为你的实际数据表，这里用给定的样本数据做演示。
2. grouped_data：
   • is_match：简单标记当前行是否满足field1=field2的条件。
   • group_id：通过窗口函数SUM()生成分组ID，每当遇到不匹配的行，或者前一行不匹配但当前行匹配时，分组ID加1，确保所有连续匹配的行被分到同一个分组中。
3. 最终查询：在每个分组内，对匹配的行用ROW_NUMBER()生成递增的计数器，不匹配的行直接设为0，结果按原数据顺序输出。

执行结果 #

完全符合预期输出要求。

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