CTE计算2000年后电动车与燃油车YoY同比增长率报错求助

错误原因 #

你遇到的报错核心是CTE（公共表表达式）里不能直接使用ORDER BY子句，除非同时搭配TOP、OFFSET或FOR XML。第一个CTE cte_EV_Comb_Counts 中的ORDER BY year ASC违反了这个规则，而且这个排序完全没必要——后续计算同比增长率的LAG函数已经通过OVER(ORDER BY year)指定了排序逻辑。

修正后的代码 #

WITH cte_EV_Comb_Counts AS (
    SELECT 
        year,
        COUNT(CASE WHEN fuel_type_1 = 'Electricity' THEN 1 END) AS Electricity,
        COUNT(CASE WHEN fuel_type_1 <> 'Electricity' THEN 1 END) AS Combustion
    FROM 
        fuel$
    WHERE 
        year >= '2000'
    GROUP BY 
        year
    -- 移除此处的ORDER BY
),
cte_yearly_growth AS (
    SELECT 
        year,
        Electricity,
        Combustion,
        LAG(Electricity, 1) OVER (ORDER BY year) AS Previous_Electricity,
        LAG(Combustion, 1) OVER (ORDER BY year) AS Previous_Combustion,
        -- 用NULLIF避免除数为0的报错，ROUND保留2位小数优化结果
        ROUND((Electricity - LAG(Electricity, 1) OVER (ORDER BY year)) * 100.0 / NULLIF(LAG(Electricity, 1) OVER (ORDER BY year), 0), 2) AS yearly_growth_EV,
        ROUND((Combustion - LAG(Combustion, 1) OVER (ORDER BY year)) * 100.0 / NULLIF(LAG(Combustion, 1) OVER (ORDER BY year), 0), 2) AS yearly_growth_Comb
    FROM 
        cte_EV_Comb_Counts
)
SELECT 
    year,
    Electricity,
    Combustion,
    yearly_growth_EV,
    yearly_growth_Comb
FROM 
    cte_yearly_growth
ORDER BY year ASC; -- 最终结果的排序放在外层查询中

优化说明 #

1. 简化CASE表达式：WHERE子句已经过滤了year >= '2000'，CASE里无需重复判断，减少冗余
2. 增加容错处理：用NULLIF将除数为0的情况转为NULL，避免运行报错；ROUND函数让增长率结果更规整
3. 规范排序位置：如果需要有序的输出结果，将ORDER BY放在最外层查询即可

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