使用OVER子句后重命名列时SQL语法报错求助

你的SQL报错主要有三个原因：

• 同层级SELECT中无法直接引用刚定义的列别名3_year_moving_average，窗口函数不识别该别名
• 计算差值的表达式缺少闭合括号
• 数字开头的列名在多数SQL方言中需要转义（比如用双引号、反引号或方括号包裹）

修正后的SQL（使用CTE方式） #

WITH moving_avg AS (
    SELECT 
        country,
        year,
        AVG(oil_consumption) OVER (
            PARTITION BY country 
            ORDER BY year 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS "3_year_moving_average"
    FROM owid_energy_data
)
SELECT 
    country,
    year,
    "3_year_moving_average",
    "3_year_moving_average" - LAG("3_year_moving_average", 1) OVER (PARTITION BY country ORDER BY year) AS difference_between_previous_current
FROM moving_avg;

另一种写法（子查询方式） #

SELECT 
    country,
    year,
    "3_year_moving_average",
    "3_year_moving_average" - LAG("3_year_moving_average", 1) OVER (PARTITION BY country ORDER BY year) AS difference_between_previous_current
FROM (
    SELECT 
        country,
        year,
        AVG(oil_consumption) OVER (
            PARTITION BY country 
            ORDER BY year 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS "3_year_moving_average"
    FROM owid_energy_data
) AS subquery;

注意事项 #

• 若使用MySQL，将双引号替换为反引号：3_year_moving_average
• 若使用SQL Server，替换为方括号：[3_year_moving_average]
• 确保ORDER BY year在LAG窗口中存在，保证行顺序稳定

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