BigQuery中实现非空值触发分组编号递增的方法咨询

你可以使用**SUM()窗口函数**结合条件判断来实现这个需求，核心逻辑是对conversion_time非空的行标记为1，空行标记为0，然后按行顺序累加这些标记值，得到的累加结果就是你需要的分组编号。

具体实现代码 #

假设你的表包含用于确定行顺序的字段（比如主键id或时间戳），示例代码如下：

WITH sample_data AS (
  -- 模拟你的业务数据
  SELECT 1 AS id, NULL AS conversion_time UNION ALL
  SELECT 2 AS id, NULL AS conversion_time UNION ALL
  SELECT 3 AS id, TIMESTAMP('2024-01-01 10:00:00') AS conversion_time UNION ALL
  SELECT 4 AS id, NULL AS conversion_time UNION ALL
  SELECT 5 AS id, TIMESTAMP('2024-01-02 14:00:00') AS conversion_time UNION ALL
  SELECT 6 AS id, NULL AS conversion_time
)
SELECT
  id,
  conversion_time,
  -- 核心逻辑：累加非空行的标记值
  SUM(CASE WHEN conversion_time IS NOT NULL THEN 1 ELSE 0 END) OVER (
    ORDER BY id  -- 替换为你实际用于确定行顺序的字段（如时间戳）
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS group_name
FROM sample_data

逻辑说明 #

1. 条件标记：通过CASE语句，将conversion_time非空的行转换为1，空行转换为0；
2. 窗口累加：SUM() OVER()窗口函数按照指定的行顺序（ORDER BY），从第一行开始累加到当前行的标记值；
3. 结果匹配：空行的标记值为0，累加后保持上一个分组编号；非空行标记值为1，累加后编号递增，完全匹配你的需求。

运行代码后输出结果如下：

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