Pandas与MySQL美元金额求和结果不一致问题排查

需求背景 #

筛选question为“Dollar Amount”、entry_date在2024-07-01至2024-09-30间的有效数据，按subdomain分组求和金额，但Pandas复现结果与MySQL查询结果差异显著。以下是验证一致的SQL查询、Pandas实现代码及结果对比，以及核心排查方向。

1. 验证一致的MySQL查询语句 #

SQL查询1（子查询取每个form_submission_id的最新记录） #

SELECT subdomain, SUM(CAST(answer AS DECIMAL(10,2))) AS total_amount
FROM (
    SELECT form_submission_id, subdomain, question, answer, entry_date,
           ROW_NUMBER() OVER (PARTITION BY form_submission_id ORDER BY entry_date DESC) AS rn
    FROM your_table
    WHERE question = 'Dollar Amount'
      AND entry_date BETWEEN '2024-07-01' AND '2024-09-30'
) t
WHERE rn = 1
GROUP BY subdomain
ORDER BY total_amount DESC;

SQL查询2（与上述结果一致的关联写法） #

SELECT t1.subdomain, SUM(CAST(t1.answer AS DECIMAL(10,2))) AS total_amount
FROM your_table t1
JOIN (
    SELECT form_submission_id, MAX(entry_date) AS latest_date
    FROM your_table
    WHERE question = 'Dollar Amount'
      AND entry_date BETWEEN '2024-07-01' AND '2024-09-30'
    GROUP BY form_submission_id
) t2 ON t1.form_submission_id = t2.form_submission_id AND t1.entry_date = t2.latest_date
WHERE t1.question = 'Dollar Amount'
GROUP BY t1.subdomain
ORDER BY total_amount DESC;

2. Pandas实现代码 #

import pandas as pd

# 假设数据已加载至DataFrame df
filtered_df = df[
    (df['question'] == 'Dollar Amount') &
    (df['entry_date'] >= '2024-07-01') &
    (df['entry_date'] <= '2024-09-30')
]

# 标记每个form_submission_id的最新记录
filtered_df['rn'] = filtered_df.groupby('form_submission_id')['entry_date'].rank(ascending=False, method='first')
latest_records = filtered_df[filtered_df['rn'] == 1]

# 分组求和金额
result = latest_records.groupby('subdomain')['answer'].agg(
    lambda x: pd.to_numeric(x, errors='coerce').sum()
).reset_index()
result.columns = ['subdomain', 'total_amount']
result = result.sort_values('total_amount', ascending=False)

# 输出前5行
print(result.head())

3. 结果对比（前5行） #

MySQL查询结果 #

Pandas输出结果 #

4. 核心排查方向 #

• 数据类型转换差异：
  MySQL用CAST(answer AS DECIMAL(10,2))处理金额，Pandas用pd.to_numeric(x, errors='coerce')——后者会将无法转换的值转为NaN并忽略，前者可能报错或截断。统计两边非有效数值的数量，确认是否因数据转换规则不同导致求和差异。
• 最新记录选取逻辑：
  MySQL的ROW_NUMBER()按entry_date降序取组内第一条；Pandas的rank(method='first')虽逻辑类似，但需确认：当同一form_submission_id存在多条entry_date相同的记录时，Pandas加载数据的顺序是否与MySQL返回顺序一致，是否导致选取的记录不同。
• 时间范围边界处理：
  MySQL的BETWEEN '2024-09-30'包含当天23:59:59的记录，而Pandas的<= '2024-09-30'仅包含当天00:00:00之前的记录。需将Pandas的时间筛选改为df['entry_date'] <= '2024-09-30 23:59:59'，或用pd.Timestamp('2024-09-30') + pd.Timedelta(days=1, seconds=-1)。
• 过滤条件匹配度：
  检查大小写敏感性：MySQL的字符串匹配可能因排序规则不区分大小写，而Pandas严格区分，确认是否存在'dollar amount'这类记录被Pandas过滤但MySQL纳入的情况；同时排查NULL值处理，确保两边过滤逻辑完全一致。
• 数据源一致性：
  先统计两边最新记录的总数：MySQL执行SELECT COUNT(*) FROM (...) t WHERE rn=1，Pandas执行len(latest_records)，若数量不一致，说明Pandas加载的数据源与MySQL查询的数据源存在差异（如重复导入、数据更新）。

内容的提问来源于stack exchange，提问作者Thomas O'Donnell