时间戳

首页 > 解决方案 > 自定义滚动计算

google-bigquery - 自定义滚动计算

问题描述

假设我有一个模型，该模型具有A(t)并B(t)受以下方程式控制：

A(t) = {
  WHEN B(t-1) < 10 : B(t-1)
  WHEN B(t-1) >=10 : B(t-1) / 6
}

B(t) = A(t) * 2

下表作为输入提供。

SELECT * FROM model ORDER BY t;

| t | A    | B    |
|---|------|------|
| 0 | 0    | 9    |
| 1 | null | null |
| 2 | null | null |
| 3 | null | null |
| 4 | null | null |

即我们知道 和 的A(t=0)值B(t=0)。

对于每一行，我们要使用上面的等式计算A&的值。B

决赛桌应该是：

| t | A | B  |
|---|---|----|
| 0 | 0 | 9  |
| 1 | 9 | 18 |
| 2 | 3 | 6  |
| 3 | 6 | 12 |
| 4 | 2 | 4  |

我们尝试过使用滞后，但由于模型的递归性质，我们最终只能得到A& Bat(t=1)

CREATE TEMPORARY FUNCTION A_fn(b_prev FLOAT64) AS (
  CASE
    WHEN b_prev < 10 THEN b_prev
    ELSE b_prev / 6.0
  END
);

SELECT
t,
CASE WHEN t = 0 THEN A ELSE A_fn(LAG(B) OVER (ORDER BY t)) END AS A,
CASE WHEN t = 0 THEN B ELSE A_fn(LAG(B) OVER (ORDER BY t)) * 2 END AS B
FROM model
ORDER BY t;

产生：

| t | A    | B    |
|---|------|------|
| 0 | 0    | 9    |
| 1 | 9    | 18   |
| 2 | null | null |
| 3 | null | null |
| 4 | null | null |

每一行都依赖于它上面的行。似乎应该可以一次计算一行，同时遍历行？还是 BigQuery 不支持这种类型的窗口？

如果不可能，您有什么建议？

标签： google-bigquery

解决方案

第 1 轮- 起点

以下是 BigQuery 标准 SQL 并且（对我而言）最多可处理 3M 行 

#standardSQL
CREATE TEMP FUNCTION x(v FLOAT64, t INT64)
RETURNS ARRAY<STRUCT<t INT64, v FLOAT64>>
LANGUAGE js AS """
  var i, result = [];
  for (i = 1; i <= t; i++) { 
    if (v < 10) {v = 2 * v} 
    else {v = v / 3};
    result.push({t:i, v});
  };
  return result
""";
SELECT 0 AS t, 0 AS A, 9 AS B UNION ALL 
SELECT line.t, line.v / 2, line.v FROM UNNEST(x(9, 3000000)) line

在此处输入图像描述

超过 3M 行会产生Resources exceeded during query execution: UDF out of memory.
为了克服这个问题——我认为你应该在客户端上实现它——所以没有应用 JS UDF 限制。我认为这是合理的“解决方法”，因为无论如何你在 BQ 中没有真正的数据，只有一个起始值（本例中为 9）。但是，即使您在表中确实有其他有价值的列 - 您也可以将生成的结果 JOIN 返回到表 ON t 值 - 所以应该没问题！

第 2 轮- 可能是数十亿...... - 所以让我们处理规模，并行化

下面是一个避免 JS UDF 资源和/或内存错误的小技巧
所以，我能够运行它2B rows in one shot! 

#standardSQL
CREATE TEMP FUNCTION anchor(seed FLOAT64, len INT64, batch INT64)
RETURNS ARRAY<STRUCT<t INT64, v FLOAT64>> LANGUAGE js AS """
  var i, result = [], v = seed;
  for (i = 0; i <= len; i++) { 
    if (v < 10) {v = 2 * v} else {v = v / 3};
    if (i % batch == 0) {result.push({t:i + 1, v})};
  }; return result
""";
CREATE TEMP FUNCTION x(value FLOAT64, start INT64, len INT64)
RETURNS ARRAY<STRUCT<t INT64, v FLOAT64>>
LANGUAGE js AS """
  var i, result = []; result.push({t:0, v:value});
  for (i = 1; i < len; i++) { 
    if (value < 10) {value = 2 * value} else {value = value / 3};
    result.push({t:i, v:value});
  }; return result
""";
CREATE OR REPLACE TABLE `project.dataset.result` AS
WITH settings AS (SELECT 9 init, 2000000000 len, 1000 batch), 
  anchors  AS (SELECT line.* FROM settings, UNNEST(anchor(init, len, batch)) line)
SELECT 0 AS t, 0 AS A, init AS B FROM settings UNION ALL
SELECT a.t + line.t, line.v / 2, line.v
FROM settings, anchors a, UNNEST(x(v, t, batch)) line

在上面的查询中 - 您在下面的行中“控制”初始值 

WITH settings AS (SELECT 9 init, 2000000000 len, 1000 batch),

在上面的示例中，9是初始值，2,000,000,000 是要计算的行数，1000 是要处理的批处理（这对于防止 BQ 引擎抛出资源和/或内存错误很重要 - 你不能让它太大或太小了——我觉得我对它需要什么有了一些了解——但还不足以尝试制定它）

一些统计数据（设置 - 执行时间）： 

  1M: SELECT 9 init,    1000000 len,  1000 batch  -  0 min  9 sec   
 10M: SELECT 9 init,   10000000 len,  1000 batch  -  0 min 50 sec
100M: SELECT 9 init,  100000000 len,   600 batch  -  3 min  4 sec
100M: SELECT 9 init,  100000000 len,    40 batch  -  2 min 56 sec   
  1B: SELECT 9 init, 1000000000 len, 10000 batch  - 29 min 39 sec
  1B: SELECT 9 init, 1000000000 len,  1000 batch  - 27 min 50 sec
  2B: SELECT 9 init, 2000000000 len,  1000 batch  - 48 min 27 sec

第三轮- 一些想法和评论

显然，正如我在上面的＃1 中提到的 -这种类型的计算更适合在您选择的客户端上实现- 所以我很难判断上述的实用价值 - 但我真的玩得很开心！实际上，我有一些更酷的想法，并且还实施并使用它们 - 但上面（在＃2中）是最实用/可扩展的一个

注意：上述解决方案中最有趣的部分是anchors表格。生成并允许在批量大小的间隔中设置锚点非常便宜 - 因此，您可以例如计算行的值 = 2,000,035 或 1,123,456,789（例如），而无需实际处理所有先前的行 - 这将花费几分之一秒. 或者您可以通过使用各自的锚等启动多个线程/计算来并行化所有行的计算。相当多的机会。

最后，这真的取决于你的具体用例走哪条路——所以我把它留给你

推荐阅读