insert overwrite table temp_idcard
select idcardno,
0 as birthprovid,
max(birthprovname) as birthprovname,
0 as birthcityid,
max(birthcityname) as birthcityname,
0 as birthdistrictid,
max(birthdistrictname) as birthdistrictname,
max(birthyear) as birthyear,
‘’ as birthdecade,
max(sex) as sex,
unix_timestamp(current_timestamp()) as createtime
from (select idcardno,
birthprovname,
case when birthcityname = ‘市直辖行政区’
then birthprovname
when birthcityname = ‘省（自治区）直辖县级行政区’
then concat(birthprovname,‘直辖区划’)
else birthcityname end as birthcityname,
case when birthdistrictname = ‘’
then birthcityname
else birthdistrictname end as birthdistrictname,
birthyear,
sex
from dw_bankcard_auth_apply
where dateid = ${etlDate}
and length(idcardno) >= 1
union all
select idcardno,
birthprovname,
case when birthcityname = ‘市直辖行政区’
then birthprovname
when birthcityname = ‘省（自治区）直辖县级行政区’
then concat(birthprovname,‘直辖区划’)
else birthcityname end as birthcityname,
case when birthdistrictname = ‘’
then birthcityname
else birthdistrictname end as birthdistrictname,
birthyear,
sex
from dw_activity_antirush_apply
where dateid = ${etlDate}
and length(idcardno) >= 1
union all
select idcardno,
birthprovname,
case when birthcityname = ‘市直辖行政区’
then birthprovname
when birthcityname = ‘省（自治区）直辖县级行政区’
then concat(birthprovname,‘直辖区划’)
else birthcityname end as birthcityname,
case when birthdistrictname = ‘’
then birthcityname
else birthdistrictname end as birthdistrictname,
birthyear,
sex
from dw_psb_realname_apply
where dateid = ${etlDate}
and length(idcardno) >= 1
union all
select idcardno,
birthprovname,
case when birthcityname = ‘市直辖行政区’
then birthprovname
when birthcityname = ‘省（自治区）直辖县级行政区’
then concat(birthprovname,‘直辖区划’)
else birthcityname end as birthcityname,
case when birthdistrictname = ‘’
then birthcityname
else birthdistrictname end as birthdistrictname,
birthyear,
sex
from dw_telecom_realname_apply
where dateid = ${etlDate}
and length(idcardno) >= 1
union all
select idcardno,
birthprovname,
case when birthcityname = ‘市直辖行政区’
then birthprovname
when birthcityname = ‘省（自治区）直辖县级行政区’
then concat(birthprovname,‘直辖区划’)
else birthcityname end as birthcityname,
case when birthdistrictname = ‘’
then birthcityname
else birthdistrictname end as birthdistrictname,
birthyear,
sex
from dw_per_model_apply
where dateid = ${etlDate}
and length(idcardno) >= 1) dw
group by idcardno;
ANALYZE TABLE temp_idcard COMPUTE STATISTICS FOR COLUMNS;
insert overwrite table temp_idcard
select di.idcardno,
coalesce(dr.provid,0) as birthprovid,
coalesce(dr.provname,di.birthprovname) as birthprovname,
coalesce(dr.cityid,0) as birthcityid,
coalesce(dr.cityname,di.birthcityname) as birthcityname,
coalesce(dr.regionid,0) as birthdistrictid,
coalesce(dr.regionname,di.birthdistrictname) as birthdistrictname,
di.birthyear,
(case when length(cast(di.birthyear as string)) <> 4 then ‘’
else concat(substring(cast(di.birthyear as string),3,1),‘0后’) end) as birthdecade,
di.sex,
unix_timestamp(current_timestamp()) as updatetime
from temp_idcard di
left join (select tmp.provid,
tmp.provname,
tmp.cityid,
tmp.cityname,
tmp.regionid,
tmp.regionname
from (select provid,
provname,
cityid,
cityname,
regionid,
regionname,
row_number() over (partition by provname,cityname,regionname order by regionid) as rn
from dim_region
where isnew = 0) tmp
where tmp.rn = 1) dr
on (di.birthdistrictname = dr.regionname
and di.birthcityname = dr.cityname
and di.birthprovname = dr.provname);
insert overwrite table temp_idcard
select di.idcardno,
coalesce(dr.provid,0) as birthprovid,
coalesce(dr.provname,di.birthprovname) as birthprovname,
coalesce(dr.cityid,0) as birthcityid,
coalesce(dr.cityname,di.birthcityname) as birthcityname,
coalesce(dr.regionid,0) as birthdistrictid,
coalesce(dr.regionname,di.birthdistrictname) as birthdistrictname,
di.birthyear,
(case when length(cast(di.birthyear as string)) <> 4 then ‘’
else concat(substring(cast(di.birthyear as string),3,1),‘0后’) end) as birthdecade,
di.sex,
unix_timestamp(current_timestamp()) as updatetime
from temp_idcard di
left join dim_region dr
on (di.birthdistrictname = dr.regionname
and di.birthcityname = dr.cityname
and di.birthprovname = dr.provname
and dr.isnew = 1);
insert overwrite table dim_idcard
select coalesce(ti.idcardno,di.idcardno) as idcardno,
coalesce(ti.birthprovid,di.birthprovid) as birthprovid,
case when nvl(ti.birthprovname,’’) = ‘’
then nvl(di.birthprovname,’’)
else ti.birthprovname end as birthprovname,
coalesce(ti.birthcityid,di.birthcityid) as birthcityid,
case when nvl(ti.birthcityname,’’) = ‘’
then nvl(di.birthcityname,’’)
else ti.birthcityname end as birthcityname,
coalesce(ti.birthdistrictid,di.birthdistrictid) as birthdistrictid,
case when nvl(ti.birthdistrictname,’’) = ‘’
then nvl(di.birthdistrictname,’’)
else ti.birthdistrictname end as birthdistrictname,
case when nvl(ti.birthyear,0) = 0
then nvl(di.birthyear,0)
else ti.birthyear end as birthyear,
case when length(coalesce(ti.birthdecade,’’)) = 0
then di.birthdecade
else ti.birthdecade end as birthdecade,
case when nvl(ti.sex,0) = 0
then nvl(di.sex,0)
else ti.sex end as sex,
unix_timestamp(current_timestamp()) as updatetime
from dim_idcard di
full outer join temp_idcard ti
on di.idcardno = ti.idcardno;
ANALYZE TABLE dim_idcard COMPUTE STATISTICS FOR COLUMNS;
insert overwrite table temp_bankcard
select cardno,
‘’ as cardname,
max(cardtype) as cardtype,
0 as bankid,
max(cardbank) as cardbank,
‘’ as cardlevel,
max(carddesc) as carddesc,
0 as tagid,
unix_timestamp(current_timestamp()) as createtime
from (select cardno,
cardtype,
cardbank,
carddesc
from dw_bankcard_auth_apply
where dateid = ${etlDate}
and length(cardno) >= 1
union all
select cardno,
cardtype,
cardbank,
carddesc
from dw_activity_antirush_apply
where dateid = ${etlDate}
and length(cardno) >= 1
union all
select cardno,
cardtype,
cardbank,
carddesc
from dw_per_nature_apply
where dateid = ${etlDate}
and length(cardno) >= 1) dw
group by cardno;
load data local inpath ‘KaTeX parse error: Expected group after '_' at position 38: …c/bankcard_desc_̲{etlDate}_10.18.4.212.log’
overwrite into table stg_bankcard_desc;
with sbd as (select ID,
CardNO,
CardDesc,
SourceCreateTime,
OrigCreateTime
from (select ID,
replace(CardNO,’’’,’’) as CardNO,
replace(CardDesc,’’’,’’) as CardDesc,
replace(SourceCreateTime,’’’,’’) as SourceCreateTime,
unix_timestamp(replace(CreateTime,’’’,’’)) as OrigCreateTime,
row_number() over (partition by CardNO order by SourceCreateTime desc) as rn
from stg_bankcard_desc) tmp
where rn = 1)
insert overwrite table mid_bankcard_desc
select coalesce(sbd.ID,mbd.ID) as ID,
coalesce(sbd.CardNO,mbd.CardNO) as CardNO,
coalesce(sbd.CardDesc,mbd.CardDesc) as CardDesc,
coalesce(sbd.SourceCreateTime,mbd.SourceCreateTime) as SourceCreateTime,
coalesce(sbd.OrigCreateTime,mbd.OrigCreateTime) as OrigCreateTime,
unix_timestamp(current_timestamp()) as CreateTime
from mid_bankcard_desc mbd
full join sbd on sbd.cardno = mbd.CardNO;
ANALYZE TABLE mid_bankcard_desc COMPUTE STATISTICS FOR COLUMNS;
insert overwrite table temp_bankcard
select dbc.cardno,
dbc.cardname,
dbc.cardtype,
coalesce(db.bankid,dbc.bankid) as bankid,
coalesce(db.targetbankname,dbc.cardbank) as cardbank,
dbc.cardlevel,
coalesce(mbd.carddesc,dbc.carddesc) as carddesc,
coalesce(db.tagid,dbc.tagid) as tagid,
unix_timestamp(current_timestamp()) as updatetime
from temp_bankcard dbc
left join para_bank_ref db on dbc.cardbank = db.sourcebankname
left join mid_bankcard_desc mbd on mbd.cardno = dbc.cardno;
insert overwrite table dim_bankcard
select coalesce(tbc.cardno,dbc.cardno) as cardno,
‘’ as cardname,
case when nvl(tbc.cardtype,’’) = ‘’
then nvl(dbc.cardtype,’’)
else tbc.cardtype end as cardtype,
coalesce(tbc.bankid,dbc.bankid) as bankid,
case when nvl(tbc.cardbank,’’) = ‘’
then nvl(dbc.cardbank,’’)
else tbc.cardbank end as cardbank,
‘’ as cardlevel,
case when nvl(tbc.carddesc,’’) = ‘’
then nvl(dbc.carddesc,’’)
else tbc.carddesc end as carddesc,
coalesce(tbc.tagid,dbc.tagid) as tagid,
unix_timestamp(current_timestamp()) as updatetime
from dim_bankcard dbc
full outer join temp_bankcard tbc
on dbc.cardno = tbc.cardno;
ANALYZE TABLE dim_bankcard COMPUTE STATISTICS FOR COLUMNS;
insert overwrite table temp_mobile
select mobileno,
max(carrier) as carrier,
0 as provid,
max(provname) as provname,
0 as cityid,
max(cityname) as cityname,
0 as provtagid,
0 as citytagid,
unix_timestamp(current_timestamp()) as createtime
from (select mobileno,
carrier,
provname,
cityname
from dw_bankcard_auth_apply
where dateid = ${etlDate}
and length(mobileno) >= 1
union all
select mobileno,
carrier,
provname,
cityname
from dw_activity_antirush_apply
where dateid = ${etlDate}
and length(mobileno) >= 1
union all
select mobileno,
carrier,
provname,
cityname
from dw_overdue_black_apply
where dateid = ${etlDate}
and length(mobileno) >= 1
union all
select mobileno,
carrier,
provname,
cityname
from dw_telecom_realname_apply
where dateid = ${etlDate}
and length(mobileno) >= 1
union all
select mobileno,
carrier,
provname,
cityname
from dw_loan_antifraud_apply
where dateid = ${etlDate}
and length(mobileno) >= 1) dw
group by mobileno;
insert overwrite table temp_mobile
select dm.mobileno,
dm.carrier,
coalesce(dr.provid,dm.provid) as provid,
dm.provname,
coalesce(dr.regionid,dm.cityid) as cityid,
dm.cityname,
coalesce(dre.tagid,dm.provtagid) as provtagid,
coalesce(dr.tagid,dm.citytagid) as citytagid,
unix_timestamp(current_timestamp()) as updatetime
from temp_mobile dm
left join (select regionid,
regionname,
provid,
provshortname,
regionshortname,
tagid
from dim_region
where regiontype <> ‘P’
and substring(regionname,-1) <> ‘区’
and isnew = 1) dr
on dm.cityname = dr.regionshortname
and dm.provname = dr.provshortname
left join (select provid,
provshortname,
tagid
from dim_region
where regiontype = ‘P’
and isnew = 1) dre
on dm.provname = dre.provshortname;
insert overwrite table dim_mobile
select coalesce(tm.mobileno,dm.mobileno) as mobileno,
case when nvl(tm.carrier,’’) = ‘’
then nvl(dm.carrier,’’)
else tm.carrier end as carrier,
coalesce(tm.provid,dm.provid) as provid,
case when nvl(tm.provname,’’) = ‘’
then nvl(dm.provname,’’)
else tm.provname end as provname,
coalesce(tm.cityid,dm.cityid) as cityid,
case when nvl(tm.cityname,’’) = ‘’
then nvl(dm.cityname,’’)
else tm.cityname end as cityname,
coalesce(tm.provtagid,dm.provtagid) as provtagid,
coalesce(tm.citytagid,dm.citytagid) as citytagid,
unix_timestamp(current_timestamp()) as updatetime
from dim_mobile dm
full outer join temp_mobile tm
on dm.mobileno = tm.mobileno;
ANALYZE TABLE dim_mobile COMPUTE STATISTICS FOR COLUMNS;
load data local inpath ‘KaTeX parse error: Expected group after '_' at position 42: …s_cust_info_all_̲{etlDate}_10.18.4.212.log’
overwrite into table stg_cust;
insert overwrite table dim_cust
select CustID,
CustType,
CustAccType,
SourceCreateTime,
row_number() over (order by CustID) as TagID,
unix_timestamp(current_timestamp()) as CreateTime
from (select coalesce(replace(CustID,’’’,’’),’’) as CustID,
max(coalesce(replace(CustType,’’’,’’),’’)) as CustType,
min(coalesce(replace(CustAccType,’’’,’’),’’)) as CustAccType,
max(replace(SourceCreateTime,’’’,’’)) as SourceCreateTime
from stg_cust
group by CustID) tmp;
ANALYZE TABLE dim_cust COMPUTE STATISTICS FOR COLUMNS;
/卡鉴权成功的数据/
insert overwrite table temp_business_analysis
select idcardno,
max(name) as name,
cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_bankcard_auth_apply
where dateid = ${etlDate}
and result = ‘01’
group by idcardno,
cardno,
mobileno
union all
/卡鉴权不成功的数据 身份证数据/
select idcardno,
‘’ as name,
‘’ as cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_bankcard_auth_apply
where dateid = ${etlDate}
and result <> ‘01’
and length(idcardno) >= 1
group by idcardno
union all
/卡鉴权不成功的数据 银行卡数据/
select ‘’ as idcardno,
‘’ as name,
cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_bankcard_auth_apply
where dateid = ${etlDate}
and result <> ‘01’
and length(cardno) >= 1
group by cardno
union all
/卡鉴权不成功的数据 手机号数据/
select ‘’ as idcardno,
‘’ as name,
‘’ as cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_bankcard_auth_apply
where dateid = ${etlDate}
and result <> ‘01’
and length(mobileno) >= 1
group by mobileno;
insert into table temp_business_analysis
/慧识防刷身份证的数据/
select idcardno,
‘’ as name,
‘’ as cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_activity_antirush_apply
where dateid = ${etlDate}
and length(idcardno) >= 1
group by idcardno
union all
/慧识防刷银行卡的数据/
select ‘’ as idcardno,
‘’ as name,
cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_activity_antirush_apply
where dateid = ${etlDate}
and length(cardno) >= 1
group by cardno
union all
/慧识防刷手机号的数据/
select ‘’ as idcardno,
‘’ as name,
‘’ as cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_activity_antirush_apply
where dateid = ${etlDate}
and length(mobileno) >= 1
group by mobileno;
insert into table temp_business_analysis
/实名认证一致的数据/
select idcardno,
max(name) as name,
‘’ as cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_psb_realname_apply
where dateid = ${etlDate}
and stat = ‘01’
and length(idcardno) >= 1
group by idcardno
union all
/实名认证不一致的数据/
select idcardno,
‘’ as name,
‘’ as cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_psb_realname_apply
where dateid = ${etlDate}
and stat <> ‘01’
and length(idcardno) >= 1
group by idcardno;
insert into table temp_business_analysis
/云信贷风险名单手机号的数据/
select ‘’ as idcardno,
‘’ as name,
‘’ as cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_overdue_black_apply
where dateid = ${etlDate}
and length(mobileno) >= 1
group by mobileno;
insert into table temp_business_analysis
/运营商三要素认证一致的数据/
select idcardno,
max(name) as name,
‘’ as cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_telecom_realname_apply
where dateid = ${etlDate}
and result = ‘01’
group by idcardno,
mobileno
union all
/运营商三要素认证不一致 身份证的数据/
select idcardno,
‘’ as name,
‘’ as cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_telecom_realname_apply
where dateid = ${etlDate}
and result <> ‘01’
and length(idcardno) >= 1
group by idcardno
union all
/运营商三要素认证不一致 手机号的数据/
select ‘’ as idcardno,
‘’ as name,
‘’ as cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_telecom_realname_apply
where dateid = ${etlDate}
and result <> ‘01’
and length(mobileno) >= 1
group by mobileno;
insert into table temp_business_analysis
/信贷申请反欺诈手机号的数据/
select ‘’ as idcardno,
‘’ as name,
‘’ as cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_loan_antifraud_apply
where dateid = ${etlDate}
and length(mobileno) >= 1
group by mobileno;
insert into table temp_business_analysis
/个人模型类查询 身份证的数据/
select idcardno,
‘’ as name,
‘’ as cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_per_model_apply
where dateid = ${etlDate}
and length(idcardno) >= 1
group by idcardno;
insert into table temp_business_analysis
/用户画像查询 身份证的数据/
select ‘’ as idcardno,
‘’ as name,
cardno,
‘’ as mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from dw_per_nature_apply
where dateid = ${etlDate}
and length(cardno) >= 1
group by cardno;
/汇总/
insert overwrite table temp_business_analysis
select idcardno,
max(name) as name,
cardno,
mobileno,
max(unix_timestamp(sourcecreatetime)) as lasttime,
min(sourcecreatetime) as sourcecreatetime,
unix_timestamp(current_timestamp()) as createtime
from temp_business_analysis
group by idcardno,
cardno,
mobileno;
ANALYZE TABLE temp_business_analysis COMPUTE STATISTICS FOR COLUMNS;
/插入新身份证数据/
insert overwrite table temp_business_analysis_build
select 1 as userid,
tba.idcardno,
tba.cardno,
tba.mobileno,
tba.name,
unix_timestamp(tba.sourcecreatetime) as effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from (select idcardno,
cardno,
mobileno,
name,
lasttime,
sourcecreatetime
from temp_business_analysis
where length(idcardno) >= 1) tba
left join base_idcard_analysis bia on bia.idcardno = tba.idcardno
where bia.idcardno is null;
/插入新银行卡数据 1旧身份证 新银行卡（新身份证–银行卡对应关系）/
insert into table temp_business_analysis_build
select 1 as userid,
tba.idcardno,
tba.cardno,
tba.mobileno,
tba.name,
unix_timestamp(tba.sourcecreatetime) as effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_business_analysis tba
left semi join base_idcard_analysis bia on bia.idcardno = tba.idcardno
and length(tba.cardno) >= 1
left join (select idcardno,
cardno
from base_social_analysis
where effectflag = 1
and length(idcardno) >= 1
and length(cardno) >= 1) bsa on (bsa.idcardno = tba.idcardno
and bsa.cardno = tba.cardno)
where bsa.cardno is null;
/插入新银行卡数据 2无身份证 新银行卡/
insert into table temp_business_analysis_build
select 1 as userid,
‘’ as idcardno,
tba.cardno,
tba.mobileno,
tba.name,
unix_timestamp(tba.sourcecreatetime) as effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from (select cardno,
mobileno,
name,
lasttime,
sourcecreatetime
from temp_business_analysis
where length(cardno) >= 1
and length(idcardno) = 0) tba
left join base_bankcard_analysis bba on bba.cardno = tba.cardno
where bba.cardno is null;
/插入卡鉴权旧银行卡新手机数据（新银行卡–手机号对应关系）/
insert into table temp_business_analysis_build
select 1 as userid,
tba.idcardno,
tba.cardno,
tba.mobileno,
tba.name,
unix_timestamp(tba.sourcecreatetime) as effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_business_analysis tba
left semi join base_bankcard_analysis bba on bba.cardno = tba.cardno
and length(tba.mobileno) >= 1
left join (select cardno,
mobileno
from base_social_analysis
where effectflag = 1
and length(cardno) >= 1
and length(mobileno) >= 1) bsa on (bsa.cardno = tba.cardno
and bsa.mobileno = tba.mobileno)
left join (select cardno,
mobileno
from temp_business_analysis_build
where length(cardno) >= 1
and length(mobileno) >= 1) tbb on (tbb.cardno = tba.cardno
and tbb.mobileno = tba.mobileno)
where bsa.mobileno is null
and tbb.mobileno is null;
/插入运营商三要素认证新手机号数据 1旧身份证 新手机号（新身份证–手机号对应关系）/
insert into table temp_business_analysis_build
select 1 as userid,
tba.idcardno,
tba.cardno,
tba.mobileno,
tba.name,
unix_timestamp(tba.sourcecreatetime) as effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_business_analysis tba
left semi join base_idcard_analysis bia on bia.idcardno = tba.idcardno
and length(tba.cardno) = 0
and length(tba.mobileno) >= 1
left join (select idcardno,
mobileno
from base_social_analysis
where effectflag = 1
and length(idcardno) >= 1
and length(mobileno) >= 1) bsa on (bsa.idcardno = tba.idcardno
and bsa.mobileno = tba.mobileno)
left join (select idcardno,
mobileno
from temp_business_analysis_build
where length(idcardno) >= 1
and length(mobileno) >= 1) tbb on (tbb.idcardno = tba.idcardno
and tbb.mobileno = tba.mobileno)
where bsa.mobileno is null
and tbb.mobileno is null;
/插入新手机数据/
insert into table temp_business_analysis_build
select 1 as userid,
‘’ as idcardno,
‘’ as cardno,
tba.mobileno,
‘’ as name,
unix_timestamp(tba.sourcecreatetime) as effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from (select mobileno,
lasttime,
sourcecreatetime
from temp_business_analysis
where length(idcardno) = 0
and length(cardno) = 0
and length(mobileno) >= 1) tba
left join base_mobile_analysis bma on bma.mobileno = tba.mobileno
left join temp_business_analysis_build tbb on tbb.mobileno = tba.mobileno
where bma.mobileno is null
and tbb.mobileno is null;
set hive.strict.checks.cartesian.product=false;
/生成userid/
insert overwrite table temp_business_analysis_build
select row_number() over (order by tba.lasttime) + bsa.userid_max as userid,
tba.idcardno,
tba.cardno,
tba.mobileno,
tba.name,
tba.effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_business_analysis_build tba
cross join (select max(userid) as userid_max
from base_social_analysis
where userid < 1000000000000) bsa;
ANALYZE TABLE temp_business_analysis_build COMPUTE STATISTICS FOR COLUMNS;
/追加base_social_analysis需要合并的身份证数据/
insert overwrite table mid_business_analysis partition(querytype = 100)
select bsa.userid,
bsa.idcardno,
bsa.cardno,
bsa.mobileno,
bsa.name,
bsa.effecttime,
bsa.lasttime,
bsa.updatetime
from (select userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
lasttime,
updatetime
from base_social_analysis
where effectflag = 1
and length(idcardno) >= 1) bsa
left semi join temp_business_analysis_build sba on (sba.idcardno = bsa.idcardno
and length(sba.idcardno) >= 1);
/追加base_social_analysis需要合并的银行卡数据/
insert into table mid_business_analysis partition(querytype = 100)
select bsa.userid,
bsa.idcardno,
bsa.cardno,
bsa.mobileno,
bsa.name,
bsa.effecttime,
bsa.lasttime,
bsa.updatetime
from (select userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
lasttime,
updatetime
from base_social_analysis
where effectflag = 1
and length(cardno) >= 1) bsa
left semi join temp_business_analysis_build sba on (sba.cardno = bsa.cardno
and length(sba.cardno) >= 1)
left join mid_business_analysis mba on (mba.userid = bsa.userid
and mba.querytype = 100)
where mba.userid is null;
drop table if exists temp_business_analysis_mobile;
create table temp_business_analysis_mobile as
select tmp.userid,
tmp.idcardno,
tmp.cardno,
tmp.mobileno,
tmp.name,
tmp.effecttime,
tmp.lasttime,
tmp.updatetime
from (select bsa.userid,
bsa.idcardno,
bsa.cardno,
bsa.mobileno,
bsa.name,
bsa.effecttime,
bsa.lasttime,
bsa.updatetime,
rank() over (partition by bsa.mobileno order by bsa.lasttime desc) as rn
from (select fatheruserid,
userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
lasttime,
updatetime
from base_social_analysis
where effectflag = 1
and length(mobileno) >= 1) bsa
left semi join temp_business_analysis_build sba on (sba.mobileno = bsa.mobileno
and length(sba.mobileno) >= 1)) tmp
where tmp.rn = 1;
drop table if exists temp_business_analysis_userid;
create table temp_business_analysis_userid as
select tbam.userid
from (select userid,
idcardno,
mobileno
from temp_business_analysis_mobile
where length(idcardno) >= 1) tbam
left join (select idcardno,
mobileno
from temp_business_analysis_build
where length(idcardno) >= 1
and length(mobileno) >= 1) tbab on tbab.mobileno = tbam.mobileno
where tbab.idcardno <> tbam.idcardno;
/追加base_social_analysis需要合并的手机号数据/
insert into table mid_business_analysis partition(querytype = 100)
select tmp.userid,
tmp.idcardno,
tmp.cardno,
tmp.mobileno,
tmp.name,
tmp.effecttime,
tmp.lasttime,
tmp.updatetime
from temp_business_analysis_mobile tmp
left join temp_business_analysis_userid tba on tba.userid = tmp.userid
left join mid_business_analysis mba on (mba.userid = tmp.userid
and mba.querytype = 100)
where tba.userid is null
and mba.userid is null;
/追加日增量数据/
insert into table mid_business_analysis partition(querytype = 100)
select userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
lasttime,
updatetime
from temp_business_analysis_build;
ANALYZE TABLE mid_business_analysis PARTITION(querytype = 100) COMPUTE STATISTICS FOR COLUMNS;
/追加base_social_analysis需要合并的手机号数据/
insert overwrite table mid_business_analysis partition(querytype = 101)
select tmp.userid,
tmp.idcardno,
tmp.cardno,
tmp.mobileno,
tmp.name,
tmp.effecttime,
tmp.lasttime,
tmp.updatetime
from temp_business_analysis_mobile tmp
left join temp_business_analysis_userid tba on tba.userid = tmp.userid
where tba.userid is null;
/追加手机号日增量数据/
insert into table mid_business_analysis partition(querytype = 101)
select userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
lasttime,
updatetime
from temp_business_analysis_build
where length(mobileno) >= 1;
ANALYZE TABLE mid_business_analysis PARTITION(querytype = 101) COMPUTE STATISTICS FOR COLUMNS;
/更新中间表mid_business_analysis数据 1：有身份证号/
insert overwrite table mid_business_analysis partition(querytype = 1)
select mba1.userid,
mba1.idcardno,
coalesce(mba2.cardno,’’) as cardno,
coalesce(mba3.mobileno,’’) as mobileno,
coalesce(mba4.name,’’) as name,
mba1.effecttime,
mba1.lasttime,
mba1.updatetime
from (select min(userid) as userid,
idcardno,
min(effecttime) as effecttime,
max(lasttime) as lasttime,
min(updatetime) as updatetime
from mid_business_analysis
where querytype = 100
and length(idcardno) >= 1
group by idcardno) mba1
left join (select idcardno,
substring(max(concat(cast(lasttime as string),cardno)),length(max(cast(lasttime as string))) + 1) as cardno
from mid_business_analysis
where querytype = 100
and length(idcardno) >= 1
and length(cardno) >= 1
group by idcardno) mba2 on mba2.idcardno = mba1.idcardno
left join (select idcardno,
substring(max(concat(cast(lasttime as string),mobileno)),length(max(cast(lasttime as string))) + 1) as mobileno
from mid_business_analysis
where querytype = 100
and length(idcardno) >= 1
and length(mobileno) >= 1
group by idcardno) mba3 on mba3.idcardno = mba1.idcardno
left join (select idcardno,
substring(max(concat(cast(lasttime as string),name)),length(max(cast(lasttime as string))) + 1) as name
from mid_business_analysis
where querytype = 100
and length(idcardno) >= 1
and length(name) >= 1
group by idcardno) mba4 on mba4.idcardno = mba1.idcardno;
ANALYZE TABLE mid_business_analysis PARTITION(querytype = 1) COMPUTE STATISTICS FOR COLUMNS;
/更新中间表mid_business_analysis数据 2：有银行卡号/
insert overwrite table mid_business_analysis partition(querytype = 2)
select mba1.userid,
coalesce(mba2.idcardno,’’) as idcardno,
mba1.cardno,
coalesce(mba3.mobileno,’’) as mobileno,
coalesce(mba4.name,’’) as name,
mba1.effecttime,
mba1.lasttime,
mba1.updatetime
from (select min(userid) as userid,
cardno,
min(effecttime) as effecttime,
max(lasttime) as lasttime,
min(updatetime) as updatetime
from mid_business_analysis
where querytype = 100
and length(cardno) >= 1
group by cardno) mba1
left join (select cardno,
substring(max(concat(cast(lasttime as string),idcardno)),length(max(cast(lasttime as string))) + 1) as idcardno
from mid_business_analysis
where querytype = 100
and length(idcardno) >= 1
and length(cardno) >= 1
group by cardno) mba2 on mba2.cardno = mba1.cardno
left join (select cardno,
substring(max(concat(cast(lasttime as string),mobileno)),length(max(cast(lasttime as string))) + 1) as mobileno
from mid_business_analysis
where querytype = 100
and length(cardno) >= 1
and length(mobileno) >= 1
group by cardno) mba3 on mba3.cardno = mba1.cardno
left join (select cardno,
substring(max(concat(cast(lasttime as string),name)),length(max(cast(lasttime as string))) + 1) as name
from mid_business_analysis
where querytype = 100
and length(cardno) >= 1
and length(name) >= 1
group by cardno) mba4 on mba4.cardno = mba1.cardno;
ANALYZE TABLE mid_business_analysis PARTITION(querytype = 2) COMPUTE STATISTICS FOR COLUMNS;
/更新中间表mid_business_analysis数据 3：有手机号/
insert overwrite table mid_business_analysis partition(querytype = 3)
select mba1.userid,
coalesce(mba2.idcardno,’’) as idcardno,
coalesce(mba3.cardno,’’) as cardno,
mba1.mobileno,
coalesce(mba4.name,’’) as name,
mba1.effecttime,
mba1.lasttime,
mba1.updatetime
from (select min(userid) as userid,
mobileno,
min(effecttime) as effecttime,
max(lasttime) as lasttime,
min(updatetime) as updatetime
from mid_business_analysis
where querytype = 101
group by mobileno) mba1
left join (select mobileno,
substring(max(concat(cast(lasttime as string),idcardno)),length(max(cast(lasttime as string))) + 1) as idcardno
from mid_business_analysis
where querytype = 101
and length(idcardno) >= 1
group by mobileno) mba2 on mba2.mobileno = mba1.mobileno
left join (select mobileno,
substring(max(concat(cast(lasttime as string),cardno)),length(max(cast(lasttime as string))) + 1) as cardno
from mid_business_analysis
where querytype = 101
and length(cardno) >= 1
group by mobileno) mba3 on mba3.mobileno = mba1.mobileno
left join (select mobileno,
substring(max(concat(cast(lasttime as string),name)),length(max(cast(lasttime as string))) + 1) as name
from mid_business_analysis
where querytype = 101
and length(name) >= 1
group by mobileno) mba4 on mba4.mobileno = mba1.mobileno;
ANALYZE TABLE mid_business_analysis PARTITION(querytype = 3) COMPUTE STATISTICS FOR COLUMNS;
/填充数据身份证存在/
insert overwrite table temp_social_analysis
select tba.userid,
tba.idcardno,
case when tba.cardno = ‘’ then coalesce(mba.cardno,’’) else tba.cardno end as cardno,
case when tba.mobileno = ‘’ then coalesce(mba.mobileno,’’) else tba.mobileno end as mobileno,
case when tba.name = ‘’ then coalesce(mba.name,’’) else tba.name end as name,
tba.effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from (select userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
lasttime
from mid_business_analysis
where querytype = 100) tba
left join mid_business_analysis mba on tba.idcardno = mba.idcardno
and mba.querytype = 1
and length(tba.idcardno) >= 1;
/填充数据银行卡存在/
insert overwrite table temp_social_analysis
select tba.userid,
case when tba.idcardno = ‘’ then coalesce(mba.idcardno,’’) else tba.idcardno end as idcardno,
tba.cardno,
case when tba.mobileno = ‘’ then coalesce(mba.mobileno,’’) else tba.mobileno end as mobileno,
case when tba.name = ‘’ then coalesce(mba.name,’’) else tba.name end as name,
tba.effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_social_analysis tba
left join mid_business_analysis mba on tba.cardno = mba.cardno
and mba.querytype = 2
and length(tba.cardno) >= 1;
/填充数据手机号存在/
insert overwrite table temp_social_analysis
select tba.userid,
case when tba.idcardno = ‘’ then coalesce(mba.idcardno,’’) else tba.idcardno end as idcardno,
case when tba.cardno = ‘’ then coalesce(mba.cardno,’’) else tba.cardno end as cardno,
tba.mobileno,
case when tba.name = ‘’ then coalesce(mba.name,’’) else tba.name end as name,
tba.effecttime,
tba.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_social_analysis tba
left semi join mid_business_analysis mb1 on mb1.userid = tba.userid
and mb1.querytype = 101
left join mid_business_analysis mba on mba.mobileno = tba.mobileno
and mba.querytype = 3
and length(tba.mobileno) >= 1
union all
select tb.userid,
tb.idcardno,
tb.cardno,
tb.mobileno,
tb.name,
tb.effecttime,
tb.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_social_analysis tb
left join mid_business_analysis mb2 on mb2.userid = tb.userid
and mb2.querytype = 101
where mb2.userid is null;
ANALYZE TABLE temp_social_analysis COMPUTE STATISTICS FOR COLUMNS;
drop table if exists temp_social_analysis_mobile;
create table temp_social_analysis_mobile as
select cardno,
min(userid) as userid,
substring(max(concat(cast(lasttime as string),mobileno)),length(max(cast(lasttime as string))) + 1) as mobileno,
count(distinct mobileno) as cm
from temp_social_analysis
where length(mobileno) >= 1
and length(cardno) >= 1
group by cardno
having cm >= 2;
ANALYZE TABLE temp_social_analysis_mobile COMPUTE STATISTICS FOR COLUMNS;
drop table if exists temp_social_idcard;
select coalesce(bia.userid,tsa.fatheruserid) as fatheruserid,
tsa.idcardno
from (select min(userid) as fatheruserid,
idcardno
from temp_social_analysis
where length(idcardno) >= 1
group by idcardno) tsa
left join base_idcard_analysis bia on bia.idcardno = tsa.idcardno;
ANALYZE TABLE temp_social_idcard COMPUTE STATISTICS FOR COLUMNS;
drop table if exists temp_social_bankcard;
create table temp_social_bankcard as
select min(userid) as fatheruserid,
cardno
from temp_social_analysis
where length(cardno) >= 1
group by cardno
having count(1) >= 2;
ANALYZE TABLE temp_social_bankcard COMPUTE STATISTICS FOR COLUMNS;
/插入有银行卡的失效数据/
insert into table base_social_analysis partition(effectflag = 0)
select coalesce(tsi.fatheruserid,tmp2.fatheruserid) as fatheruserid,
tmp1.userid,
tmp1.idcardno,
tmp1.cardno,
tmp1.mobileno,
tmp1.name,
tmp1.effecttime,
unix_timestamp(current_timestamp()) as expirytime,
tmp1.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_social_analysis tmp1
join temp_social_bankcard tmp2 on (tmp2.cardno = tmp1.cardno
and length(tmp1.cardno) >= 1)
left join temp_social_idcard tsi on tsi.idcardno = tmp1.idcardno
where tmp1.userid > tmp2.fatheruserid;
/插入有银行卡但手机号失效数据/
insert into table base_social_analysis partition(effectflag = 0)
select coalesce(tsi.fatheruserid,tsb.fatheruserid,tmp1.userid) as fatheruserid,
tmp1.userid + 1000000000000 as userid,
tmp1.idcardno,
tmp1.cardno,
tmp1.mobileno,
tmp1.name,
tmp1.effecttime,
unix_timestamp(current_timestamp()) as expirytime,
unix_timestamp(’${etlDate}’,‘yyyyMMdd’) as lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_social_analysis tmp1
left semi join temp_social_analysis_mobile tmp2 on tmp1.userid = tmp2.userid
left join temp_social_idcard tsi on tsi.idcardno = tmp1.idcardno
left join temp_social_bankcard tsb on tsb.cardno = tmp1.cardno;
/插入有身份证旧手机号用户的失效数据/
insert into table base_social_analysis partition(effectflag = 0)
select coalesce(tsi.fatheruserid,bsa.userid) as fatheruserid,
bsa.userid,
bsa.idcardno,
bsa.cardno,
bsa.mobileno,
bsa.name,
bsa.effecttime,
unix_timestamp(current_timestamp()) as expirytime,
bsa.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from (select userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
lasttime
from temp_social_analysis
where length(idcardno) >= 1
and length(cardno) = 0
and length(mobileno) >= 1) bsa
left join (select max(userid) as userid,
idcardno,
mobileno
from temp_social_analysis
where length(idcardno) >= 1
and length(cardno) = 0
and length(mobileno) >= 1
group by idcardno,
mobileno) tsa on (tsa.idcardno = bsa.idcardno
and tsa.mobileno = bsa.mobileno)
left join temp_social_idcard tsi on tsi.idcardno = bsa.idcardno
where tsa.userid > bsa.userid;
/重写生效数据 未参与合并的数据/
insert overwrite table base_social_analysis partition(effectflag = 1)
select bsa.fatheruserid,
bsa.userid,
bsa.idcardno,
bsa.cardno,
bsa.mobileno,
bsa.name,
bsa.effecttime,
bsa.expirytime,
bsa.lasttime,
bsa.updatetime
from (select fatheruserid,
userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
expirytime,
lasttime,
updatetime
from base_social_analysis
where effectflag = 1) bsa
left join temp_social_analysis tsa on tsa.userid = bsa.userid
where tsa.userid is null;
/插入参与合并的生效数据/
insert into table base_social_analysis partition(effectflag = 1)
select coalesce(tsi.fatheruserid,tsb.fatheruserid,tsa.userid) as fatheruserid,
tsa.userid,
tsa.idcardno,
tsa.cardno,
coalesce(sa.mobileno,tsa.mobileno) as mobileno,
tsa.name,
tsa.effecttime,
unix_timestamp(‘9999-12-31 23:59:59.999’) as expirytime,
tsa.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from temp_social_analysis tsa
left join temp_social_analysis_mobile sa on sa.userid = tsa.userid
left join temp_social_idcard tsi on tsi.idcardno = tsa.idcardno
left join temp_social_bankcard tsb on tsb.cardno = tsa.cardno
left join base_social_analysis bsa on bsa.userid = tsa.userid
and bsa.effectflag = 0
where bsa.userid is null;
ANALYZE TABLE base_social_analysis PARTITION(effectflag) COMPUTE STATISTICS FOR COLUMNS;
drop table if exists temp_analysis_mobile;
create table temp_analysis_mobile as
select tmp.userid,
tmp.mobileno,
tmp.lasttime,
unix_timestamp(current_timestamp()) as updatetime
from (select bsa.userid,
bsa.mobileno,
tba.lasttime,
rank() over (partition by bsa.mobileno order by bsa.lasttime desc) as rn
from (select userid,
mobileno,
lasttime
from base_social_analysis
where effectflag = 1
and length(mobileno) >= 1) bsa
join (select mobileno,
max(lasttime) as lasttime
from temp_business_analysis
where length(mobileno) >= 1
group by mobileno) tba on tba.mobileno = bsa.mobileno) tmp
where tmp.rn = 1;
ANALYZE TABLE temp_analysis_mobile COMPUTE STATISTICS FOR COLUMNS;
/更新base_social_analysis 生效数据的lasttime 通过手机号/
insert overwrite table base_social_analysis partition(effectflag = 1)
select bsa.fatheruserid,
bsa.userid,
bsa.idcardno,
bsa.cardno,
bsa.mobileno,
bsa.name,
bsa.effecttime,
bsa.expirytime,
greatest(coalesce(tbm.lasttime,0),coalesce(bsa.lasttime,0)) as lasttime,
coalesce(tbm.updatetime,bsa.updatetime) as updatetime
from (select fatheruserid,
userid,
idcardno,
cardno,
mobileno,
name,
effecttime,
expirytime,
lasttime,
updatetime
from base_social_analysis
where effectflag = 1) bsa
left join temp_analysis_mobile tbm on tbm.userid = bsa.userid;
ANALYZE TABLE base_social_analysis PARTITION(effectflag = 1) COMPUTE STATISTICS FOR COLUMNS;
– temp_activity_antirush_apply
DROP TABLE IF EXISTS temp_activity_antirush_apply;
CREATE TABLE temp_activity_antirush_apply AS
SELECT ApplyID,
SourceCreateTime,
MobileNO,
Level
FROM dw_activity_antirush_apply
where dateid = ${etlDate}
AND LENGTH(MobileNO) > 0;
– temp_activity_antirush_result
DROP TABLE IF EXISTS temp_activity_antirush_result;
CREATE TABLE temp_activity_antirush_result AS
SELECT ApplyID,
RiskCode
FROM dw_activity_antirush_result
WHERE dateid = ${etlDate};
– temp_activity_antirush_apply_sum
DROP TABLE IF EXISTS temp_activity_antirush_apply_sum;
CREATE TABLE temp_activity_antirush_apply_sum AS
select a.MobileNO,
SUM(CASE WHEN b.RiskCode = 1 THEN 1 ELSE 0 END) AS LowCreditCounts,
SUM(CASE WHEN b.RiskCode = 2 THEN 1 ELSE 0 END) AS GarbageCounts,
SUM(CASE WHEN b.RiskCode = 3 THEN 1 ELSE 0 END) AS InvalidCounts,
SUM(CASE WHEN b.RiskCode = 4 THEN 1 ELSE 0 END) AS BlacklistCounts,
SUM(CASE WHEN b.RiskCode = 101 THEN 1 ELSE 0 END) AS BatchCounts,
SUM(CASE WHEN b.RiskCode = 102 THEN 1 ELSE 0 END) AS AutoCounts,
SUM(CASE WHEN b.RiskCode = 201 THEN 1 ELSE 0 END) AS EnvAnorCounts,
SUM(CASE WHEN b.RiskCode = 202 THEN 1 ELSE 0 END) AS RptAnorCounts,
SUM(CASE WHEN b.RiskCode = 203 THEN 1 ELSE 0 END) AS HitLibrCounts,
substr(max(concat(a.SourceCreateTime,’|’,b.RiskCode)),21) AS LastRiskType,
COUNT(DISTINCT CASE WHEN a.Level = ‘D’ THEN a.ApplyID ELSE NULL END) AS DCallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘C’ THEN a.ApplyID ELSE NULL END) AS CCallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘B’ THEN a.ApplyID ELSE NULL END) AS BCallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘A’ THEN a.ApplyID ELSE NULL END) AS ACallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘S’ THEN a.ApplyID ELSE NULL END) AS SCallCounts,
substr(max(concat(a.SourceCreateTime,’|’,a.Level)),21) AS LastRiskLevel,
COUNT(DISTINCT a.ApplyID) AS D0Counts,
MAX(a.SourceCreateTime) AS LastTime,
unix_timestamp(current_timestamp()) AS UpdateTime
FROM temp_activity_antirush_apply a
LEFT JOIN temp_activity_antirush_result b ON a.ApplyID = b.ApplyID
GROUP BY a.MobileNO;
– temp_aa_analysis
INSERT OVERWRITE TABLE temp_aa_analysis
SELECT b.UserID,
a.LowCreditCounts,
a.GarbageCounts,
a.InvalidCounts,
a.BlacklistCounts,
a.BatchCounts,
a.AutoCounts,
a.EnvAnorCounts,
a.RptAnorCounts,
a.HitLibrCounts,
a.LastRiskType,
a.DCallCounts,
a.CCallCounts,
a.BCallCounts,
a.ACallCounts,
a.SCallCounts,
a.LastRiskLevel,
a.D0Counts AS CallTotalCounts,
a.D0Counts,
0 AS D1Counts,
0 AS D2Counts,
0 AS D3Counts,
0 AS D4Counts,
0 AS D5Counts,
0 AS D6Counts,
a.D0Counts AS M0Counts,
0 AS M1Counts,
0 AS M2Counts,
0 AS M3Counts,
0 AS M4Counts,
0 AS M5Counts,
0 AS M6Counts,
0 AS M7Counts,
0 AS M8Counts,
0 AS M9Counts,
0 AS M10Counts,
0 AS M11Counts,
a.D0Counts AS Rcnt7DaysCounts,
a.D0Counts AS Rcnt3MonsCounts,
a.D0Counts AS RcntYearCounts,
a.LastTime,
unix_timestamp(current_timestamp()) AS UpdateTime
FROM temp_activity_antirush_apply_sum a
JOIN base_mobile_analysis b ON a.MobileNO = b.MobileNO;
–月初
INSERT INTO TABLE temp_aa_analysis
SELECT UserID,
LowCreditCounts,
GarbageCounts,
InvalidCounts,
BlacklistCounts,
BatchCounts,
AutoCounts,
EnvAnorCounts,
RptAnorCounts,
HitLibrCounts,
LastRiskType,
DCallCounts,
CCallCounts,
BCallCounts,
ACallCounts,
SCallCounts,
LastRiskLevel,
CallTotalCounts,
0 AS D0Counts,
D0Counts AS D1Counts,
D1Counts AS D2Counts,
D2Counts AS D3Counts,
D3Counts AS D4Counts,
D4Counts AS D5Counts,
D5Counts AS D6Counts,
0 AS M0Counts,
M0Counts AS M1Counts,
M1Counts AS M2Counts,
M2Counts AS M3Counts,
M3Counts AS M4Counts,
M4Counts AS M5Counts,
M5Counts AS M6Counts,
M6Counts AS M7Counts,
M7Counts AS M8Counts,
M8Counts AS M9Counts,
M9Counts AS M10Counts,
M10Counts AS M11Counts,
Rcnt7DaysCounts - D6Counts AS Rcnt7DaysCounts,
Rcnt3MonsCounts - M2Counts AS Rcnt3MonsCounts,
RcntYearCounts - M11Counts AS RcntYearCounts,
LastTime,
UpdateTime
FROM base_aa_analysis;
– temp_activity_antirush_apply
DROP TABLE IF EXISTS temp_activity_antirush_apply;
CREATE TABLE temp_activity_antirush_apply AS
SELECT ApplyID,
SourceCreateTime,
MobileNO,
Level
FROM dw_activity_antirush_apply
where dateid = ${etlDate}
AND LENGTH(MobileNO) > 0;
– temp_activity_antirush_result
DROP TABLE IF EXISTS temp_activity_antirush_result;
CREATE TABLE temp_activity_antirush_result AS
SELECT ApplyID,
RiskCode
FROM dw_activity_antirush_result
WHERE dateid = ${etlDate};
– temp_activity_antirush_apply_sum
DROP TABLE IF EXISTS temp_activity_antirush_apply_sum;
CREATE TABLE temp_activity_antirush_apply_sum AS
select a.MobileNO,
SUM(CASE WHEN b.RiskCode = 1 THEN 1 ELSE 0 END) AS LowCreditCounts,
SUM(CASE WHEN b.RiskCode = 2 THEN 1 ELSE 0 END) AS GarbageCounts,
SUM(CASE WHEN b.RiskCode = 3 THEN 1 ELSE 0 END) AS InvalidCounts,
SUM(CASE WHEN b.RiskCode = 4 THEN 1 ELSE 0 END) AS BlacklistCounts,
SUM(CASE WHEN b.RiskCode = 101 THEN 1 ELSE 0 END) AS BatchCounts,
SUM(CASE WHEN b.RiskCode = 102 THEN 1 ELSE 0 END) AS AutoCounts,
SUM(CASE WHEN b.RiskCode = 201 THEN 1 ELSE 0 END) AS EnvAnorCounts,
SUM(CASE WHEN b.RiskCode = 202 THEN 1 ELSE 0 END) AS RptAnorCounts,
SUM(CASE WHEN b.RiskCode = 203 THEN 1 ELSE 0 END) AS HitLibrCounts,
substr(max(concat(a.SourceCreateTime,’|’,b.RiskCode)),21) AS LastRiskType,
COUNT(DISTINCT CASE WHEN a.Level = ‘D’ THEN a.ApplyID ELSE NULL END) AS DCallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘C’ THEN a.ApplyID ELSE NULL END) AS CCallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘B’ THEN a.ApplyID ELSE NULL END) AS BCallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘A’ THEN a.ApplyID ELSE NULL END) AS ACallCounts,
COUNT(DISTINCT CASE WHEN a.Level = ‘S’ THEN a.ApplyID ELSE NULL END) AS SCallCounts,
substr(max(concat(a.SourceCreateTime,’|’,a.Level)),21) AS LastRiskLevel,
COUNT(DISTINCT a.ApplyID) AS D0Counts,
MAX(a.SourceCreateTime) AS LastTime,
unix_timestamp(current_timestamp()) AS UpdateTime
FROM temp_activity_antirush_apply a
LEFT JOIN temp_activity_antirush_result b ON a.ApplyID = b.ApplyID
GROUP BY a.MobileNO;
– temp_aa_analysis
INSERT OVERWRITE TABLE temp_aa_analysis
SELECT b.UserID,
a.LowCreditCounts,
a.GarbageCounts,
a.InvalidCounts,
a.BlacklistCounts,
a.BatchCounts,
a.AutoCounts,
a.EnvAnorCounts,
a.RptAnorCounts,
a.HitLibrCounts,
a.LastRiskType,
a.DCallCounts,
a.CCallCounts,
a.BCallCounts,
a.ACallCounts,
a.SCallCounts,
a.LastRiskLevel,
a.D0Counts AS CallTotalCounts,
a.D0Counts,
0 AS D1Counts,
0 AS D2Counts,
0 AS D3Counts,
0 AS D4Counts,
0 AS D5Counts,
0 AS D6Counts,
a.D0Counts AS M0Counts,
0 AS M1Counts,
0 AS M2Counts,
0 AS M3Counts,
0 AS M4Counts,
0 AS M5Counts,
0 AS M6Counts,
0 AS M7Counts,
0 AS M8Counts,
0 AS M9Counts,
0 AS M10Counts,
0 AS M11Counts,
a.D0Counts AS Rcnt7DaysCounts,
a.D0Counts AS Rcnt3MonsCounts,
a.D0Counts AS RcntYearCounts,
a.LastTime,
unix_timestamp(current_timestamp()) AS UpdateTime
FROM temp_activity_antirush_apply_sum a
JOIN base_mobile_analysis b ON a.MobileNO = b.MobileNO;
–非月初
INSERT INTO TABLE temp_aa_analysis
SELECT UserID,
LowCreditCounts,
GarbageCounts,
InvalidCounts,
BlacklistCounts,
BatchCounts,
AutoCounts,
EnvAnorCounts,
RptAnorCounts,
HitLibrCounts,
LastRiskType,
DCallCounts,
CCallCounts,
BCallCounts,
ACallCounts,
SCallCounts,
LastRiskLevel,
CallTotalCounts,
0 AS D0Counts,
D0Counts AS D1Counts,
D1Counts AS D2Counts,
D2Counts AS D3Counts,
D3Counts AS D4Counts,
D4Counts AS D5Counts,
D5Counts AS D6Counts,
M0Counts,
M1Counts,
M2Counts,
M3Counts,
M4Counts,
M5Counts,
M6Counts,
M7Counts,
M8Counts,
M9Counts,
M10Counts,
M11Counts,
Rcnt7DaysCounts - D6Counts AS Rcnt7DaysCounts,
Rcnt3MonsCounts,
RcntYearCounts,
LastTime,
UpdateTime
FROM base_aa_analysis;
– base_aa_analysis
INSERT OVERWRITE TABLE base_aa_analysis
SELECT UserID,
SUM(LowCreditCounts) AS LowCreditCounts,
SUM(GarbageCounts) AS GarbageCounts,
SUM(InvalidCounts) AS InvalidCounts,
SUM(BlacklistCounts) AS BlacklistCounts,
SUM(BatchCounts) AS BatchCounts,
SUM(AutoCounts) AS AutoCounts,
SUM(EnvAnorCounts) AS EnvAnorCounts,
SUM(RptAnorCounts) AS RptAnorCounts,
SUM(HitLibrCounts) AS HitLibrCounts,
SUBSTR(MAX(CONCAT(LastTime,’|’,LastRiskType)),21) AS LastRiskType,
SUM(DCallCounts) AS DCallCounts,
SUM(CCallCounts) AS CCallCounts,
SUM(BCallCounts) AS BCallCounts,
SUM(ACallCounts) AS ACallCounts,
SUM(SCallCounts) AS SCallCounts,
SUBSTR(MAX(CONCAT(LastTime,’|’,LastRiskLevel)),21) AS LastRiskLevel,
SUM(CallTotalCounts) AS CallTotalCounts,
SUM(D0Counts) AS D0Counts,
SUM(D1Counts) AS D1Counts,
SUM(D2Counts) AS D2Counts,
SUM(D3Counts) AS D3Counts,
SUM(D4Counts) AS D4Counts,
SUM(D5Counts) AS D5Counts,
SUM(D6Counts) AS D6Counts,
SUM(M0Counts) AS M0Counts,
SUM(M1Counts) AS M1Counts,
SUM(M2Counts) AS M2Counts,
SUM(M3Counts) AS M3Counts,
SUM(M4Counts) AS M4Counts,
SUM(M5Counts) AS M5Counts,
SUM(M6Counts) AS M6Counts,
SUM(M7Counts) AS M7Counts,
SUM(M8Counts) AS M8Counts,
SUM(M9Counts) AS M9Counts,
SUM(M10Counts) AS M10Counts,
SUM(M11Counts) AS M11Counts,
SUM(Rcnt7DaysCounts) AS Rcnt7DaysCounts,
SUM(Rcnt3MonsCounts) AS Rcnt3MonsCounts,
SUM(RcntYearCounts) AS RcntYearCounts,
max(LastTime) AS LastTime,
max(UpdateTime) AS UpdateTime
FROM temp_aa_analysis
GROUP BY UserID;
ANALYZE TABLE base_aa_analysis COMPUTE STATISTICS FOR COLUMNS;
– base_aa_analysis_his
INSERT OVERWRITE TABLE base_aa_analysis_his partition(DateID=${etlDate})
SELECT UserID,
LowCreditCounts,
GarbageCounts,
InvalidCounts,
BlacklistCounts,
BatchCounts,
AutoCounts,
EnvAnorCounts,
RptAnorCounts,
HitLibrCounts,
LastRiskType,
DCallCounts,
CCallCounts,
BCallCounts,
ACallCounts,
SCallCounts,
LastRiskLevel,
CallTotalCounts,
D0Counts,
D1Counts,
D2Counts,
D3Counts,
D4Counts,
D5Counts,
D6Counts,
M0Counts,
M1Counts,
M2Counts,
M3Counts,
M4Counts,
M5Counts,
M6Counts,
M7Counts,
M8Counts,
M9Counts,
M10Counts,
M11Counts,
Rcnt7DaysCounts,
Rcnt3MonsCounts,
RcntYearCounts,
LastTime,
UpdateTime
FROM base_aa_analysis;
扫一扫
9万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
