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

我有数千个脚本，其中包括循环数据集并使用变量进行累积。例如：

// 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。让我知道如何解决这个问题。

Edit:虽然我更倾向于仅使用 SQL 方法但是BigQuery 脚本 https://cloud.google.com/blog/products/data-analytics/command-and-control-now-easier-in-bigquery-with-scripting-and-stored-procedures似乎也是有前途的选择。

我已经成功地在 BigQuery 中使用 SQL UDF 重现了您的脚本，我使用了 StandardSQL 而不是 Legacy SQL，您可以阅读更多相关信息here https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions。我想说 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 and var2。此外，该数组从位置 1 开始test_v1功能。

最后，输出是：

我希望它有帮助。