求助：用BigQuery SQL实现按阈值累加汇总并重置的逻辑

要实现按id分组，累加total_amount直到达到阈值后生成汇总记录、重置累加器继续处理的需求，核心是给每一行分配分组标识，再基于标识聚合。以下是具体的BigQuery SQL方案：

解决方案代码 #

WITH RECURSIVE grouped_data AS (
  -- 初始化：获取每个id的首条记录，初始化累加器和组ID
  SELECT 
    id,
    from_range,
    to_range,
    total_amount,
    total_amount AS current_sum,
    0 AS group_id
  FROM Test t1
  WHERE NOT EXISTS (
    SELECT 1 FROM Test t2 
    WHERE t2.id = t1.id AND t2.from_range < t1.from_range
  )
  
  UNION ALL
  
  -- 递归处理后续每一条记录
  SELECT 
    t.id,
    t.from_range,
    t.to_range,
    t.total_amount,
    -- 累加判断：超过阈值则重置累加器，否则继续累加
    CASE 
      WHEN gd.current_sum + t.total_amount >= 10000000 THEN t.total_amount 
      ELSE gd.current_sum + t.total_amount 
    END AS current_sum,
    -- 组ID更新：超过阈值则新建组，否则保留原组
    CASE 
      WHEN gd.current_sum + t.total_amount >= 10000000 THEN gd.group_id + 1 
      ELSE gd.group_id 
    END AS group_id
  FROM Test t
  JOIN grouped_data gd 
    ON t.id = gd.id 
    AND t.from_range > gd.from_range
  -- 确保每条记录仅被处理一次
  WHERE NOT EXISTS (
    SELECT 1 FROM grouped_data gd2 
    WHERE gd2.id = t.id AND gd2.from_range = t.from_range
  )
)
-- 按id和组ID聚合，生成最终汇总结果
SELECT 
  id,
  MIN(from_range) AS from_range,
  MAX(to_range) AS to_range,
  SUM(total_amount) AS total_amount
FROM grouped_data
GROUP BY id, group_id
ORDER BY id, from_range;

代码说明 #

1. 递归初始化：先定位每个id的第一条记录，初始化当前累加和为该记录的total_amount，组ID设为0。
2. 递归迭代：逐行处理后续记录，判断当前组的累加和加上当前记录金额是否达到阈值：
   • 若达到阈值：重置累加器为当前记录金额，组ID加1（开启新组）；
   • 未达到阈值：继续累加，组ID保持不变。
3. 聚合汇总：按id和group_id分组，取每组的最小from_range、最大to_range，以及total_amount的总和，得到分段后的汇总记录。

灵活调整 #

如果阈值需要动态设置，可将代码中的10000000替换为BigQuery的查询参数（如@threshold），适配不同场景。

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