在bigquery脚本中将Array的列设置为变量值 [英] Set column of Array to variable value in bigquery scripting
本文介绍了在bigquery脚本中将Array的列设置为变量值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试获取数组中多列的值,并将它们设置为可在循环中用于执行其他操作的变量.谢谢.
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脚本,请参见以下示例
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脚本中将Array的列设置为变量值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文