如何使用BigQuery SQL基于前三列生成符合规则的BlockID

根据需求，我们可以通过窗口函数和多步骤CTE实现BlockID的生成，核心是识别同一id下连续的num_characters组，对符合条件的组分配唯一ID，单独出现的组标记为-1。

完整SQL代码 #

WITH grouped_data AS (
  SELECT
    id,
    num_characters,
    PARSE_DATE('%m/%d/%y', date) AS date,
    -- 标记连续组的起始行
    CASE
      WHEN num_characters != LAG(num_characters) OVER (PARTITION BY id ORDER BY PARSE_DATE('%m/%d/%y', date))
        THEN 1
      WHEN LAG(num_characters) OVER (PARTITION BY id ORDER BY PARSE_DATE('%m/%d/%y', date)) IS NULL
        THEN 1
      ELSE 0
    END AS group_start,
    -- 生成每个id内的组编号
    SUM(CASE
      WHEN num_characters != LAG(num_characters) OVER (PARTITION BY id ORDER BY PARSE_DATE('%m/%d/%y', date))
        THEN 1
      WHEN LAG(num_characters) OVER (PARTITION BY id ORDER BY PARSE_DATE('%m/%d/%y', date)) IS NULL
        THEN 1
      ELSE 0
    END) OVER (PARTITION BY id ORDER BY PARSE_DATE('%m/%d/%y', date)) AS id_group_id
  FROM
    your_table_name -- 替换为实际表名
),
group_counts AS (
  -- 统计每个组的行数
  SELECT
    id,
    id_group_id,
    COUNT(*) AS row_count
  FROM
    grouped_data
  GROUP BY
    id,
    id_group_id
),
block_assignments AS (
  -- 给行数≥2的组分配全局唯一BlockID
  SELECT
    gc.id,
    gc.id_group_id,
    ROW_NUMBER() OVER (ORDER BY MIN(gd.date)) AS block_id
  FROM
    group_counts gc
  JOIN
    grouped_data gd ON gc.id = gd.id AND gc.id_group_id = gd.id_group_id
  WHERE
    gc.row_count >= 2
  GROUP BY
    gc.id,
    gc.id_group_id
)
-- 最终生成结果
SELECT
  gd.id,
  gd.num_characters,
  FORMAT_DATE('%m/%d/%y', gd.date) AS date,
  CASE
    WHEN gc.row_count = 1 THEN -1
    ELSE ba.block_id
  END AS BlockID
FROM
  grouped_data gd
JOIN
  group_counts gc ON gd.id = gc.id AND gd.id_group_id = gc.id_group_id
LEFT JOIN
  block_assignments ba ON gd.id = ba.id AND gd.id_group_id = ba.id_group_id
ORDER BY
  gd.id,
  gd.date;

逻辑说明 #

1. grouped_data：

   • 将字符串类型的date转换为BigQuery可识别的DATE类型，保证排序准确性。
   • 用LAG()窗口函数对比当前行与前一行的num_characters，标记连续组的起始点。
   • 累加起始点标记，生成每个id内部的组编号id_group_id，同一连续组的行拥有相同编号。

2. group_counts：

   • 统计每个id + id_group_id组合的行数，判断该组是否为仅出现一次的组。

3. block_assignments：

   • 筛选出行数≥2的组，按组的最早日期排序，用ROW_NUMBER()生成全局唯一的BlockID，确保编号递增且唯一。

4. 最终查询：

   • 关联所有CTE数据，根据组行数判断：行数为1时BlockID设为-1，否则使用分配的全局BlockID，同时将date转回原字符串格式。

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