将带有变量的循环转换为BigQuery SQL

Question

我有成千上万的脚本，其中包括循环数据集和使用变量的积累。例：

// assuming that 'ids' is populated from some BQ table
ids = [1, 2, 3, 4, 5]
var1 = "v1"   //initialize variable
var2 = "v2"   //initialize variable

for id in ids
    var1 = var2
    if (id > 2)
      var2 = var1 + "-" + item
    else
      var2 = id
    print(id, var1, var2)

这将产生以下产出：

1,v2,1
2,1,2
3,2,2-3
4,2-3,2-3-4
5,2-3-4,2-3-4-5

很少有其他事情需要考虑：

• 脚本可以涉及n个变量。
• 变量可以充当累加器，也可以简单地具有文本。
• 循环中的条件可以是更复杂的、多个嵌套的if / else条件。
• 脚本也可以有嵌套循环，这将循环n次或直到某些条件不满足。

是否可以在BigQuery SQL中支持它？我也开放给BigQuery UDF，但是UDF不支持可变状态。

此外，由于有成千上万这样的脚本，而不是手动执行，我想要自动转换。解析这些脚本对我来说不是问题，我只需要将其转换为等效的BigQuery SQL即可。让我知道如何处理这个问题。

编辑：，虽然我更倾向于使用SQL方法，但是BigQuery脚本看起来也很有前途。

Answer 1

我能够转换上述示例脚本的SQL查询：

with  t as
(select 1 as id union all
select 2 as id union all
select 3 as id union all
select 4 as id union all
select 5 as id)

select id, if(id = 1, 'v2', lag(var2) over(order by id)) var1, var2
from (
    select
      id,
      if(
        id > 2,
        string_agg(if(id > 1, concat('', '', cast(id as string)), null), '-') over (rows between unbounded preceding and current row),
        cast(id as string)
      ) var2
    from t
  )

就像这样，我还能够在纯SQL中转换大多数脚本。它只是围绕着它构建模型，它更复杂，但至少我正在进行手动转换。

Answer 2

我已经成功地在BigQuery中使用SQL复制了您的脚本，我使用了StandardSQL而不是Legacy，您可以阅读更多关于它的这里。我想指出，UDF支持JavaScript，我发现使用它的功能编写它更好。

我使用这个虚拟数据而不是声明一个数组：

​

​

下面是我开发的脚本，用于再现您使用代码完成的工作。

DECLARE VAR1 STRING DEFAULT 'v1';
DECLARE var2 STRING DEFAULT 'v2';

CREATE TEMPORARY FUNCTION test_v2 ( x1 string , x2 string,id INT64)
RETURNS STRING
LANGUAGE js AS """
var output= new Array(); 
i =0;
          while(i <= 5){
          x1=x2;
          if(i > 2) {
            x2 =x1 + " - " + i ;
            output.push(x2);

          } else{
                  x2 = i;
                  output.push(x2);
                } 
          i++;}return  output[id];
""";

CREATE TEMPORARY FUNCTION test_v1 ( x1 string , x2 string,id INT64)
RETURNS STRING
LANGUAGE js AS """
var output= new Array(); 
i =1;
output.push(x1);
          while(i <= 5){
          x1 = x2;
          if(i > 2) {
            x2 =x1 + " - " + i ;
            output.push(x1);

          } else{
                  output.push(x1);
                  x2 = i;
                } 
          i++;}return  output[id];
""";

SELECT ids,test_v1(var1,var2,ids) as v1, test_v2(var1,var2,ids) as var2 FROM `test-proj-261014.sample.ids` LIMIT 1000

注意，我必须编写两个不同的函数，每一行var1和var2一个函数。此外，数组从test_v1函数的位置1开始。

最后，产出如下：

​

​

希望能帮上忙。