优化前
select count(b.user_initial_ccid)
from dwd.dwd_ccid_register_a a
left join audience_data_service.sys_user_all b
on ((b.user_id=a.union_id_md5 and b.user_type_id=15)
or (b.user_id=a.mobile_md5 and b.user_type_id=16)
or (b.user_id=a.email_md5 and b.user_type_id=11)
or (b.user_id=a.open_id_md5 and b.user_type_id=14)
or (b.user_id=a.alipay_id_md5 and b.user_type_id=18)
or (b.user_id=a.wechat_id_md5 and b.user_type_id=13)
or (b.user_id=a.ip_md5 and b.user_type_id=12)) and b.dt='current';
优化后
drop table if exists tmp.tmp_count1;
create table tmp.tmp_count1 as
select b.ccid,a.*
from dwd.dwd_ccid_register_a a
left join audience_data_service.sys_user_ccid_all b
on case when a.mobile_md5 is not null then a.mobile_md5
when a.union_id_md5 is not null then a.union_id_md5
when a.open_id_md5 is not null then a.open_id_md5
when a.email_md5 is not null then a.email_md5
when a.alipay_id_md5 is not null then a.alipay_id_md5 end = b.user_id
and case when a.mobile_md5 is not null then 16
when a.union_id_md5 is not null then 15
when a.open_id_md5 is not null then 14
when a.email_md5 is not null then 11
when a.alipay_id_md5 is not null then 18 end = b.user_type_id;
