Hive优化---or

优化前

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;