Snowflake三种去重计数方法结果差异的原因与验证

测试场景与结果 #

使用以下三种SQL语句对同一张表src进行去重计数，得到不同结果：

select 'count_distinct_subquery' as strat,count(*) from (
  select distinct 
  plan_code,
  fis_we_dt,
  sku_no,
  pog_segment_name,
  shelf_no,
  position_id
  from src 
)
union all
select 'count_distinct' as strat,count(
  distinct 
  plan_code,
  fis_we_dt,
  sku_no,
  pog_segment_name,
  shelf_no,
  position_id
)
from src 
union all 
select 'group_by_subquery' as strat, count(*) from (
  select *
  from src 
  group by 
  plan_code,
  fis_we_dt,
  sku_no,
  pog_segment_name,
  shelf_no,
  position_id
)

测试结果：count_distinct_subquery和group_by_subquery结果均为144592，count_distinct结果为144576，二者存在16条差异。

一、三种方法不可互换的场景 #

1. 组合列包含NULL值 #

Snowflake对NULL的处理逻辑在COUNT(DISTINCT)与SELECT DISTINCT/GROUP BY中存在核心差异：

• SELECT DISTINCT和GROUP BY将NULL视为相等：若两条记录的非NULL列完全一致，仅某列均为NULL，会被判定为同一组，只计1次。
• COUNT(DISTINCT (col1, col2, ...))将NULL视为不相等：即使其他列完全一致，只要某列是NULL，会被判定为不同的组合，分别计数。这是导致结果差异最常见的原因。

2. 包含复杂数据类型 #

当组合列包含ARRAY、OBJECT等复杂数据类型时，COUNT(DISTINCT)的哈希计算逻辑可能与SELECT DISTINCT/GROUP BY不一致，导致去重结果偏差。

3. 查询优化器的特殊处理 #

虽然Snowflake默认COUNT(DISTINCT)为精确计数，但在超大数据集下，若查询优化器触发了特殊的并行处理或近似优化（罕见，但需排查），也可能导致结果差异。

二、判断哪种结果正确的方法 #

1. 排查NULL值影响 #

• 统计组合列中各列的NULL数量：执行SELECT SUM(CASE WHEN plan_code IS NULL THEN 1 ELSE 0 END) AS plan_code_nulls, ... FROM src，定位是否存在大量NULL的列。
• 验证重复组：执行SELECT plan_code, fis_we_dt, sku_no, pog_segment_name, shelf_no, position_id, COUNT(*) FROM src GROUP BY ALL HAVING COUNT(*) > 1，查看是否存在包含NULL的重复记录。若业务上认为NULL属于同一组，则SELECT DISTINCT/GROUP BY的结果正确；若NULL需视为不同值，则COUNT(DISTINCT)结果正确。

2. 手动抽样验证 #

从差异数据中随机抽取样本，对比两条记录的实际内容。例如，找到SELECT DISTINCT计数但COUNT(DISTINCT)未计数的记录，判断业务上是否应视为同一组。

3. 查看执行计划 #

在Snowflake中查看三种查询的执行计划，确认COUNT(DISTINCT)是否采用了特殊的去重逻辑（如是否启用近似计数），或是否存在数据类型转换导致的哈希差异。

内容的提问来源于stack exchange，提问作者Gamaliel Ronzón