使用sum()子句计算邮件发送总量结果错误，请求排查问题

问题描述 #

需要统计已发送电子邮件（类型1）和纸质邮件（类型2）的总数，当前执行结果中Total仅显示700，预期合计值应为3200（2500+700），具体执行结果如下：

Result1     Result2     Total
2500        700             700

原代码 #

with 
Customers1 as(          

SELECT   
count(1) as Result1

from table.documents d1, table.contracts c1
where d1.customerid = c1.customerid
and d1.documentid = c1.documentid 
and d1.type = 1  /* Sent by e-mail */
)  
,

Customers2 as(            
SELECT   
count(1) as Result2     
  
from table.documents d2, table.contracts c2
where d2.customerid = c2.customerid
and d2.documentid = c2.documentid 
and d2.type = 2  /* Sent by mail */
)
,

Summary as(
select
sum(Result1),
sum(Result2) as Total

from 
Customers1,
Customers2
)          
         
select 
Result1,
Result2,
Total

from 
Customers1,
Customers2,
Summary

问题分析 #

1. 合计逻辑错误：Summary中将Total定义为sum(Result2)，仅统计了纸质邮件的数量，完全遗漏了电子邮件的计数Result1，这是Total值错误的核心原因。
2. 冗余关联：最后查询时同时关联Customers1、Customers2、Summary属于逻辑冗余，因为Summary可以直接整合所有需要的统计值。
3. 隐式关联可读性差：使用逗号进行表关联属于旧写法，建议替换为显式JOIN语法，提升代码可维护性。

修正后的代码 #

方案一（保留CTE结构） #

with 
Customers1 as(          
SELECT count(1) as Result1
from table.documents d1
join table.contracts c1 
  on d1.customerid = c1.customerid
  and d1.documentid = c1.documentid 
where d1.type = 1  /* 电子邮件 */
)  
,
Customers2 as(            
SELECT count(1) as Result2     
from table.documents d2
join table.contracts c2 
  on d2.customerid = c2.customerid
  and d2.documentid = c2.documentid 
where d2.type = 2  /* 纸质邮件 */
),
Summary as(
select
  c1.Result1,
  c2.Result2,
  c1.Result1 + c2.Result2 as Total
from Customers1 c1, Customers2 c2
)          
select Result1, Result2, Total from Summary

方案二（简化写法） #

select
  -- 统计电子邮件数量
  (select count(1) 
   from table.documents d1
   join table.contracts c1 
     on d1.customerid = c1.customerid
     and d1.documentid = c1.documentid 
   where d1.type = 1) as Result1,
  -- 统计纸质邮件数量
  (select count(1) 
   from table.documents d2
   join table.contracts c2 
     on d2.customerid = c2.customerid
     and d2.documentid = c2.documentid 
   where d2.type = 2) as Result2,
  -- 计算总数
  (select count(1) 
   from table.documents d1
   join table.contracts c1 
     on d1.customerid = c1.customerid
     and d1.documentid = c1.documentid 
   where d1.type = 1) +
  (select count(1) 
   from table.documents d2
   join table.contracts c2 
     on d2.customerid = c2.customerid
     and d2.documentid = c2.documentid 
   where d2.type = 2) as Total

修正说明 #

• 将Total的计算逻辑改为Result1 + Result2，确保合计了两种邮件的数量。
• 使用显式JOIN替代隐式逗号关联，代码逻辑更清晰。
• 移除冗余的表关联，直接从Summary获取所有统计结果，避免不必要的笛卡尔积。

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