Python pandas groupby与agg聚合操作转换为PostgreSQL SQL实现方案

算力阿峰

2026-9-30

等效PostgreSQL查询实现

你给出的pandas分组聚合逻辑可以直接用标准SQL的GROUP BY + 聚合函数实现，和pandas执行结果完全一致：

SELECT 
    "Region",
    "State",
    "CO_FIPS",
    "Tiermetric_Prelim",
    "Mod_unMod_Prelim",
    "Val_Combined_Prelim",
    "Det_Approx_Prelim",
    "Decay_Date_Prelim",
    SUM("Actual_Miles_Prelim") AS "Actual_Miles_Prelim"
FROM cnms_table
GROUP BY 
    "Region",
    "State",
    "CO_FIPS",
    "Tiermetric_Prelim",
    "Mod_unMod_Prelim",
    "Val_Combined_Prelim",
    "Det_Approx_Prelim",
    "Decay_Date_Prelim";

逻辑对应说明

pandas中groupby传入的分组字段列表，直接对应SQL的GROUP BY后跟随的字段列表
pandas中agg({'Actual_Miles_Prelim': 'sum'})的聚合逻辑，对应SQL的SUM("Actual_Miles_Prelim")，别名保持原列名和pandas输出结构完全对齐
pandas中的as_index=False参数不需要在SQL中做额外处理，SQL默认会把SELECT列表中的分组字段作为普通列返回

简化写法&持久化存储

如果需要直接把聚合结果保存为新表，可以用CREATE TABLE AS语法，同时GROUP BY可以用字段在SELECT中的位置指代，简化写法：

CREATE TABLE sum_df_prelim AS
SELECT 
    "Region",
    "State",
    "CO_FIPS",
    "Tiermetric_Prelim",
    "Mod_unMod_Prelim",
    "Val_Combined_Prelim",
    "Det_Approx_Prelim",
    "Decay_Date_Prelim",
    SUM("Actual_Miles_Prelim") AS "Actual_Miles_Prelim"
FROM cnms_table
GROUP BY 1,2,3,4,5,6,7,8;