BigQuery中循环/递归逻辑实现留存曲线的问题求助

问题背景 #

需基于现有数据集填充Count列空白行，计算逻辑为：第n行Count = 第n-1行Count × 第n行Degredation（对应Excel中A11B12、A12B13的递推逻辑）。迁移原Vertica代码到BigQuery时，递归CTE实现失败；尝试用LN+EXP计算累积乘积时，因将浮点类型的rate转换为int64导致无法解决的浮点误差。

基础数据结构 #

Count   Degredation
1       567         1
2       436         0.7689594356
3       423         0.9701834862
4       376         0.8888888889
5       305         0.8111702128
6       234         0.7672131148
7       180         0.7692307692
8       143         0.7944444444
9       127         0.8881118881
10      100         0.7874015748
11                  0.7654321
12                  0.5463234
13                  0.9876543
14                  0.876435463
15                  0.9865432
16                  1
17                  0.456789

解决方案 #

方案一：递归CTE实现递推计算 #

BigQuery支持递归CTE，通过锚点查询获取有Count值的初始行，再递归计算后续空白行的Count值：

WITH recursive_data AS (
  -- 锚点：获取所有有Count值的行，生成排序用的period
  SELECT 
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS period,
    Count,
    Degredation
  FROM your_table_name
  WHERE Count IS NOT NULL
  
  UNION ALL
  
  -- 递归：计算下一行的Count值
  SELECT 
    rd.period + 1,
    ROUND(rd.Count * t.Degredation, 0) AS Count, -- 按需保留小数或转整数
    t.Degredation
  FROM recursive_data rd
  JOIN your_table_name t
    ON rd.period + 1 = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
  WHERE t.Count IS NULL
)
SELECT * FROM recursive_data ORDER BY period;

注：如果表中有天然排序列（如period），直接用该列替代ROW_NUMBER生成的period，避免排序歧义。

方案二：修正窗口函数累积乘积计算（避免浮点误差） #

原代码错误地将浮点类型rate转为int64导致精度丢失，修正后保留浮点类型计算，按需转换结果：

SELECT
  subscription_number_id,
  period,
  -- 计算Count预测值：初始Count × 后续Degredation累积乘积
  FIRST_VALUE(Count) OVER(PARTITION BY subscription_number_id ORDER BY period ROWS UNBOUNDED PRECEDING)
  * EXP(SUM(LN(NULLIF(Degredation, 0))) OVER(PARTITION BY subscription_number_id ORDER BY period ROWS BETWEEN 1 PRECEDING AND CURRENT ROW))
  AS predicted_count,
  
  -- 修正后的各类rate累积乘积计算
  EXP(SUM(LN(NULLIF(renewal_count_rate, 0))) OVER(PARTITION BY subscription_number_id ORDER BY period ROWS UNBOUNDED PRECEDING)) AS renewal_count_curve,
  EXP(SUM(LN(NULLIF(potential_renewal_count_rate, 0))) OVER(PARTITION BY subscription_number_id ORDER BY period ROWS UNBOUNDED PRECEDING)) AS potential_renewal_count_rate_curve,
  EXP(SUM(LN(NULLIF(pre_tax_amount_rate, 0))) OVER(PARTITION BY subscription_number_id ORDER BY period ROWS UNBOUNDED PRECEDING)) AS pre_tax_amount_rate_curve,
  EXP(SUM(LN(NULLIF(net_pre_tax_amount_rate, 0))) OVER(PARTITION BY subscription_number_id ORDER BY period ROWS UNBOUNDED PRECEDING)) AS net_pre_tax_amount_rate_curve,
  EXP(SUM(LN(NULLIF(refunded_amount_rate, 0))) OVER(PARTITION BY subscription_number_id ORDER BY period ROWS UNBOUNDED PRECEDING)) AS refunded_amount_rate_curve
FROM your_table_name
ORDER BY subscription_number_id, period;

说明：

1. 移除cast(xxx as int64)，保留rate浮点类型避免精度丢失；
2. 用FIRST_VALUE获取初始Count，结合后续Degredation累积乘积实现递推；
3. 需整数结果时，可在计算后用ROUND()或CAST(... AS INT64)转换。

内容的提问来源于stack exchange，提问作者Jalal.Hassan