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

Question

我该如何在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 list STRUCT [less than] int64,int64 [greater than]，但它不兼容。

- user147529

Answer 1

尝试以下代码：

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)

- rmesteves

---

谢谢！我很难理解数组和结构体。有什么好的资源吗？ - user147529

您可以在下面的链接中找到有关此信息的有用资料：https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql - rmesteves

Answer 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    |
+----------+

- FreshSnow

Answer 3

以下语法似乎也可以使用，如果您真的想要使用DECLARE与STRUCT类型，或者适用于其他人发现这篇文章的情况：

DECLARE foo DEFAULT (SELECT AS STRUCT 2, 2, 2, 2);

- Sean