javascript - 挑战 : BigQuery multiply and sum recursively

Question

我有一个我认为很简单的问题，但也许我低估了它(或者 BigQuery 缺乏递归 CTE):
假设我有这张 table :

SELECT DATE("2021-09-01") AS date, 50 AS a, 0.5 AS b, 2 AS c

UNION ALL

SELECT DATE("2021-09-02") AS date, NULL AS a, 0.6 AS b, 1 AS c

UNION ALL

SELECT DATE("2021-09-03") AS date, NULL AS a, 0.4 AS b, 3 AS c

依此类推，直到 2021 年底。即:
栏目 a只有第一行有一个值，而其他的直到表的末尾才变化。
我希望生成另一列(“计算”)，执行以下操作直到表结束:

row 1 = a * (1 - b) + c
row 2 = row 1 * (1 - b) + c
row 3 = row 2 * (1 - b) + c
etc.

因此给出这样的结果:

date        a   b    c  calculation
2021-09-01  50  0.5  2  27
2021-09-02      0.6  1  11.8
2021-09-03      0.4  3  10.08

关键是:我需要从前一行获取结果，然后对其应用相同的操作等等。
这样做的好方法是什么？
(注意:C 可能大于 709.7827，这就排除了使用 EXP(C) Mikhail 下面的答案——尽管这是一个有希望的开始!)
谢谢!

Answer 1

您是对的 - BigQuery 不支持 [但希望] 递归 CTE，但总有解决方法。
所以，你的问题可以用以下公式表示 电话 扔

这可以[相对容易]用窗口/分析函数实现，如下例所示

with temp as (
  select *, 
    row_number() over(order by date) pos,
    exp(sum(ln(1-b)) over(order by date)) p,
    min(a) over() * exp(sum(ln(1-b)) over(order by date)) + c calculation,
  from `project.dataset.table`
)
select any_value(struct(t1.date, t1.a, t1.b, t1.c)).*, 
  any_value(t1.calculation) + sum(
    if(t1.pos > t2.pos, t2.c * t1.p / t2.p, 0)
  ) calculation
from temp t1
join temp t2
on t1.pos >= t2.pos
group by to_json_string(t1) 

如果应用于您问题中的样本数据 - 输出是

正如你在这里看到的 - 额外的技巧是使用 LN 然后 EXP 函数与分析函数 SUM 结合使用。
LN 允许您将乘法(设置窗口内行中的值)转换为这些值的总和 - LN(v1 * v2 * ... * vN) = LN(v1)+LN(v2)+...+LN( vN)。然后 - 使用 EXP 给你实际的乘法结果 - EXP(LN(v1v2... vN)) = v1v2*... *vN。 - 这主要是你的计算公式