将数组的列设置为 bigquery 脚本中的变量值 [英] Set column of Array to variable value in bigquery scripting

问题描述

我正在尝试获取数组中多列的值，并将它们设置为可在循环中用于执行其他操作的变量.谢谢.

I'm trying to get the value of multiple columns in an array and set them as a variable that can be used in the loop to do something else. Thanks.

DECLARE the_array ARRAY<STRUCT<value1 STRING,value2 STRING>>;

SET the_array = (
  SELECT ARRAY_AGG(STRUCT(value1,value2))
  FROM `project.dataset.table`
  WHERE somthing = 'somthing'
);




LOOP
  SET i = i + 1;

  SET var1 = the_array[ORDINAL(????)]; // what do I do here?


  SET var2 = the_array[ORDINAL(???)]; // what do I do here?



  IF i > ARRAY_LENGTH(the_array) THEN 
    LEAVE; 
  END IF;
 
 insert into `project.dataset.other_table` values(var1,var2);


END LOOP;

推荐答案

如果您正在寻找实现上述结果的正确方法 - 以基于集合的方式进行，这是在 SQL 中表达逻辑的最有效方式

If you are looking for correct way of achieving above result - do it in set based way which is most effective way of expressing your logic in SQL

INSERT INTO `project.dataset.other_table`
SELECT value1, value2
FROM `project.dataset.table`
WHERE somthing = 'somthing'      

同时，如果您的问题的目的是学习 BigQuery Scripting - 请参见下面的示例

Meantime, if purpose of your question is to learn BigQuery Scripting - see below example

DECLARE the_array ARRAY<STRUCT<value1 STRING,value2 STRING>>;
DECLARE i INT64 DEFAULT 0;
DECLARE var1, var2 STRING;

SET the_array = (
  SELECT ARRAY_AGG(STRUCT(value1,value2))
  FROM `project.dataset.table`
  WHERE somthing = 'somthing'
);

LOOP
  SET i = i + 1;

  IF i > ARRAY_LENGTH(the_array) THEN 
    LEAVE; 
  END IF;

  SET var1 = the_array[ORDINAL(i)].value1; 
  SET var2 = the_array[ORDINAL(i)].value2; 
 
 insert into `project.dataset.other_table` values(var1,var2);

END LOOP;

这篇关于将数组的列设置为 bigquery 脚本中的变量值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！