Pandas与PostgreSQL计算男女用户平均订单额差值的错误排查

任务背景 #

存在两张数据表：

• rides表（order_id, user_id, order_dt, order_cost）：存储Uber的全部订单数据；
• users表（user_id, gender）：存储用户信息，标注用户性别（M/F）。

需求 #

计算指定时间段内男性（M）与女性（F）用户的平均订单额差值，可使用SQL或Python实现，并解释结果可能存在差异的原因。

问题描述 #

我分别用Python（Pandas）和PostgreSQL实现了需求，但被反馈存在错误，却未说明具体错误点（可能是逻辑问题），请求帮忙排查错误。

我的实现代码 #

Python代码 #

import pandas as pd

taxi = pd.read_csv('taxi.csv')
users = pd.read_csv('users.csv')

df = pd.merge(taxi, users, on='user_id')

start_date = pd.to_datetime('2022-01-01')
end_date = pd.to_datetime('2022-12-31')
df = df[(df['order_dt'] >= start_date) & (df['order_dt'] <= end_date)]

result = df.groupby('gender')['order_cost'].mean()

diff = result.loc['М'] - result.loc['F']

print(diff)

SQL代码 #

SELECT AVG(CASE WHEN gender = 'М' THEN order_cost ELSE END) AS avg_cost_male,
       AVG(CASE WHEN gender = 'F' THEN order_cost ELSE END) AS avg_cost_female,
       AVG(CASE WHEN gender = 'М' THEN order_cost ELSE END) - AVG(CASE WHEN gender = 'F' 
THEN order_cost ELSE 0 END) AS diff
FROM taxi
JOIN users ON taxi.user_id = users.user_id
WHERE order_dt BETWEEN '2022-01-01' AND '2022-12-31';

错误排查与修正 #

Python代码问题 #

1. 性别字符错误：代码中误用了西里尔字母'М'，而非需求指定的英文大写字母'M'，会导致result.loc['М']找不到对应数据，触发KeyError。
2. 潜在细节：需确保order_dt被正确转换为datetime类型，否则日期筛选逻辑可能失效。

修正后的Python代码：

import pandas as pd

taxi = pd.read_csv('taxi.csv')
users = pd.read_csv('users.csv')

# 内连接保留有订单且有用户信息的数据
df = pd.merge(taxi, users, on='user_id', how='inner')

# 统一转换日期格式并筛选时间段
start_date = pd.to_datetime('2022-01-01')
end_date = pd.to_datetime('2022-12-31')
df['order_dt'] = pd.to_datetime(df['order_dt'])
df = df[(df['order_dt'] >= start_date) & (df['order_dt'] <= end_date)]

# 按性别计算平均订单额
result = df.groupby('gender')['order_cost'].mean()

# 使用正确的英文M计算差值，同时兼容某性别无数据的情况
diff = result.get('M', 0) - result.get('F', 0)

print(diff)

SQL代码问题 #

1. 性别字符错误：同样误用了西里尔字母'М'，需改为英文'M'。
2. CASE语法错误：ELSE END不符合SQL规范，应明确ELSE NULL（AVG函数会自动忽略NULL值，符合平均订单额的计算逻辑）。
3. 差值逻辑错误：原代码中女性平均订单额的CASE用了ELSE 0，会将非女性订单成本按0计算，导致女性平均订单额被严重拉低，完全偏离需求。

修正后的SQL代码：

SELECT 
    AVG(CASE WHEN gender = 'M' THEN order_cost ELSE NULL END) AS avg_cost_male,
    AVG(CASE WHEN gender = 'F' THEN order_cost ELSE NULL END) AS avg_cost_female,
    AVG(CASE WHEN gender = 'M' THEN order_cost ELSE NULL END) - AVG(CASE WHEN gender = 'F' THEN order_cost ELSE NULL END) AS diff
FROM taxi
JOIN users ON taxi.user_id = users.user_id
WHERE order_dt BETWEEN '2022-01-01' AND '2022-12-31';

结果差异的可能原因 #

即使修正后，Python和SQL的结果仍可能存在差异，原因包括：

• 精度处理差异：Pandas与PostgreSQL对浮点型的精度计算逻辑不同，当order_cost存在大量小数时，累计误差会被放大。
• 空值处理细节：若原始数据中order_cost的空值存储形式不一致（比如Pandas里的空字符串被解析为NaN，SQL里为NULL），可能导致计算范围略有不同。
• 数据类型匹配：若两张表中user_id的数据类型不一致（如一张是字符串、一张是整数），Pandas会自动转换匹配，而SQL会直接匹配失败，导致最终数据集不同。
• 时间精度对齐：若order_dt包含时分秒，需确保两边的时间段筛选完全覆盖边界数据，避免因时间精度差异导致的数据遗漏。

内容的提问来源于stack exchange，提问作者Григорий