Spark SQL全外连接统计3天用户活跃时缺失0 1 1、1 0 1行问题排查

问题原因 #

你缺失0 1 1、1 0 1两类活跃组合的核心问题出在第二次全外连接的关联条件错误：
你关联t2时用的条件是t0.uid = t2.uid and t1.uid = t2.uid，这意味着只有同时在t0和t1中存在的uid，才能匹配到t2的记录：

• 对于仅在t1、t2活跃的用户（对应0 1 1组合，比如测试数据的uid=4）：t0.uid为null，null = 任意值的判断结果永远为未知，关联条件不成立，这类用户的t2活跃标识无法被正常关联，不会被计入统计
• 对于仅在t0、t2活跃的用户（对应1 0 1组合，比如测试数据的uid=3）：t1.uid为null，同样关联条件不成立，t2的活跃标识无法关联，也不会被计入统计

修复方案 #

方案1：修正全外连接的关联条件 #

把第二次关联t2的条件改为用coalesce取前两个表的非空uid来匹配t2的uid，同时用nvl把空值转成0避免分组异常：

select 
  nvl(d0,0) as d0,
  nvl(d1,0) as d1,
  nvl(d2,0) as d2,
  count(*) as user_count 
from (
  select uid, 1 as d0 from my_table where day=5 and uid is not Null group by uid
) as t0 
full outer join (
  select uid, 1 as d1 from my_table where day=6 and uid is not Null group by uid
) as t1 on t0.uid = t1.uid
full outer join (
  select uid, 1 as d2 from my_table where day=7 and uid is not Null group by uid
) as t2 on coalesce(t0.uid, t1.uid) = t2.uid
group by nvl(d0,0),nvl(d1,0),nvl(d2,0) 
order by d0,d1,d2

方案2：改用条件聚合（更推荐，逻辑更简洁性能更好） #

不需要多次子查询关联，直接扫描一次表做条件聚合即可：

select 
  d0,d1,d2,
  count(*) as user_count
from (
  select 
    uid,
    max(case when day=5 then 1 else 0 end) as d0,
    max(case when day=6 then 1 else 0 end) as d1,
    max(case when day=7 then 1 else 0 end) as d2
  from my_table
  where day in (5,6,7) and uid is not null
  group by uid
) t
group by d0,d1,d2
order by d0,d1,d2

对测试表执行上述两种写法，都可以得到你期望的输出结果。

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