0
135
ViewsChallenging: BigQuery multiply and sum recursively
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!
1 answers
Answer question0
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
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
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