Hive优化---or

select count(b.user_initial_ccid)from dwd.dwd_ccid_register_a aleft 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 aleft 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_md5when a.alipay_id_md5 is not null then a.alipay_id_md5 end = b.user_idand 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 11when a.alipay_id_md5 is not null then 18 end = b.user_type_id;