BigQuery聚簇分区场景下相邻同组排序编号生成问题

结论 #

没有匹配该需求的开箱即用内置函数，该场景属于SQL经典的**间隙与岛屿（Gaps and Islands）**问题，通过多层窗口函数组合即可实现，无需额外预先生成辅助列。

实现前提 #

需提前确认一个可全局确定行先后顺序的字段（如事件时间戳、自增唯一ID等），作为「相邻行」的判断依据，示例中该字段记为sort_col，两个聚簇分组字段记为col1、col2。

实现代码（BigQuery 标准SQL） #

WITH step1 AS (
  SELECT
    *,
    -- 对比当前行与上一行的聚簇标识，发生变化则标记为1
    IF(
      LAG(CONCAT(col1, ',', col2)) OVER(ORDER BY sort_col) != CONCAT(col1, ',', col2),
      1,
      0
    ) AS is_new_cluster
  FROM `your_project.your_dataset.your_table`
),
step2 AS (
  SELECT
    *,
    -- 对变化标记累加得到全局唯一聚簇ID
    SUM(is_new_cluster) OVER(ORDER BY sort_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS global_cluster_id
  FROM step1
),
step3 AS (
  SELECT
    *,
    -- 同聚簇标识内按出现顺序生成批次号，即需求描述的连续组编号
    DENSE_RANK() OVER(PARTITION BY col1, col2 ORDER BY global_cluster_id) AS cluster_batch_num
  FROM step2
)
SELECT * FROM step3

逻辑说明 #

• step1的is_new_cluster标记位用于识别聚簇切换的边界行，只有聚簇发生变化时才会标记为1
• step2生成的global_cluster_id为全局连续的聚簇编号，所有连续同组的行会分配相同的ID
• step3生成的cluster_batch_num就是需求中描述的批次号：同一聚簇标识首次连续出现时值为1，被其他组隔开后再次出现时值为2，以此类推

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