英文:
How to declare a list/array/struct type variable in BigQuery
问题
DECLARE list ARRAY<int64>;
SET list = [1, 2];
WITH
subquery AS (
SELECT
1 AS col1
UNION ALL
SELECT
2 AS col1
UNION ALL
SELECT
3 AS col1 )
SELECT
col1
FROM
subquery
WHERE
col1 IN UNNEST(list)
英文:
How can I declare a list type variable in BigQuery so that I can use it in a where clause? I have this code
WITH
subquery AS (
SELECT
1 AS col1
UNION ALL
SELECT
2 AS col1
UNION ALL
SELECT
3 AS col1 )
SELECT
col1
FROM
subquery
WHERE
col1 IN (1, 2)
instead I would like to get to the point with the variable in the query
DECLARE list ARRAY;
SET list = (1,2);
WITH
subquery AS (
SELECT
1 AS col1
UNION ALL
SELECT
2 AS col1
UNION ALL
SELECT
3 AS col1 )
SELECT
col1
FROM
subquery
WHERE
col1 IN list
I have tried DECLARE list STRUCT [less than] int64,int64 [greater than] which it doesn't accept
答案1
得分: 11
尝试以下代码:
DECLARE list ARRAY
SET list = [1,2];
WITH
subquery AS (
SELECT
1 AS col1
UNION ALL
SELECT
2 AS col1
UNION ALL
SELECT
3 AS col1 )
SELECT
col1
FROM
subquery
WHERE
col1 IN UNNEST(list)
英文:
Try the following code:
DECLARE list ARRAY <INT64>;
SET list = [1,2];
WITH
subquery AS (
SELECT
1 AS col1
UNION ALL
SELECT
2 AS col1
UNION ALL
SELECT
3 AS col1 )
SELECT
col1
FROM
subquery
WHERE
col1 IN UNNEST(list)
答案2
得分: 2
DECLARE foo DEFAULT (SELECT AS STRUCT 2, 2, 2, 2);
英文:
The following syntax also seems to work, if you really want to use DECLARE with a STRUCT type, or for other people who find this post:
DECLARE foo DEFAULT (SELECT AS STRUCT 2, 2, 2, 2);
答案3
得分: 2
你可以一次性使用DECLARE
来声明一个数组并赋值,而不使用SET
。示例:
DECLARE myArray ARRAY <STRING> DEFAULT ["FIRST", "SECOND", "THIRD"];
SELECT myValues
FROM UNNEST(myArray) AS myValues
你也可以使用WITH
子查询来实现相同的效果。示例:
WITH myArray AS (
SELECT *
FROM UNNEST(["FIRST", "SECOND", "THIRD"]) AS myValues
)
SELECT myValues
FROM myArray
这两个查询将提供相同的结果:
+----------+
| myValues |
+----------+
| FIRST |
| SECOND |
| THIRD |
+----------+
英文:
You can DECLARE
an array and assign it values in one go, without using SET
. Example:
DECLARE myArray ARRAY <STRING> DEFAULT ["FIRST", "SECOND", "THIRD"];
SELECT myValues
FROM UNNEST(myArray) AS myValues
You may prefer a subquery, using WITH
. Example:
WITH myArray AS (
SELECT *
FROM UNNEST(["FIRST", "SECOND", "THIRD"]) AS myValues
)
SELECT myValues
FROM myArray
Both queries will provide the same result:
+----------+
| myValues |
+----------+
| FIRST |
| SECOND |
| THIRD |
+----------+
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论