BigQuery SQL中从WITH临时表创建数据集内永久表的正确语法是什么

你之前执行CREATE TABLE报错的核心原因有两个：

1. WITH子句中定义的临时表名为temptable，但后续查询引用了不存在的combined_rides，触发语法错误
2. 没有正确拼接创建表的声明语句

修正后的完整可运行代码如下：

-- 下述语法表不存在时自动创建，表已存在时覆盖原有数据
CREATE OR REPLACE TABLE `project.data_set.permtable`
AS
WITH
  temptable AS ( 
        -- 2021年5月数据
  SELECT
    ride_id,
    rideable_type,
    CAST(started_at AS TIMESTAMP ) AS started_at,
    CAST(ended_at AS TIMESTAMP ) AS ended_at,
    start_station_name,
    CAST(start_station_id AS STRING ) AS start_station_id,
    end_station_name,
    CAST(end_station_id AS STRING ) AS end_station_id,
    start_lat,
    start_lng,
    end_lat,
    end_lng,
    member_casual,
    timestamp_diff (ended_at,started_at, minute) AS trip_duration,
    EXTRACT(DAYOFWEEK FROM started_at) AS day_of_week
  FROM
    `project.data_set.table1`
  UNION ALL
    -- 2021年6月数据
  SELECT
    ride_id,
    rideable_type,
    CAST(started_at AS TIMESTAMP ) AS started_at,
    CAST(ended_at AS TIMESTAMP ) AS ended_at,
    start_station_name,
    CAST(start_station_id AS STRING ) AS start_station_id,
    end_station_name,
    CAST(end_station_id AS STRING ) AS end_station_id,
    start_lat,
    start_lng,
    end_lat,
    end_lng,
    member_casual,
    timestamp_diff (ended_at,started_at, minute) AS trip_duration,
    EXTRACT(DAYOFWEEK FROM started_at) AS day_of_week
  FROM
    `project.data_set.table2` )

SELECT * FROM temptable
 WHERE
    NOT( start_station_id IS NULL
      OR end_station_id IS NULL
      OR start_lat IS NULL
      OR end_lat IS NULL
      OR start_station_name LIKE '%CHECKING%'
      OR end_station_name LIKE '%CHECKING%'
      OR trip_duration < 1 )

可选配置说明 #

• 仅当目标表不存在时才创建写入，不覆盖已有数据：将CREATE OR REPLACE TABLE替换为CREATE TABLE IF NOT EXISTS
• 往已有的目标表中追加查询结果：将CREATE OR REPLACE TABLE project.data_set.permtable AS替换为INSERT INTO project.data_set.permtable``

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