如何在BigQuery中创建临时表及WITH语句语法报错处理

• 报错的核心是WITH子句（公共表表达式CTE）无法单独作为完整SQL执行，定义完CTE后必须紧跟引用该CTE的执行语句
• 你想要结合CREATE TEMPORARY TABLE创建临时表的话，语法顺序错误，需要将建表语句放在CTE之前

你当前的场景不需要额外嵌套WITH子句，直接用以下语法即可创建临时表：

CREATE TEMPORARY TABLE analysis_fall_2021_season AS
SELECT
  start_station_name,
  end_station_name,
  EXTRACT(DATE FROM started_at) AS start_date,
  EXTRACT(DATE FROM ended_at) AS end_date, 
  EXTRACT(TIME FROM started_at) AS start_time,
  EXTRACT(TIME FROM ended_at) AS end_time,
  DATETIME_DIFF(ended_at,started_at, MINUTE) AS total_length,
  member_casual
FROM (
  SELECT
    fall_analysis.ride_id, 
    fall_analysis.started_at, 
    fall_analysis.ended_at, 
    fall_analysis.start_station_name, 
    fall_analysis.end_station_name, 
    fall_analysis.member_casual
  FROM `ciclystic.cyclistic_seasonal_analysis.fall_202010` AS fall_analysis
  INNER JOIN `ciclystic.cyclistic_seasonal_analysis.fall_202011` AS fall_202011
    ON fall_analysis.member_casual = fall_202011.member_casual
  INNER JOIN `ciclystic.cyclistic_seasonal_analysis.fall_202012` AS fall_202012
    ON fall_analysis.member_casual = fall_202012.member_casual
);

如果确实需要先定义CTE再生成临时表，通用语法格式如下：

CREATE TEMPORARY TABLE 临时表名 AS
WITH CTE名称 AS (
  CTE查询逻辑
)
SELECT * FROM CTE名称;

额外注意你原代码里的字段名total_lenght是拼写错误，正确拼写应为total_length。

内容的提问来源于stack exchange，提问作者Rodrigo