Pandas与SQL数据聚合操作深度对比：从基础到高级场景

最新推荐文章于 2025-10-20 17:02:57 发布

原创最新推荐文章于 2025-10-20 17:02:57 发布 · 772 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#pandas #sql #数据库 #python #pip

Python 专栏收录该内容

101 篇文章

订阅专栏

文章目录

Pandas与SQL数据聚合操作深度对比：从基础到高级场景

Pandas与SQL数据聚合操作深度对比：从基础到高级场景

数据聚合是从数据集中提取关键统计信息的核心操作，无论是SQL还是Pandas都提供了丰富的聚合功能。本文将从基础到高级场景，详细对比两者在聚合操作上的实现方式、语法差异和适用场景。

一、基础聚合操作（无分组）

对整个数据集进行聚合计算，不进行分组划分。

SQL实现

-- 计算员工总数、平均工资、最高工资
SELECT 
  COUNT(*) AS total_employees,
  AVG(salary) AS avg_salary,
  MAX(salary) AS max_salary,
  SUM(bonus) AS total_bonus
FROM employees;

Pandas实现

import pandas as pd
import numpy as np

# 假设df是包含员工数据的DataFrame
agg_results = df.agg({
    'salary': ['count', 'mean', 'max'],  # count统计非空值数量
    'bonus': 'sum'
}).rename(columns={
    'count': 'total_employees',
    'mean': 'avg_salary',
    'max': 'max_salary',
    'sum': 'total_bonus'
}).T  # 转置使结果更易读

# 简化写法（适用于单字段多函数）
simple_agg = df['salary'].agg(['count', 'mean', 'max'])

对比说明

SQL中聚合函数直接作用于整个表，结果是单行统计值
Pandas中agg()方法可接受字典（指定字段-函数映射）或列表（对所有字段应用相同函数）
SQL的COUNT(*)包含NULL值，而Pandas的count()默认排除NaN值（类似SQL的COUNT(字段名)）

二、分组聚合（GROUP BY）

按一个或多个维度分组后进行聚合，是最常用的聚合场景。

1. 单字段分组

SQL实现

-- 按部门分组，计算每个部门的员工数和平均工资
SELECT 
  department,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Pandas实现

# 按部门分组并聚合
dept_agg = df.groupby('department').agg(
    emp_count=('name', 'count'),  # 用name字段计数（排除NaN）
    avg_salary=('salary', 'mean'),
    total_salary=('salary', 'sum')
).reset_index()  # 将分组列从索引还原为普通列

2. 多字段分组

SQL实现

-- 按部门和性别分组，计算各组平均工资
SELECT 
  department,
  gender,
  AVG(salary) AS avg_salary,
  COUNT(*) AS emp_count
FROM employees
GROUP BY department, gender  -- 多字段分组
ORDER BY department, gender;

Pandas实现

# 多字段分组聚合
dept_gender_agg = df.groupby(['department', 'gender']).agg(
    avg_salary=('salary', 'mean'),
    emp_count=('id', 'count')
).reset_index()  # 重置索引，使所有分组列变为普通列

# 排序（与SQL的ORDER BY对应）
dept_gender_agg = dept_gender_agg.sort_values(
    by=['department', 'gender']
)

对比说明

SQL的GROUP BY子句需显式列出所有非聚合字段
Pandas的groupby()接受列表参数实现多字段分组，分组字段默认成为索引
两者都遵循"分组字段+聚合结果"的输出结构，但Pandas需显式调用reset_index()才能与SQL结果结构一致

三、聚合后过滤（HAVING子句）

对聚合结果进行二次过滤，区别于聚合前的WHERE过滤。

SQL实现

-- 筛选平均工资超过80000的部门
SELECT 
  department,
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000  -- 聚合后过滤
AND COUNT(*) > 5;  -- 可组合多个条件

Pandas实现

# 方法1：先聚合后过滤（推荐，与SQL逻辑一致）
dept_agg = df.groupby('department').agg(
    emp_count=('id', 'count'),
    avg_salary=('salary', 'mean')
).reset_index()

filtered_agg = dept_agg[
    (dept_agg['avg_salary'] > 80000) & 
    (dept_agg['emp_count'] > 5)
]

# 方法2：使用groupby的filter方法（直接返回原始数据中行级过滤结果）
filtered_groups = df.groupby('department').filter(
    lambda x: (x['salary'].mean() > 80000) & (len(x) > 5)
)

对比说明

SQL严格区分WHERE（聚合前过滤）和HAVING（聚合后过滤）
Pandas有两种思路：先聚合得到统计结果再过滤（对应SQL的HAVING），或用filter()直接保留满足条件的原始数据行
注意Pandas中多条件用&/|连接，且每个条件需用括号包裹（区别于SQL的AND/OR）

