如何高效替代iterrows遍历DataFrame执行SQL查询并合并结果？

我有如下Pandas DataFrame：

import pandas as pd
import sqlalchemy
con = sqlalchemy.create_engine('....')

df=pd.DataFrame({'user_id':[1,2,3],'start_date':pd.Series(['2022-05-01 00:00:00','2022-05-10 00:00:00','2022-05-20 00:00:00'],dtype='datetime64[ns]'),
                 'end_date':pd.Series(['2022-06-01 00:00:00','2022-06-10 00:00:00','2022-06-20 00:00:00'],dtype='datetime64[ns]')})

'''
user_id    start_date            end_date
1          2022-05-01 00:00:00   2022-06-01 00:00:00
2          2022-05-10 00:00:00   2022-06-10 00:00:00
3          2022-05-20 00:00:00   2022-06-20 00:00:00
'''

我需要从数据库中获取每个用户在df指定日期范围内的销售数据，当前使用iterrows()循环查询的代码可以正常运行，但速度极慢：

df_stats=pd.DataFrame()
for k,j in df.iterrows():
    sql='''
  select '{}' as user_id,sum(item_price) as sales,count(return) as return from sales 
  where created_at between '{}' and '{}' and user_id={}'''.format(j['user_id'],j['start_date'],j['end_date'],j['user_id'])
    sql_to_df = pd.read_sql(sql, con)
    df_stats = df_stats.append(sql_to_df)

final=df.merge(df_stats,on='user_id')

'''
final:

user_id    start_date            end_date              sales   return
1          2022-05-01 00:00:00   2022-06-01 00:00:00   1500    5
2          2022-05-10 00:00:00   2022-06-10 00:00:00   2900    9
3          2022-05-20 00:00:00   2022-06-20 00:00:00   1450    1
'''

请问有没有更高效的实现方式？

方法1：利用数据库临时表做关联聚合（最优方案） #

把本地DataFrame的用户日期条件写入数据库临时表，直接在数据库层面完成关联和聚合，只需要一次查询，效率远高于循环。

代码示例（以PostgreSQL为例，临时表会话结束后自动销毁）：

# 将本地df写入数据库临时表
df.to_sql('user_date_ranges', con, index=False, if_exists='replace', method='multi')

# 编写关联查询SQL，直接计算统计结果
sql = '''
SELECT 
    u.user_id,
    SUM(s.item_price) AS sales,
    COUNT(s.return) AS return
FROM user_date_ranges u
JOIN sales s 
    ON s.user_id = u.user_id 
    AND s.created_at BETWEEN u.start_date AND u.end_date
GROUP BY u.user_id
'''

# 一次性读取统计数据
df_stats = pd.read_sql(sql, con)

# 合并原数据与统计结果
final = df.merge(df_stats, on='user_id', how='left')

优势：借助数据库优化器处理关联逻辑，避免多次网络请求，同时彻底规避SQL注入风险。

方法2：构造参数化批量查询（无临时表时可用） #

如果无法创建临时表，可以构造包含所有用户条件的参数化SQL，一次性查询所有数据：

# 提取用户参数列表
params = list(df[['user_id', 'start_date', 'end_date']].itertuples(index=False, name=None))

# 构造批量查询条件
conditions = " OR ".join([
    "(s.user_id = %s AND s.created_at BETWEEN %s AND %s)" 
    for _ in params
])

# 完整SQL语句
sql = f'''
SELECT 
    s.user_id,
    SUM(s.item_price) AS sales,
    COUNT(s.return) AS return
FROM sales s
WHERE {conditions}
GROUP BY s.user_id
'''

# 执行参数化查询
df_stats = pd.read_sql(sql, con, params=[p for param_tuple in params for p in param_tuple])

# 合并数据
final = df.merge(df_stats, on='user_id', how='left')

注意：如果用户数量过多（如上万条），会导致SQL语句过长，此时优先选择临时表方案。

方法3：用apply替代iterrows（次优选择） #

如果必须用Pandas行处理，用apply替代iterrows，配合参数化查询提升效率：

def get_user_stats(row):
    sql = '''
    SELECT 
        %(user_id)s AS user_id,
        SUM(item_price) AS sales,
        COUNT(return) AS return
    FROM sales
    WHERE created_at BETWEEN %(start_date)s AND %(end_date)s 
      AND user_id = %(user_id)s
    '''
    return pd.read_sql(sql, con, params=row.to_dict())

# 批量处理并合并结果
df_stats = pd.concat(df.apply(get_user_stats, axis=1).tolist(), ignore_index=True)

final = df.merge(df_stats, on='user_id')

优势：比iterrows效率略高，且参数化查询更安全，但仍不如一次性数据库查询高效。

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