1、vintage、滚动率、迁移率
https://zhuanlan.zhihu.com/p/81027037/
vintage
滚动率：前六个月到后六个月状态
迁徙率是在vintage基础上加工而来的。
vintage
vintage：
select loan_month,mob,sum(loan_amt) as "放款金额",
sum(if(overdueday>=1,left_repay_principal,0)) as 1+剩余本金,
sum(if(overdueday>=60,left_repay_principal,0)) as 60+剩余本金,
sum(if(overdueday>=90,left_repay_principal,0)) as 90+剩余本金,
sum(if(overdueday>=30,left_repay_principal,0)) as 30+剩余本金,
sum(if(overdueday>=1,left_repay_principal,0))/sum(loan_amt) as "vintage1+",
sum(if(overdueday>=60,left_repay_principal,0))/sum(loan_amt) as "vintage60+",
sum(if(overdueday>=90,left_repay_principal,0))/sum(loan_amt) as "vintage90+",
sum(if(overdueday>=30,left_repay_principal,0))/sum(loan_amt) as "vintage30+"
from (
select loan_month,ref_nbr,mob,loan_amt,max(over_day) as overdueday,
(loan_amt - sum(
case
when (repay_date is not null and repay_date>mob_date) or repay_date is null then 0
when repay_date is not null and repay_date<=mob_date then principal_paid
else 0 end )) as left_repay_principal
from (
select *,
(case
when repay_date is null then datediff(mob_date,grace_date_new)
when repay_date is not null and principal != principal_paid then datediff(mob_date,grace_date_new)
when repay_date is not null and principal = principal_paid and mob_date < repay_date then datediff(mob_date,grace_date_new)
else 0 end
) as over_day from (
select ,date_format(loan_date,'%Y-%m') as loan_month,
((year(mob_date)-year(last_day(loan_date)))12 +(month(mob_date)-month(last_day(loan_date)))) as mob
from
(select * from
(select jingfa_bill_no as ref_nbr,SUBSTR(loan_date,1,10) as loan_date, loan_amt from dev_risk_tmp.cf_bill_info_guest group by jingfa_bill_no,loan_date,loan_amt) as a
inner join
(select due_bill_no,curr_term,fund_prin_amt as principal,rel_fund_prin_amt as principal_paid,DATE_FORMAT(real_date,"%Y-%m-%d") as repay_date,
DATE_FORMAT(rpy_date,"%Y-%m-%d") as grace_date_new from dev_risk_tmp.cf_repay_plan_guest group by
due_bill_no,curr_term,fund_prin_amt,rel_fund_prin_amt,DATE_FORMAT(rpy_date,"%Y-%m-%d"),DATE_FORMAT(real_date,"%Y-%m-%d")) as b
on a.ref_nbr = b.due_bill_no) as c
cross join
(select '2022-12-31' as mob_date
union all
select '2023-01-31' as mob_date
union all
select '2023-02-28' as mob_date
union all
select '2023-03-31' as mob_date
union all
select '2023-04-30' as mob_date
union all
select '2023-05-31' as mob_date
union all
select '2023-06-30' as mob_date
union all
select '2023-07-31' as mob_date
union all
select '2023-08-31' as mob_date
union all
select '2023-09-30' as mob_date
union all
select '2023-10-31' as mob_date
union all
select '2023-11-30' as mob_date
union all
select '2023-12-31' as mob_date ) as d
where c.loan_date<=d.mob_date and mob_date <= CURRENT_DATE() ) as e ) f group by loan_month,ref_nbr,mob,loan_amt) as g group by loan_month,mob
滚动率
select a.loan_month,b.M1/a.C as 'C-M1',c.M2/a.C AS 'C-M2',d.M3/a.C AS 'C-M3' from
(select * from chenqianguang.GD) as a
left join
(select * from chenqianguang.GD) as b
on a.loan_month =b.loan_month and a.mob + 1=b.mob
LEFT join
(select * from chenqianguang.GD) as c
on a.loan_month =c.loan_month and a.mob + 2=c.mob
LEFT join
(select * from chenqianguang.GD) as d
on a.loan_month =d.loan_month and a.mob + 3=d.mob
group by a.loan_month
2、FPD和DPD计算
dpd和fpd
FPD：第一期逾期，注意到期，人维度还是订单维度，是否首期首逾。
select bill_no,DATEDIFF(watchday,pre_repay_date) as grance_date_diff,
(case when settle_time = '' and watchday>= pre_repay_date then DATEDIFF(watchday,pre_repay_date)
when settle_time != '' then DATEDIFF(settle_time,pre_repay_date) else 0 end) as fpd from
(select bill_no,current_period,pre_repay_amt,repayment_amt,settle_time,pre_repay_date,dict_repay_plan_status,
DATE_FORMAT(CURRENT_DATE(),"%Y/%m/%d") as watchday
from clx_loan.fund_plat_repay_plan_last where current_period = 1
group by bill_no,current_period,pre_repay_amt,repayment_amt,settle_time,pre_repay_date,dict_repay_plan_status) as a
where DATEDIFF(watchday,pre_repay_date) >0
DPD：注意人维度还是订单维度。
select bill_no,DATEDIFF(watchday,pre_repay_date) as grance_date_diff,
(case when settle_time is null and watchday>= pre_repay_date then DATEDIFF(watchday,pre_repay_date)
when settle_time is not null then DATEDIFF(settle_time,pre_repay_date) else 0 end) as dpd from
(select bill_no,current_period,pre_repay_amt,repayment_amt,DATE_FORMAT(settle_time,"%Y/%m/%d") as settle_time,pre_repay_date,dict_repay_plan_status,
DATE_FORMAT(CURRENT_DATE(),"%Y/%m/%d") as watchday
from 还款表
group by bill_no,current_period,pre_repay_amt,repayment_amt,settle_time,pre_repay_date,dict_repay_plan_status) as a
where DATEDIFF(watchday,pre_repay_date) >0
将FPD和DPD转化，有两种方式。
date['people_fpd1+']=date.apply(lambda x:'还没到期'if x.people_grance_date_diff<1 else '1+' if
x.fpd>=1 else '0',axis=1)
date['people_fpd7+'] =date.apply(lambda x:'还没到期'if x.people_grance_date_diff<7 else '7+' if
x.fpd>=7 else '0-6',axis=1)
date['people_fpd15+'] =date.apply(lambda x:'还没到期'if x.people_grance_date_diff<15 else '15+' if
x.fpd>=15 else '0-14',axis=1)
date['people_fpd30+'] =date.apply(lambda x:'还没到期'if x.people_grance_date_diff<30 else '30+' if
x.fpd>=30 else '0-29',axis=1)
date['dpd1+']=date.apply(lambda x:'还没到期'if x.max_grance_date_diff<1 else '1+' if
x.DPD>=1 else '0',axis=1)
date['dpd7+'] =date.apply(lambda x:'还没到期'if x.max_grance_date_diff<7 else '7+' if
x.DPD>=7 else '0-6',axis=1)
date['dpd15+'] =date.apply(lambda x:'还没到期'if x.max_grance_date_diff<15 else '15+' if
x.DPD>=15 else '0-14',axis=1)
date['dpd30+'] =date.apply(lambda x:'还没到期'if x.max_grance_date_diff<30 else '30+' if
x.DPD>=30 else '0-29',axis=1)
data['dpd1'] = data.apply(lambda x:1 if x['dpd']>=1 else 2 if x.min_date_diff<=0 else 0,axis=1)
data['dpd15'] = data.apply(lambda x:1 if x['dpd']>=15 else 2 if x.min_date_diff<=14 else 0,axis=1)
data['dpd30'] = data.apply(lambda x:1 if x['dpd']>=30 else 2 if x.min_date_diff<=29 else 0,axis=1)
data['dpd90'] = data.apply(lambda x:1 if x['dpd']>=90 else 2 if x.min_date_diff<=89 else 0,axis=1)
data['dpd1'] = data.apply(lambda x:1 if x['dpd']>=1 else 2 if x.min_date_diff<=0 else 0,axis=1)
data['dpd15'] = data.apply(lambda x:1 if x['dpd']>=15 else 2 if x.min_date_diff<=14 else 0,axis=1)
data['dpd30'] = data.apply(lambda x:1 if x['dpd']>=30 else 2 if x.min_date_diff<=29 else 0,axis=1)
data['dpd90'] = data.apply(lambda x:1 if x['dpd']>=90 else 2 if x.min_date_diff<=89 else 0,axis=1)
报表展示：主要思想是将所有结果放一张表
第一种最垃圾不记录了（每个变量单独做表）
第二种，导致行增加太多
a=data2.shape[1]+1
col=data2.columns[6:a]
tmp=pd.melt(data2,id_vars=['idCard','people_fpd1+','people_fpd7+','people_fpd15+','people_fpd30+','product_name_new'],value_vars=col,var_name="变量")
tmp
第三种
fpd1 = pd.DataFrame()
fpd7 = pd.DataFrame()
fpd30 = pd.DataFrame()
for i in col:
t1 = pd.pivot_table(data=fksx1,index=[i],columns='people_fpd1+',
values='idcard',aggfunc=f).reset_index()
t1.rename(columns={i:'区间'},inplace=True)
t1['变量']=i
t1['fpd1+_rate'] = t1['1+']/(t1['1+']+t1['0'])
fpd1 = fpd1.append(t1)