编写CTE SQL代码时遇语法错误：期望关键字AS却得到'('求助

你遇到的问题是BigQuery不支持在CTE名称后直接用括号指定列名的语法，这种写法常见于SQL Server等数据库，但BigQuery遵循的标准SQL语法不认可这种格式。

修正后的代码 #

WITH PopulationVancine AS (
  SELECT 
    Death.date,
    Death.location, 
    Death.population, 
    Death.continent, 
    vacine.new_vaccinations, 
    SUM(vacine.new_vaccinations) OVER (
      PARTITION BY Death.location 
      ORDER BY Death.location, Death.date
    ) AS RollingPeopleVaccinated
  FROM `my-data-project-96387.PortfolioProjectSamp.CovidDeath` as Death
  INNER JOIN `my-data-project-96387.PortfolioProjectSamp.CovidVacination` as vacine
    ON Death.location = vacine.location
    AND Death.date = vacine.date
  WHERE Death.continent IS NOT NULL
)
SELECT *
FROM PopulationVancine

关键修改说明 #

• 移除了PopulationVancine后面的(Date, Location, population, continent, new_vacinations, RollingPeopleVaccinated)部分，BigQuery会自动从CTE内部的SELECT语句继承列名和别名。
• 同时去掉了SUM函数和括号之间的空格（虽然这不是语法错误，但保持代码风格更规范）。

如果确实需要显式指定CTE的列名，BigQuery的标准写法是保证SELECT返回的列数和顺序与指定列名完全匹配，格式如下（不常用，但符合标准）：

WITH PopulationVancine(date, location, population, continent, new_vacinations, rolling_people_vaccinated) AS (
  SELECT 
    Death.date,
    Death.location, 
    Death.population, 
    Death.continent, 
    vacine.new_vaccinations, 
    SUM(vacine.new_vaccinations) OVER (
      PARTITION BY Death.location 
      ORDER BY Death.location, Death.date
    )
  FROM `my-data-project-96387.PortfolioProjectSamp.CovidDeath` as Death
  INNER JOIN `my-data-project-96387.PortfolioProjectSamp.CovidVacination` as vacine
    ON Death.location = vacine.location
    AND Death.date = vacine.date
  WHERE Death.continent IS NOT NULL
)
SELECT *
FROM PopulationVancine

不过一般推荐直接使用第一种方式，让列名由SELECT语句自动生成，更简洁不易出错。

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