BigQuery 中的递归计算 [英] Recursive calculation in BigQuery

问题描述

我需要将库存计算为动态和递归值，如简单的等式:

n 值是指周期时间(月、日、年等)

当股票值为负时，它总是被零替换.

如何在 Big Query 中计算此值?下面是一个例子:

WITH `project.dataset.table` AS (选择 10 entrada、5 venda、8 quebra、8 mes、2019 年 ano UNION ALL选择 12, 8 , 3, 9, 2019 联合所有选择 20, 15, 2, 10, 2019 联合所有选择 30, 12, 2, 11, 2019 联合所有选择 20, 10, 5, 12, 2019 联合所有选择 30, 12, 2, 1, 2020 联合所有选择 30, 12, 2, 2, 2020 联合所有选择 30, 12, 2, 3, 2020)选择 entrada、venda、quebra、瓦拉考，最大的(coalesce(滞后(variacao)超过(按project.dataset.table"顺序分区，按ano，mes)，0)+ entrada - venda - quebra，0)作为estoque从 (选择 *，entrada - 文达 - quebra AS variacao从`project.dataset.table`)

预期的结果是:

entrada venda quebra variacao estoque10 5 8 -3 012 8 3 1 120 15 2 3 430 12 2 16 2020 10 5 5 2530 12 2 16 4130 12 2 16 5730 12 2 16 73

但是，上面代码的结果是:

entrada venda quebra variacao estoque10 5 8 -3 012 8 3 1 020 15 2 3 430 12 2 16 1920 10 5 5 2130 12 2 16 2130 12 2 16 3230 12 2 16 32

提前致谢！

解决方案

BigQuery 本身不支持递归操作.尝试将 array_agg() 与

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!

解决方案

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`

这篇关于BigQuery 中的递归计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！