如何在BigQuery中创建表？创建临时表持续报错求助

咱们先理清楚你代码里的几个核心问题，这些就是导致报错的根源：

报错原因拆解 #

1. SQL语句缺少结束分号：BigQuery要求每个独立SQL语句必须用分号结尾，你的DROP、CREATE、INSERT、SELECT连在一起未分隔，会触发语法解析错误。
2. 列定义错误使用表别名：在CREATE TABLE的列列表里，你写了dea.continent STRING这种带查询别名的格式，这完全没必要——列定义只需要写列名+数据类型，和后面查询里的表别名无关。
3. 冗余的多步骤操作：你先建空表、再插入数据、最后再查询计算百分比，其实BigQuery支持直接用CREATE TABLE ... AS SELECT一步完成，既省步骤又不容易出错。
4. 临时表语法混淆：如果你本来是想创建临时表，当前代码写的是永久表的完整路径（Portfolio_Covid_Data.percentage_population_vaccinated），临时表不需要指定数据集，语法是CREATE TEMP TABLE 表名。

修复后的永久表创建代码 #

下面是修正后的完整代码，一步完成表创建、数据写入，同时直接计算出你需要的百分比列：

DROP TABLE IF EXISTS Portfolio_Covid_Data.percentage_population_vaccinated;
CREATE TABLE Portfolio_Covid_Data.percentage_population_vaccinated
AS
SELECT 
  dea.continent,
  dea.location,
  dea.date,
  dea.population,
  vac.new_vaccinations,
  SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.date) AS rolling_people_vaccinated,
  ROUND((SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.date)/dea.population)*100,2) AS percentage_population_vaccinated
FROM `big-dataset.Portfolio_Covid_Data.covid_deaths` AS dea
JOIN `big-dataset.Portfolio_Covid_Data.covid_vaccinations` AS vac
  ON dea.location = vac.location 
  AND dea.date = vac.date;

临时表的正确创建方式 #

如果你的需求是创建会话级临时表（会话结束后自动删除，无需手动清理），语法如下：

CREATE TEMP TABLE percentage_population_vaccinated
AS
SELECT 
  dea.continent,
  dea.location,
  dea.date,
  dea.population,
  vac.new_vaccinations,
  SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.date) AS rolling_people_vaccinated,
  ROUND((SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.date)/dea.population)*100,2) AS percentage_population_vaccinated
FROM `big-dataset.Portfolio_Covid_Data.covid_deaths` AS dea
JOIN `big-dataset.Portfolio_Covid_Data.covid_vaccinations` AS vac
  ON dea.location = vac.location 
  AND dea.date = vac.date;

-- 直接查询临时表即可，不需要指定数据集
SELECT * FROM percentage_population_vaccinated;

额外实用提示 #

• 权限检查：确保你对目标数据集Portfolio_Covid_Data有创建表和写入数据的权限，权限不足也会导致报错。
• 数据类型兼容：如果源表的population或new_vaccinations是整数类型，用NUMERIC是没问题的，但如果是其他类型，记得做必要的转换。
• 临时数据优先用CTE：如果不需要永久保存数据，用公共表表达式（CTE）更高效，不用占用存储空间：

WITH percentage_population_vaccinated AS (
  SELECT 
    dea.continent,
    dea.location,
    dea.date,
    dea.population,
    vac.new_vaccinations,
    SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.date) AS rolling_people_vaccinated,
    ROUND((SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.date)/dea.population)*100,2) AS percentage_population_vaccinated
  FROM `big-dataset.Portfolio_Covid_Data.covid_deaths` AS dea
  JOIN `big-dataset.Portfolio_Covid_Data.covid_vaccinations` AS vac
    ON dea.location = vac.location 
    AND dea.date = vac.date
)
SELECT * FROM percentage_population_vaccinated;

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