Spark SQL 计数异常求助：关联后去重计数反而增加

你的预期和实际结果不符，核心是对关联操作影响visitor_id去重计数的逻辑理解有偏差——当用ip而非visitor_id做关联条件时，关联结果里的visitor_id去重数不一定会小于等于原表的数，不过更大概率是你的查询里字段引用搞混了。

下面分两种情况给你拆解：

最可能的原因：字段引用混淆 #

看你的第三条SQL：

select count(distinct visitor_id) from (
  select a.visitor_id, a.ip, b.visitor_id as visitor_id_b 
  from df_and_lkp_join_cache a 
  inner join df_not_matching_lkp b on a.ip = b.ip 
) --628259

子查询里你把b.visitor_id命名成了visitor_id_b，照理说外层的visitor_id应该是a.visitor_id。但如果你实际写的时候不小心漏了as visitor_id_b（比如写成select a.visitor_id, a.ip, b.visitor_id），Spark SQL会默认取最后一个同名字段，也就是b.visitor_id。

如果df_not_matching_lkp表本身去重后的visitor_id数量就是628259，那这个结果就完全说得通——你其实统计的是b表的visitor_id，不是a表的。

你可以先跑这个查询验证一下：

select count(distinct visitor_id) from df_not_matching_lkp

逻辑层面的误解：看似“新增”的情况 #

假设你字段引用完全没错，确实统计的是a.visitor_id的去重数，那结果增加的唯一可能是：原表df_and_lkp_join_cache里有visitor_id为NULL的记录，而且这些NULL对应的ip在b表能匹配到。

但你的前两条查询里，select count(distinct trim(visitor_id))结果也是178996，说明trim没改变去重结果（NULL trim后还是NULL）。而内关联会过滤掉a.ip为NULL的记录，所以NULL的visitor_id会被排除，结果应该变少才对，这种情况可能性极低。

下一步排查建议 #

1. 明确字段来源：修改第三条SQL，给字段加明确的别名，看看结果：

   select count(distinct a_visitor_id) from (
     select a.visitor_id as a_visitor_id, a.ip, b.visitor_id as b_visitor_id 
     from df_and_lkp_join_cache a 
     inner join df_not_matching_lkp b on a.ip = b.ip 
   )
   

   如果这个结果变回178996，那就是之前字段引用搞混了。

2. 检查数据分布：看看原表和关联表中ip与visitor_id的对应关系：

   -- 看a表每个visitor_id对应多少个不同的ip
   select visitor_id, count(distinct ip) as ip_count 
   from df_and_lkp_join_cache 
   group by visitor_id 
   having ip_count > 1
   
   -- 看b表每个ip对应多少个不同的visitor_id
   select ip, count(distinct visitor_id) as visitor_count 
   from df_not_matching_lkp 
   group by ip 
   having visitor_count > 1
   

   这能帮你理解关联后数据膨胀的程度，但如果统计的是a.visitor_id的去重数，膨胀不会影响最终的distinct计数。

总的来说，大概率是字段引用的问题，导致你误统计了关联表的visitor_id数量。

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