如何将SQL分组查询结果的Avg_MPG与CO2Avg_withoutEV列存入原表？

我已经编写了如下SQL查询，按年份分组统计非电动车型的平均油耗（Avg_MPG）和平均尾气CO₂排放（CO2Avg_withoutEV）：

select year, 
 avg(combined_mpg_ft1) as Avg_MPG, 
 avg(tailpipe_co2_in_grams_mile_ft1) as CO2Avg_withoutEV
from fuel$
where fuel_type_1 <> 'Electricity'
group by year
order by year desc

查询结果示例：

year    Avg_MPG             CO2Avg_withoutEV
2017    23.3069727891156    403.622448979592
2016    23.2543068088597    401.520098441345
2015    22.9001597444089    410.376198083067
...
1984    19.8818737270876    489.237133709112

现在需要将这两个统计列作为新列存入原fuel$表，用于后续计算，该如何操作？

操作步骤 #

1. 为原表添加新列

首先在fuel$表中创建两个新列，根据数据精度需求调整小数位数：

-- 适配MySQL、SQL Server、PostgreSQL等主流数据库
ALTER TABLE fuel$
ADD COLUMN Avg_MPG DECIMAL(15,13),
ADD COLUMN CO2Avg_withoutEV DECIMAL(15,13);

2. 批量更新表填充统计值

根据你使用的数据库类型，选择对应的更新语句：

MySQL 版本

通过JOIN关联分组统计结果与原表，批量更新：

UPDATE fuel$ f
JOIN (
    SELECT year, 
           avg(combined_mpg_ft1) as Avg_MPG, 
           avg(tailpipe_co2_in_grams_mile_ft1) as CO2Avg_withoutEV
    FROM fuel$
    WHERE fuel_type_1 <> 'Electricity'
    GROUP BY year
) stats ON f.year = stats.year
SET f.Avg_MPG = stats.Avg_MPG,
    f.CO2Avg_withoutEV = stats.CO2Avg_withoutEV;

SQL Server 版本

使用UPDATE ... FROM语法关联子查询结果：

UPDATE f
SET f.Avg_MPG = stats.Avg_MPG,
    f.CO2Avg_withoutEV = stats.CO2Avg_withoutEV
FROM fuel$ f
INNER JOIN (
    SELECT year, 
           avg(combined_mpg_ft1) as Avg_MPG, 
           avg(tailpipe_co2_in_grams_mile_ft1) as CO2Avg_withoutEV
    FROM fuel$
    WHERE fuel_type_1 <> 'Electricity'
    GROUP BY year
) stats ON f.year = stats.year;

PostgreSQL 版本

使用UPDATE ... FROM语法完成更新：

UPDATE fuel$ f
SET Avg_MPG = stats.Avg_MPG,
    CO2Avg_withoutEV = stats.CO2Avg_withoutEV
FROM (
    SELECT year, 
           avg(combined_mpg_ft1) as Avg_MPG, 
           avg(tailpipe_co2_in_grams_mile_ft1) as CO2Avg_withoutEV
    FROM fuel$
    WHERE fuel_type_1 <> 'Electricity'
    GROUP BY year
) stats
WHERE f.year = stats.year;

可选：仅更新非电动车型记录

如果希望仅给非电动车型填充统计值，电动车型的新列保持NULL，可在更新语句中添加过滤条件：

-- 以MySQL为例，其他数据库调整WHERE位置即可
UPDATE fuel$ f
JOIN (
    SELECT year, 
           avg(combined_mpg_ft1) as Avg_MPG, 
           avg(tailpipe_co2_in_grams_mile_ft1) as CO2Avg_withoutEV
    FROM fuel$
    WHERE fuel_type_1 <> 'Electricity'
    GROUP BY year
) stats ON f.year = stats.year
SET f.Avg_MPG = stats.Avg_MPG,
    f.CO2Avg_withoutEV = stats.CO2Avg_withoutEV
WHERE f.fuel_type_1 <> 'Electricity';

替代方案：无需修改原表，动态计算

如果不需要永久存储统计值，仅在后续计算中使用，可通过窗口函数直接查询，避免修改表结构：

SELECT *,
       -- 按年份分组计算非电动车型的平均MPG
       AVG(CASE WHEN fuel_type_1 <> 'Electricity' THEN combined_mpg_ft1 END) OVER (PARTITION BY year) AS Avg_MPG,
       -- 按年份分组计算非电动车型的平均CO₂排放
       AVG(CASE WHEN fuel_type_1 <> 'Electricity' THEN tailpipe_co2_in_grams_mile_ft1 END) OVER (PARTITION BY year) AS CO2Avg_withoutEV
FROM fuel$;

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