今天发贴解决一个数据质量校验中关于sql关联后空值验证的问题。到底是粗颗粒度关联明细表还是明细表关联粗颗粒度表?
有这样两张表:
select * from company;
select * from department;
--全表查company_name的空值
select company_id from company where company_name is null;
--粗颗粒度关联明细表查company_name空值
select c.company_id from company c
left join department d
on c.company_id=d.company_id
where c.company_name is null;
--细颗粒度关联粗表查company_name空值
select c.company_id from department d
left join company c
on d.company_id=c.company_id
where c.company_name is null;
结果表明:当粗颗粒度数据量更广
且作为主表去left join明细表得到结论是:关联空值校验=全表空值校验。
明细表做主表去left join粗粒度表得到结论是:关联空值校验<全表空值校验
反之,当明细表数据量更广时:
粗颗粒表做主表left join明细表得到结论是:关联空值校验=全表校验。
明细表做主表left join粗颗粒都表得到结论是:关联空值校验>全表校验。
select * from company;
select * from department;
--全表查company_name的空值
select company_id from company where company_name is null;
--粗颗粒度关联明细表查company_name空值
select c.company_id from company c
left join department d
on c.company_id=d.company_id
where c.company_name is null;
--细颗粒度关联粗表查company_name空值
select c.company_id from department d
left join company c
on d.company_id=c.company_id
where c.company_name is null;
最终结论,我们为什么做关联后的空值校验,就是为了确认,粗颗粒度field是否能够在明细表找到相应记录(找不出来就mark为空)。那么关联后空值必须要>全表空值。譬如说,company table突然新增了一条记录,(5,‘Apple’)这个就到不了最小核算单元即明细表没有关于company_id=5的记录,所以应该把他也mark进关联后空值+1;
那么正确的写法应该是:
可以肯定的是粗颗粒度往明细表关联:
其次,要注意统计的对象不再是该字段本身,而应该是最小核算单元。
select count(company_id) from company where company_name is null (全表空值)+
select count(company_name) from company c
left join department d
on c.company_id=d.company_id
where d.department_id is null;