left join NULL踩坑

1、背景

因为最近响应监管，做数据全面匿名化的需求。会在写入数据库时，把姓名身份证做MD5处理后，只保留32位的前30位，保证即使拿到处理后的数据，也无法复原，恢复到源数据。

相应的，大数据BDP HIVE这边的数据处理也要做相应的改造，之前处理时会判断身份证位数，如果非15位和18位，则认为数据不正确，置为NULL。姓名身份证匿名化后要去除这个判断逻辑。

直接去除后上线，产品反馈出的报表数据有问题，数据汇总的表，总数=无源+有源，其中有源数据不对。

形如这种：

2、问题分析

去查了下对应代码：

select
,'当日' as time_type
,sum(case when last_created_date='20240104' then day_pv else 0 end) valid_pv
,sum(case when src_last_created_date='20240104' then src_day_pv else 0 end) src_valid_pv
from(
select
id_no
,day_pv
,year_pv
,total_pv
,last_created_date
,t4.src_id_no
,t4.src_day_pv
,t4.src_year_pv
,t4.src_total_pv
,t4.src_last_created_date
from (
select 
id_no
,sum(day_pv) as day_pv
,sum(year_pv) as year_pv
,sum(total_pv) as total_pv
,max(last_created_date) as last_created_date
from report_idap_face_compare_idno_mid
where ds='20240104'
and last_created_date='20240104' 
group by 
id_no
)t1

left join 
(
select 
id_no as src_id_no
,sum(day_pv) as src_day_pv
,sum(year_pv) as src_year_pv
,sum(total_pv) as src_total_pv
,max(last_created_date) as src_last_created_date
from report_idap_compare_record_idno_mid
where ds='20240104'
and last_created_date='20240104' 
group by 
id_no
)t4
on t1.id_no=t4.src_id_no
)s

其中，t1对应的是总有效次数，t4对应的有源有效次数。分别查了t1总有效次数是对的，t4的有源有效次数也是对的，但left join一关联起来有源有效次数就对不上了。

怀疑本来能关联的数据，由于匿名化修改后导致关联不上了。回去查看修改的代码段：
原代码：

select
case when length(id_no) in (15,18) then id_no else 'NULL' end as id_no

修改后：

select
id_no

仔细看代码发现非15，18位的身份证好像不是置为NULL，而是置为字符串的‘NULL’，于是顺手搜了下，t1表里果然有有许多身份证是NULL的。又试了身份证NULL的情况下，进行t1 left join t4，果然没关联上。

3、问题解决

破案了，之前应该是为了NULL这些身份证都能关联上，把不正常的身份证也都置为字符串的‘NULL’，这次修改的时候没注意到这点，直接改成了保持NULL，这就导致了left join后，对应列的缺失。

把这次修改身份证的地方改为：

select
case when id_no is not null then id_no else 'NULL' end as id_no

问题解决。

4、总结

这种原本的写法感觉不是很好，会导致误解，之后接手的人不了解这里，很容易就改出问题了。
其实把字符串‘NULL’，换成别的更容易理解的字符串，也是完全不影响结果的，而且更好理解。之后写代码可以注意这里。