MySQL中OVER(PARTITION BY)子句语法错误排查求助

问题场景 #

运行查询获取不同地区疫苗接种人员滚动计数时触发语法错误，以下是详细信息：

原始查询语句 #

SELECT 
    dea.continent,
    dea.location,
    dea.date,
    dea.population,
    vac.new_vaccinations,
    SUM(CONVERT(INT, vac.new_vaccinations)), OVER (PARTITION BY dea.location order by dea.location, dea.date)as RollingCountPeopleVaccinated
FROM  
    CovidDeaths dea
JOIN CovidVaccinations vac
    ON dea.location= vac.location and dea.date= vac.date
WHERE dea.continent is not null
ORDER BY 1,2,3

错误提示 #

Execution finished with errors.
Result: near "BY": syntax error
At line 161:

错误原因 #

核心问题出在窗口函数的语法格式上：SUM(CONVERT(INT, vac.new_vaccinations)), OVER (...) 中多了一个多余的逗号。窗口函数OVER()必须直接紧跟在聚合函数SUM()之后，不能用逗号分隔，这个错误导致数据库无法正确解析PARTITION BY的语法结构，进而抛出"near 'BY'"的语法错误。

另外错误提示中显示的查询存在笔误（dear.continent应为dea.continent），但原始查询里该部分是正确的，大概率是复制错误。

修正后的查询语句 #

SELECT 
    dea.continent,
    dea.location,
    dea.date,
    dea.population,
    vac.new_vaccinations,
    SUM(CONVERT(INT, vac.new_vaccinations)) OVER (PARTITION BY dea.location ORDER BY dea.date) AS RollingCountPeopleVaccinated
FROM  
    CovidDeaths dea
JOIN CovidVaccinations vac
    ON dea.location = vac.location 
    AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
ORDER BY 1, 2, 3

修正说明 #

• 移除SUM()与OVER()之间的多余逗号
• 简化ORDER BY子句：因已按dea.location分区，排序仅需dea.date即可，无需重复指定分区字段
• 调整代码格式，提升可读性

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