Warm tip: This article is reproduced from serverfault.com, please click

其他-如何在BigQuery中声明列表/数组/结构类型变量

(其他 - How to declare a list/array/struct type variable in BigQuery)

发布于 2020-01-06 15:12:35

如何在BigQuery中声明列表类型变量,以便可以在where子句中使用它?我有这个代码

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)

相反,我想用查询中的变量来说明问题

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

我尝试了DECLARE列表STRUCT [小于] int64,int64 [大于]它不接受

Questioner
user147529
Viewed
12
rmesteves 2020-01-06 23:30:21

尝试以下代码:

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)