pandasql中分组执行位置不同的两条SQL查询结果不一致问题咨询

问题描述 #

使用pandasql开展数据查询时，两条预期逻辑完全一致的SQL返回结果差异极大：

• 第一条查询输出符合预期的统计值
• 第二条查询输出完全偏离预期的异常数据
  逐行比对实现逻辑后确认，二者唯一差异为：第一条的分组、聚合求和操作在各子查询内部完成，第二条的分组、聚合求和操作在两个子查询关联后由外层查询执行，已通过随机测试数据复现该问题。

对应SQL代码 #

可输出正确结果的查询 #

SELECT a.'Name', a.Q1, b.Q2, (a.Q1 + b.Q2) AS Total
FROM
    (SELECT c.'Name', SUM(c.'Paid Amount') AS Q1
     FROM some_data AS c
     WHERE c.'Quarter' = 'Q1'
     GROUP BY c.'Name') AS a
JOIN
    (SELECT d.'Name', SUM(d.'Paid Amount') AS Q2
     FROM some_data AS d
     WHERE d.'Quarter' = 'Q2'
     GROUP BY d.'Name') AS b
ON a.'Name' = b.'Name'
ORDER BY Total DESC
LIMIT 5;

返回异常结果的查询 #

SELECT a.'Name' as Label, SUM(a.'Paid Amount') AS Q1, SUM(b.'Paid Amount') AS Q2, (SUM(a.'Paid Amount') + SUM(b.'Paid Amount')) as Total
FROM 
    (SELECT c.'Name', c.'Paid Amount'
     FROM some_data AS c
     WHERE c.'Quarter' = 'Q1') AS a
JOIN
    (SELECT c.'Name', c.'Paid Amount'
     FROM some_data AS c
     WHERE c.'Quarter' = 'Q2') AS b
ON a.'Name' = b.'Name'
GROUP BY Label
ORDER BY Total DESC
LIMIT 5;

问题根源 #

核心原因是多表等值关联产生的行膨胀效应，两条查询的计算逻辑从本质上就不等价：

1. 先聚合再关联的逻辑：
   先按Name分组，分别对Q1、Q2的Paid Amount做求和聚合，每个Name在Q1、Q2的聚合结果都仅为1行，后续关联时不会产生多余的行，求和结果准确。
2. 先关联再聚合的逻辑：
   未做聚合就直接将Q1、Q2的原始明细行按Name关联。若某个Name在Q1有n条记录、Q2有m条记录，关联后会生成n*m行中间结果，而非预期的明细行数之和。外层再执行求和时，Q1的每条金额会被重复计算m次，Q2的每条金额会被重复计算n次，最终结果会远大于真实值，出现异常。

举例说明 #

假设某Name的记录情况：

• Q1有2条明细：金额100、200，真实Q1求和值为300
• Q2有3条明细：金额50、150、250，真实Q2求和值为450
• 先聚合再关联的输出：Q1=300，Q2=450，Total=750
• 先关联再聚合的输出：Q1实际求和为(100+200)*3=900，Q2实际求和为(50+150+250)*2=900，Total=1800，与真实值完全偏差。

可行修正方案 #

若需要在外层执行聚合操作，可改用条件聚合的写法，完全避免关联膨胀问题：

SELECT 
    Name,
    SUM(CASE WHEN Quarter = 'Q1' THEN `Paid Amount` ELSE 0 END) AS Q1,
    SUM(CASE WHEN Quarter = 'Q2' THEN `Paid Amount` ELSE 0 END) AS Q2,
    (SUM(CASE WHEN Quarter = 'Q1' THEN `Paid Amount` ELSE 0 END) + 
     SUM(CASE WHEN Quarter = 'Q2' THEN `Paid Amount` ELSE 0 END)) AS Total
FROM some_data
WHERE Quarter IN ('Q1', 'Q2')
GROUP BY Name
ORDER BY Total DESC
LIMIT 5;

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