使用GROUP BY子句后SQL统计结果不一致的原因咨询

从源系统得到的ID去重统计数为2318，编写两条SQL查询后结果出现差异：

1. 全局去重统计查询 #

SELECT 
COUNT(DISTINCT ID)
FROM my_table fact
JOIN d_time time ON ( time.time_5_min_utc = (fact.host_tlt_utc -(fact.host_tlt_utc % (300*1000))))
WHERE fact.host_tlt_utc >= ${fromTimestamp}
  AND fact.host_tlt_utc < ${fromTimestamp}  + 86400000
  AND time.time_5_min_utc >= ${fromTimestamp} 
  AND time.time_5_min_utc < ${fromTimestamp}  + 86400000;

查询结果：

2. 按status分组统计查询 #

SELECT 
status,
COUNT(DISTINCT ID)
FROM my_table fact
JOIN d_time time ON ( time.time_5_min_utc = (fact.host_tlt_utc -(fact.host_tlt_utc % (300*1000))))
WHERE fact.host_tlt_utc >= ${fromTimestamp}
  AND fact.host_tlt_utc < ${fromTimestamp}  + 86400000
  AND time.time_5_min_utc >= ${fromTimestamp} 
  AND time.time_5_min_utc < ${fromTimestamp}  + 86400000
GROUP BY status;

查询结果：

疑问：将分组结果的COUNT(DISTINCT ID)求和后（1383+980+20=2383）远大于全局统计的2318，为何会出现这种差异？

核心问题在于同一个ID对应了多个不同的status值：

• 全局统计的COUNT(DISTINCT ID)是对所有记录中的ID去重后计数，每个ID无论对应多少条记录或多少个status，只会被统计1次。
• 分组统计时，COUNT(DISTINCT ID)是对每个status组内的ID单独去重计数，如果某个ID同时出现在多个status组中，就会在每个组都被统计1次，求和后自然会大于全局统计值。

举个简单例子：假设ID=1在表中存在两条记录，分别对应Open和In_Progress状态。全局统计中ID=1只算1次，但分组统计时，Open组和In_Progress组各算1次，求和时就会多算1次。

另外也可以检查d_time关联是否导致数据膨胀：如果同一个fact记录关联到多条d_time记录，会让同一ID的记录数增加，但因为用了COUNT(DISTINCT ID)，单组内的重复关联不会影响该组计数，不过跨status的重复ID才是导致求和值偏大的核心原因。

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