Oracle PL/SQL按日期GROUP BY结果异常：TRUNC后计数未达预期求和

问题描述 #

原查询按created_on（含时分秒的时间字段）分组，2021年3月19日三个不同时间点的发票计数分别为2、165、164。改用TRUNC(created_on)按天分组后，预期得到三者之和（331），但实际仅返回166，需排查原因。

原查询SQL #

SELECT  
(CASE 
WHEN std.attribute_1 like '%709%' OR std.attribute_1 like '%999%' THEN 'COMPA' -- COMPA类发票以709或999开头
WHEN h.manual_upload = 'Y' then 'MANUAL_UPLOAD'  
ELSE 'OTHER' END) AS BILLING_SOURCE, std.created_on, COUNT(DISTINCT std.invoice_number) AS COUNT_OF_INVOICES
FROM onebiller.t_std_in_detail_his std
INNER JOIN onebiller.t_std_in_header h
ON h.job_id =  std.job_id
WHERE std.invoice_number IS NOT NULL
GROUP BY (CASE 
WHEN std.attribute_1 like '%709%' OR std.attribute_1 like '%999%' THEN 'COMPA' -- COMPA类发票以709或999开头
WHEN h.manual_upload = 'Y' then 'MANUAL_UPLOAD'  
ELSE 'OTHER' END), std.created_on
ORDER BY std.created_on ASC

修改后查询SQL #

SELECT  
(CASE 
WHEN std.attribute_1 like '%709%' OR std.attribute_1 like '%999%' THEN 'COMPA' -- COMPA类发票以709或999开头
WHEN h.manual_upload = 'Y' then 'MANUAL_UPLOAD'  
ELSE 'OTHER' END) AS BILLING_SOURCE, TRUNC(std.created_on), COUNT(DISTINCT std.invoice_number) AS COUNT_OF_INVOICES
FROM onebiller.t_std_in_detail_his std
INNER JOIN onebiller.t_std_in_header h
ON h.job_id =  std.job_id
WHERE std.invoice_number IS NOT NULL
GROUP BY (CASE 
WHEN std.attribute_1 like '%709%' OR std.attribute_1 like '%999%' THEN 'COMPA' -- COMPA类发票以709或999开头
WHEN h.manual_upload = 'Y' then 'MANUAL_UPLOAD'  
ELSE 'OTHER' END), TRUNC(std.created_on)
ORDER BY TRUNC(std.created_on) ASC

原因解释 #

问题核心在于COUNT(DISTINCT std.invoice_number)的计算逻辑差异：

• 原查询按created_on（精确到时分秒）分组时，每个分组统计的是该时间点内唯一的发票号数量。即使同一个发票号出现在多个时间点，每个分组都会单独计数一次。
• 修改为按TRUNC(created_on)（按天）分组后，COUNT(DISTINCT)会统计整个日期内所有唯一的发票号数量——那些在多个时间点重复出现的发票号，会被合并成一次计数，而非累加各时间点的计数。

你的场景中，2+165+164=331是各时间点去重后的计数之和，但当天实际唯一的发票号仅166个，说明大部分发票号在多个时间点重复出现了。

验证方法 #

可执行以下查询，查看当天每个发票号的重复出现次数，确认重复情况：

SELECT std.invoice_number, COUNT(*) AS 出现次数
FROM onebiller.t_std_in_detail_his std
INNER JOIN onebiller.t_std_in_header h ON h.job_id = std.job_id
WHERE std.invoice_number IS NOT NULL
AND TRUNC(std.created_on) = DATE '2021-03-19'
GROUP BY std.invoice_number
HAVING COUNT(*) > 1
ORDER BY 出现次数 DESC

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