如何将含CASE逻辑、count聚合的SQL分组语句转换为Python语法

待转换SQL语句 #

select service_date, case when trip_line = '6' then '6L' when trip_line = '7' then '7L' else trip_line end as trip_line,
dir, ats_sta_id, hhpsbod, count(*) as act_thruput
from act_thruput_raw
group by service_date, case when trip_line = '6' then '6L' when trip_line = '7' then '7L' else trip_line end, dir, ats_sta_id, hhpsbod

当前编写的Python代码 #

act_thruput_count = act_thruput_count[['SERVICE_DATE', 'TRIPLINE', 'DIRECTION', 'RTIF_ID', 'hhpsbod']]
.groupby(by = ['SERVICE_DATE', 'TRIPLINE', 'DIRECTION', 'RTIF_ID', 'hhpsbod']).count()

原SQL逻辑拆解 #

• 字段转换：对trip_line字段做条件映射，值为'6'时返回'6L'，值为'7'时返回'7L'，其余值保留原值
• 分组聚合：按service_date、转换后的trip_line、dir、ats_sta_id、hhpsbod五个维度分组，统计每组总记录数，统计结果列命名为act_thruput

现有代码的问题 #

• 缺失了TRIPLINE字段的条件转换步骤，和SQL逻辑不匹配
• 直接调用无参数的count()会对所有选中列分别统计非空值，且默认分组字段会转为行索引，不会作为独立列输出，也无法直接自定义统计列的名称

可直接运行的正确代码 #

# 第一步：实现SQL中的CASE WHEN字段转换逻辑
act_thruput_count["trip_line"] = act_thruput_count["TRIPLINE"].replace({"6": "6L", "7": "7L"})

# 第二步：分组统计，等价于原SQL的group by + count(*)
act_thruput_result = (
    act_thruput_count
    # as_index=False保证分组字段作为普通列输出，不转为行索引
    .groupby(by=["SERVICE_DATE", "trip_line", "DIRECTION", "RTIF_ID", "hhpsbod"], as_index=False)
    # size()统计每组总行数，和SQL count(*)逻辑完全一致，不会跳过空值
    .size()
    # 将统计结果列重命名为SQL中指定的别名
    .rename(columns={"size": "act_thruput"})
)

# 如需和SQL输出字段名完全对齐，可最后统一重命名列
act_thruput_result.columns = ["service_date", "trip_line", "dir", "ats_sta_id", "hhpsbod", "act_thruput"]

关键注意点 #

• 字段值映射用pandas的replace()传入字典即可实现多值替换，比逐行写判断效率高很多
• 统计分组总行数不要直接用无参数的count()，该方法会对每列单独统计非空值，和count(*)逻辑有差异；统计总行数优先用size()
• 分组时传入as_index=False可以省掉后续调用reset_index()的步骤，直接输出普通二维表结构

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