BigQuery SQL按客户分组递归更新select语句中end date字段

实现逻辑说明 #

你需要的是按customer分组后，基于前一行处理后的end_date递归更新当前行end_date的逻辑，可直接通过递归CTE实现，全程仅用SELECT语句，不会修改原表数据。

实现代码 #

假设你的原始表名为your_source_table，可直接使用如下查询：

WITH RECURSIVE ranked_rows AS (
  -- 第一步：按客户分组，date升序排序后给每行加编号
  SELECT
    customer,
    date,
    end_date,
    ROW_NUMBER() OVER(PARTITION BY customer ORDER BY date ASC) AS row_num
  FROM `your_source_table`
),
processed_data AS (
  -- 递归锚点：取每个客户的第一行作为初始值
  SELECT
    customer,
    date,
    end_date AS final_end_date,
    row_num
  FROM ranked_rows
  WHERE row_num = 1

  UNION ALL

  -- 递归迭代：逐行处理后续数据
  SELECT
    curr.customer,
    curr.date,
    IF(curr.date < prev.final_end_date, prev.final_end_date, curr.end_date) AS final_end_date,
    curr.row_num
  FROM processed_data prev
  INNER JOIN ranked_rows curr
    ON prev.customer = curr.customer
    AND curr.row_num = prev.row_num + 1
)
-- 输出最终结果
SELECT
  customer,
  date,
  final_end_date AS end_date
FROM processed_data
ORDER BY customer, row_num;

注意事项 #

• 把代码中的your_source_table替换为你实际的表名即可
• 若单个customer下的行数超过1000，可在查询开头加语句调整递归深度上限：SET @@recursion_depth_limit = 自定义最大层数;
• 该查询仅做数据读取计算，不会对原表执行任何增删改操作

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