如何在BigQuery中使用标准SQL填充累计值实现数据预估

我们可以通过递归公共表表达式（Recursive CTE） 实现逐行依赖的累积计算，这是处理这类前序计算结果作为当前行输入场景的标准方案。

完整实现代码 #

WITH RECURSIVE raw_data AS (
-- 原始数据集
select 1 day, 1 percentage, 100 value, 100 cum_value UNION ALL
select 2 day, 0.5 percentage, 75 value, 175 cum_value UNION ALL
select 3 day, 0.3 percentage, 80 value, 255 cum_value UNION ALL
select 4 day, 0.25 percentage, 95 value, 350 cum_value UNION ALL
select 5 day, 0.2 percentage, 45 value, 395 cum_value UNION ALL
select 6 day, 0.15 percentage, 65 value, 460 cum_value UNION ALL
select 7 day, 0.13 percentage, 80 value, 540 cum_value UNION ALL
select 8 day, 0.1 percentage, 95 value, 635 cum_value UNION ALL
select 9 day, 0.1 percentage, 45 value, 680 cum_value UNION ALL
select 10 day, 0.1 percentage, 75 value, 755 cum_value UNION ALL
select 11 day, 0.08 percentage, 86 value, 841 cum_value UNION ALL
select 12 day, 0.08 percentage, 48 value, 889 cum_value UNION ALL
select 13 day, 0.05 percentage, 57 value, 946 cum_value UNION ALL
select 14 day, 0.05 percentage, null value, null cum_value UNION ALL
select 15 day, 0.05 percentage, null value, null cum_value UNION ALL
select 16 day, 0.05 percentage, null value, null cum_value UNION ALL
select 17 day, 0.05 percentage, null value, null cum_value UNION ALL
select 18 day, 0.05 percentage, null value, null cum_value UNION ALL
select 19 day, 0.05 percentage, null value, null cum_value UNION ALL
select 20 day, 0.05 percentage, null value, null cum_value UNION ALL
select 21 day, 0.05 percentage, null value, null cum_value UNION ALL
select 22 day, 0.04 percentage, null value, null cum_value UNION ALL
select 23 day, 0.04 percentage, null value, null cum_value UNION ALL
select 24 day, 0.04 percentage, null value, null cum_value UNION ALL
select 25 day, 0.04 percentage, null value, null cum_value UNION ALL
select 26 day, 0.04 percentage, null value, null cum_value UNION ALL
select 27 day, 0.04 percentage, null value, null cum_value UNION ALL
select 28 day, 0.04 percentage, null value, null cum_value UNION ALL
select 29 day, 0.04 percentage, null value, null cum_value UNION ALL
select 30 day, 0.04 percentage, null value, null cum_value UNION ALL
select 31 day, 0.04 percentage, null value, null cum_value
),
-- 递归计算部分
recursive_calc AS (
-- 锚点成员：取第一行已有数据作为起点
SELECT day, percentage, value, cum_value
FROM raw_data
WHERE day = 1
UNION ALL
-- 递归成员：逐行计算后续每一天的cum_value
SELECT 
  curr.day,
  curr.percentage,
  curr.value,
  CASE 
    WHEN curr.value IS NOT NULL THEN prev.cum_value + curr.value
    ELSE prev.cum_value * (1 + curr.percentage)
  END AS cum_value
FROM recursive_calc prev
JOIN raw_data curr ON curr.day = prev.day + 1
)
SELECT * FROM recursive_calc
ORDER BY day;

计算逻辑说明 #

• 锚点成员固定取day=1的初始行作为递归的起点
• 递归部分每次关联上一行计算结果，按照规则判断：
  • 当前行value非空时，直接在前序累积值基础上加当前value
  • 当前行value为空时，用前序累积值乘以(1+当前百分比)得到当前累积值
• 执行后输出结果和预期值完全一致。

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