如何在BigQuery中声明一个列表/数组/结构类型的变量

huangapple go评论61阅读模式
英文:

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 &lt;INT64&gt;;
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 &lt;STRING&gt; DEFAULT [&quot;FIRST&quot;, &quot;SECOND&quot;, &quot;THIRD&quot;];

SELECT myValues
  FROM UNNEST(myArray) AS myValues

You may prefer a subquery, using WITH. Example:

WITH myArray AS (
  SELECT *
    FROM UNNEST([&quot;FIRST&quot;, &quot;SECOND&quot;, &quot;THIRD&quot;]) AS myValues
)
SELECT myValues
  FROM myArray

Both queries will provide the same result:

+----------+
| myValues |
+----------+
| FIRST    |
| SECOND   | 
| THIRD    |
+----------+

huangapple
  • 本文由 发表于 2020年1月6日 23:12:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/59614517.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定