四、条件聚合（CASE WHEN + 聚合）

对满足特定条件的记录进行聚合，实现更灵活的统计需求。

SQL实现

-- 按部门统计：总人数、男性人数、女性平均工资
SELECT 
  department,
  COUNT(*) AS total,
  COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS male_count,
  AVG(CASE WHEN gender = 'Female' THEN salary END) AS female_avg_salary
FROM employees
GROUP BY department;

Pandas实现

# 方法1：使用agg结合lambda
cond_agg = df.groupby('department').agg(
    total=('id', 'count'),
    male_count=('gender', lambda x: (x == 'Male').sum()),
    female_avg_salary=('salary', lambda x: x[df.loc[x.index, 'gender'] == 'Female'].mean())
).reset_index()

# 方法2：先创建条件列再聚合（更易读）
df['is_male'] = df['gender'] == 'Male'
df['female_salary'] = df.apply(
    lambda row: row['salary'] if row['gender'] == 'Female' else np.nan, axis=1
)

cond_agg = df.groupby('department').agg(
    total=('id', 'count'),
    male_count=('is_male', 'sum'),
    female_avg_salary=('female_salary', 'mean')
).reset_index()

对比说明

SQL的CASE WHEN在聚合中非常灵活，可直接嵌入聚合函数内部
Pandas实现条件聚合有两种方式：lambda函数（简洁但复杂场景可读性差）或先创建辅助列（更直观，适合复杂条件）
两者对不满足条件的值处理一致：SQL中CASE不匹配时返回NULL（聚合时忽略），Pandas中用NaN（聚合时同样忽略）

五、多函数聚合（同一字段多统计量）

对同一字段同时应用多个聚合函数，获取多维度统计信息。

SQL实现

-- 对工资字段同时计算多个统计量
SELECT 
  department,
  COUNT(salary) AS salary_count,
  AVG(salary) AS avg_salary,
  MAX(salary) - MIN(salary) AS salary_range,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department;

Pandas实现

# 对salary字段应用多个聚合函数
multi_func_agg = df.groupby('department')['salary'].agg(
    salary_count='count',
    avg_salary='mean',
    salary_range=lambda x: x.max() - x.min(),
    median_salary='median'
).reset_index()

# 对多个字段应用不同函数（扩展场景）
multi_field_agg = df.groupby('department').agg(
    salary_stats=('salary', ['mean', 'median']),
    bonus_total=('bonus', 'sum'),
    emp_count=('id', 'nunique')  # 去重计数
).reset_index()

对比说明

SQL中需为每个聚合结果显式命名（AS子句），Pandas中通过字典键或关键字参数直接指定列名
Pandas对多函数聚合支持更灵活，可直接使用lambda定义自定义聚合逻辑
高级统计函数（如中位数）在SQL中需用特定函数（如PERCENTILE_CONT），而Pandas内置median()更简洁

六、窗口函数聚合（保留明细行的聚合）

不压缩原始行数，在每行显示聚合结果（如"每个员工及其所在部门的平均工资"）。

SQL实现

-- 计算每个员工的工资及所在部门的平均工资
SELECT 
  id,
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
  COUNT(*) OVER (PARTITION BY department) AS dept_total_employees
FROM employees;

Pandas实现

# 方法1：使用transform（最常用，保留原始行数）
df['dept_avg_salary'] = df.groupby('department')['salary'].transform('mean')
df['dept_total_employees'] = df.groupby('department')['id'].transform('count')

# 方法2：使用merge（先聚合再关联，等价但效率较低）
dept_agg = df.groupby('department').agg(
    dept_avg_salary=('salary', 'mean'),
    dept_total_employees=('id', 'count')
).reset_index()

df_with_window = pd.merge(df, dept_agg, on='department', how='left')

对比说明

SQL的窗口函数（OVER (PARTITION BY ...)）是实现此功能的标准方式
Pandas的transform()方法与窗口函数功能完全对应，且性能优于merge方式
窗口函数/transform的核心价值：在保留原始数据明细的同时附加聚合统计，便于后续分析（如计算个人工资与部门平均的差异）

七、核心差异总结

维度	SQL	Pandas
语法风格	声明式（描述"要什么"）	命令式（描述"怎么做"）
性能优化	依赖数据库优化器和索引	依赖内存效率和算法优化
数据规模	适合超大数据集（无需加载到内存）	适合内存可容纳的数据集（GB级以内）
自定义聚合	需创建自定义函数（如PostgreSQL的CREATE FUNCTION）	支持lambda和自定义函数直接传入
代码可读性	简单聚合更简洁	复杂链式聚合更清晰
空值处理	聚合函数自动忽略NULL	聚合函数默认忽略NaN