BigQuery中创建临时表的语法问题求助

你的代码是SQL Server风格的写法，和BigQuery的语法差异主要有以下几点，对应修正方案如下：

核心问题点 #

• 临时表语法错误：BigQuery不支持#表名这种SQL Server风格的临时表写法，正确的临时表需要用TEMPORARY关键字声明，或者直接使用CTE（公共表表达式）替代临时表，更适合一次性查询场景。
• 数据类型不匹配：BigQuery没有nvarchar、numeric这类SQL Server类型，对应替换为STRING、NUMERIC/FLOAT64；datetime类型在BigQuery中是DATETIME，需确保源表字段类型匹配。
• 冗余操作：不需要先定义表结构再执行插入，BigQuery支持直接通过查询结果创建表（CTAS语法），既简洁又能避免字段类型不匹配的问题；同时INSERT语句中的ORDER BY无意义，因为BigQuery的表存储是无序的，排序只需在最终查询时添加。

修正后的代码（临时表版本） #

CREATE TEMPORARY TABLE PercentPopulationVaccinated 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 current_population_vaccinated
FROM `covid-data-project-368212.covid.coviddeaths` dea
JOIN `covid-data-project-368212.covid.covidvaccinations` vac
  ON dea.location = vac.location
  AND dea.date = vac.date
WHERE dea.continent IS NOT NULL;

SELECT 
  *, 
  (current_population_vaccinated/population)*100 AS vaccination_percentage
FROM PercentPopulationVaccinated
ORDER BY location, date;

更简洁的CTE版本（推荐） #

如果只是一次性使用中间结果，用CTE不需要显式创建临时表，代码更紧凑：

WITH PercentPopulationVaccinated 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 current_population_vaccinated
  FROM `covid-data-project-368212.covid.coviddeaths` dea
  JOIN `covid-data-project-368212.covid.covidvaccinations` vac
    ON dea.location = vac.location
    AND dea.date = vac.date
  WHERE dea.continent IS NOT NULL
)
SELECT 
  *, 
  (current_population_vaccinated/population)*100 AS vaccination_percentage
FROM PercentPopulationVaccinated
ORDER BY location, date;

额外优化说明 #

• 移除了PARTITION BY dea.location ORDER BY dea.location中的冗余排序字段，因为分区已经按location分组，仅按date排序即可实现累计求和的逻辑。
• 新增了vaccination_percentage别名，让最终结果的百分比字段更清晰。

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