在BigQuery中紧跟SET的WITH语句

Question

我正在将几个SQL语句流水线连接在一起，发现从WITH语句开始执行并使用结果来设置变量时遇到了问题。我得到的错误是：

Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword SET at [17:2]

这些语句看起来像

WITH newtable AS (SELECT session, var FROM `table` WHERE session...)
(SET myvariable = (SELECT count(*) FROM newtable WHERE ....);

Answer 1

根据本GCP文档，只能在SELECT语句之后使用WITH：https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#sql_syntax

考虑到这个限制，您只需使用DDL语句从查询结果创建一个表并设置变量即可。

create table newtable AS (SELECT session, var FROM table);

DECLARE myvariable NUMERIC;
SET myvariable = (SELECT count(*) FROM newtable);