Challenging: BigQuery multiply and sum recursively

user_8867778 0
Viewed 252

I have what I thought was a simple problem, but maybe I underestimated it (or BigQuery's lack of recursive CTEs):

Let's say that I have this 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

And so forth until the end of 2021. That is:

Column a only has a value in the first row, while the others vary until the end of the table.

And I wish to generate another column ('calculation'), with the following operation until the end of the table:

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

Thus giving a result like this:

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

The key being: I need to get the result from the previous row and then apply the same operation to it and so forth.

What would be a good way to do this?

(Note: C might be larger than 709.7827, which rules out using EXP(C) Mikhail's answer below – though it's a promising start!)

Thanks!

google-bigquery recursive-query recursion javascript
1 Answers

You are right - BigQuery does not support [yet hopefully] recursive CTE, but there is always workaround.

So, you problem can be expressed by below formula for Nth row

enter image description here

which can [relatively easy] be implemented with window / analytic functions as in below example

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)

if applied to sample data in your question - output is

enter image description here

As you can see here - additional trick is using LN and then EXP functions in combination with analytic function SUM.
LN allows you to transform multiplication (of values in rows within set window) into sum of those values - LN(v1 * v2 * ... * vN) = LN(v1)+LN(v2)+...+LN(vN). And then - using EXP gives you actual multiplication result - EXP(LN(v1v2... vN)) = v1v2*... *vN. - which is mostly what your calculation formula is

user_52219440
Related