Recursive calculation in BigQuery

Question

I need to calculate stock as a dinamic and recursive value, as the simple equation:

n value refers to a periodic time (month, day, year, etc)

Always when a stock value is negative, this is replaced by zero.

How can I calculate this in Big Query? Here is a example:

WITH `project.dataset.table` AS (
  SELECT 10 entrada, 5  venda, 8 quebra, 8 mes, 2019 ano UNION ALL
  SELECT 12, 8 , 3, 9, 2019  UNION ALL
  SELECT 20, 15, 2, 10, 2019  UNION ALL
  SELECT 30, 12, 2, 11, 2019  UNION ALL
  SELECT 20, 10, 5, 12, 2019  UNION ALL
  SELECT 30, 12, 2, 1, 2020  UNION ALL
  SELECT 30, 12, 2, 2, 2020  UNION ALL
  SELECT 30, 12, 2, 3, 2020  
)

SELECT entrada, venda, quebra,
  variacao,
  greatest(coalesce(lag(variacao) over (partition by 'project.dataset.table' order by ano, mes),0) + entrada - venda - quebra, 0) as estoque

FROM (
  SELECT *, 
    entrada - venda - quebra AS variacao
  FROM `project.dataset.table`
)

And the expected result would be:

entrada venda   quebra  variacao    estoque
10  5   8   -3  0
12  8   3   1   1
20  15  2   3   4
30  12  2   16  20
20  10  5   5   25
30  12  2   16  41
30  12  2   16  57
30  12  2   16  73

But, the results for the code above is:

entrada venda   quebra  variacao    estoque
10  5   8   -3  0
12  8   3   1   0
20  15  2   3   4
30  12  2   16  19
20  10  5   5   21
30  12  2   16  21
30  12  2   16  32
30  12  2   16  32

Thanks in advance!

Answer 1

BigQuery does not support recursive operations natively. Try array_agg() combined with JavaScript user-defined function, but this approach is not very scalable:

CREATE TEMP FUNCTION special_sum(x ARRAY<INT64>)
RETURNS INT64
LANGUAGE js
AS """
  var estoque = 0;
  for (const num of x)
  {
     estoque = Math.max(estoque + parseInt(num), 0);
  }
  return estoque;
""";

WITH `project.dataset.table` AS (
  SELECT 10 entrada, 5  venda, 8 quebra, 8 mes, 2019 ano UNION ALL
  SELECT 12, 8 , 3, 9, 2019  UNION ALL
  SELECT 20, 15, 2, 10, 2019  UNION ALL
  SELECT 30, 12, 2, 11, 2019  UNION ALL
  SELECT 20, 10, 5, 12, 2019  UNION ALL
  SELECT 30, 12, 2, 1, 2020  UNION ALL
  SELECT 30, 12, 2, 2, 2020  UNION ALL
  SELECT 30, 12, 2, 3, 2020  
)
select *, 
 special_sum(array_agg(entrada - venda - quebra) over (order by ano, mes rows unbounded preceding)) as estoque
from `project.dataset.table`