编写BigQuery SQL查询：为连续3个0的indicator序列分配唯一组号

以下是适配BigQuery的SQL解决方案，通过窗口函数完成需求：

WITH sample_data AS (
  SELECT 0 AS offset, 1 AS indicator UNION ALL
  SELECT 1, 1 UNION ALL
  SELECT 2, 1 UNION ALL
  SELECT 3, 1 UNION ALL
  SELECT 4, 1 UNION ALL
  SELECT 5, 1 UNION ALL
  SELECT 6, 1 UNION ALL
  SELECT 7, 0 UNION ALL
  SELECT 8, 0 UNION ALL
  SELECT 9, 0 UNION ALL
  SELECT 10, 1 UNION ALL
  SELECT 11, 0 UNION ALL
  SELECT 12, 0 UNION ALL
  SELECT 13, 0 UNION ALL
  SELECT 14, 1 UNION ALL
  SELECT 15, 1 UNION ALL
  SELECT 16, 1
),
-- 标记连续相同indicator的分段
grouped_segments AS (
  SELECT
    offset,
    indicator,
    -- 每当indicator与前一行不同时，生成新的分段ID
    SUM(CASE WHEN indicator != LAG(indicator) OVER (ORDER BY offset) THEN 1 ELSE 0 END) OVER (ORDER BY offset) AS segment_id
  FROM sample_data
),
-- 计算分段长度，标记符合条件的0序列段
segment_stats AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY segment_id) AS segment_length,
    CASE WHEN indicator = 0 AND COUNT(*) OVER (PARTITION BY segment_id) >=3 THEN 1 ELSE 0 END AS is_valid_group
  FROM grouped_segments
),
-- 给有效0序列段分配唯一组编号
final_result AS (
  SELECT
    offset,
    indicator,
    CASE
      WHEN is_valid_group = 1 THEN DENSE_RANK() OVER (PARTITION BY is_valid_group ORDER BY segment_id)
      ELSE -1
    END AS grp
  FROM segment_stats
)
SELECT * FROM final_result ORDER BY offset;

代码逻辑说明： #

1. sample_data：定义输入数据集，实际使用时替换为你的目标表即可。
2. grouped_segments：通过LAG函数对比当前行与前一行的indicator值，累加生成连续相同值的分段ID。
3. segment_stats：计算每个分段的长度，标记出长度≥3的0序列分段。
4. final_result：用DENSE_RANK为所有有效0序列分段分配递增的唯一组号，非有效分段统一标记为-1。

执行后将得到与示例完全一致的结果：offset 7-9对应grp=1，offset11-13对应grp=2，其余行grp=-1。

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