PostgreSQL 计算逾期率

描述

计算下ylhx4.cxh_xinyanfeature表（小鲨中部分用新颜的数据）中的1999个用户，首次借款第一期、第二期的d0和d15的逾期率

• 还款信息在transformdata.xsfinrak表（小鲨用户）中
• psperdno：还款的期数
• latedays：逾期天数
• psduedt:截至还款日期

select 
psperdno "期数",
count(case when psperdno = 1 then (case when date(datadate)-psduedt > 0 then a.custid end) else (case when date(datadate)-psduedt > 15 then a.custid end) end) "总人数",
count(case when latedays > 0 and date(datadate)-psduedt > 0 then a.custid end) "d0逾期人数",
count(case when latedays > 0 and date(datadate)-psduedt > 0 then a.custid end)*1.0/count(case when psperdno = 1 then (case when date(datadate)-psduedt > 0 then a.custid end) else (case when date(datadate)-psduedt > 15 then a.custid end) end) "d0逾期率",
count(case when latedays > 15 and date(datadate)-psduedt > 15 then a.custid end) "d15逾期人数",
count(case when latedays > 15 and date(datadate)-psduedt > 15 then a.custid end)*1.0/count(case when psperdno = 1 then (case when date(datadate)-psduedt > 0 then a.custid end) else (case when date(datadate)-psduedt > 15 then a.custid end) end) "d15逾期率"
from 
transformdata.xsfinrank a
join
ylhx4.cxh_xinyanfeature b 
on a.custid=b.custid where brrank = 1  and psperdno in (1,2) group by 1;

方式1

改善后

-- brrank:表示第几次借款
-- psduedt:截至还款日期
-- psperdno：还款的期数
select
count(distinct a.custid ) "总人数",
count(distinct case when latedays > 0 then a.custid end) "d0逾期人数",
count(distinct case when latedays > 0 then a.custid end)*1.0/count(distinct a.custid) "d0逾期率"
from 
transformdata.xsfinrank a
join
ylhx4.cxh_xinyanfeature b
on a.custid = b.custid and psperdno = 1 and date(datadate)- psduedt > 0 

select
count(distinct a.custid ) "总人数",
count(distinct case when latedays > 15 then  a.custid end) "d15逾期人数",
count(distinct case when latedays > 15 then  a.custid end)*1.0/count(distinct a.custid) "d15逾期率"
from 
transformdata.xsfinrank a
join
ylhx4.cxh_xinyanfeature b
on a.custid = b.custid and psperdno = 1 and date(datadate)- psduedt > 15 

-- 第二期将1换成2即可

改善前

select
table1.first_0_overdue_people,
table2.first_15_overdue_people,
table3.second_0_overdue_people,
table4.second_15_overdue_people,
table5.all_people_0,
table6.all_people_15,
table1.first_0_overdue_people*1.0/table5.all_people_0 as first_0_overdue_rate,
table3.second_0_overdue_people*1.0/table6.all_people_15 as second_0_overdue_rate,
table2.first_15_overdue_people*1.0/table5.all_people_0 as first_15_overdue_rate,
table4.second_15_overdue_people*1.0/table6.all_people_15 as second_15_overdue_rate
from
-- 第一期逾期大于0
(
select 
count(t.custid) as first_0_overdue_people
from
(
select 
t1.custid,
max(t1.latedays) as maxlatedays
from  
transformdata.xsfinrank as t1
inner join
ylhx4.cxh_xinyanfeature as t2 
on 
t1.custid =  t2.custid 
where
date(t1.datadate) - t1.psduedt > 0 --表现期
and
t1.psperdno = 1 --期数
group by t1.custid
)t
where t.maxlatedays > 0
)table1,
-- 第一期逾期大于15
(
select 
count(t.custid) as first_15_overdue_people
from
(
select 
t1.custid,
max(t1.latedays) as maxlatedays
from  
transformdata.xsfinrank as t1
inner join
ylhx4.cxh_xinyanfeature as t2 
on 
t1.custid =  t2.custid 
where
date(t1.datadate) - t1.psduedt > 15 --表现期
and
t1.psperdno = 1 --期数
group by t1.custid
)t
where t.maxlatedays > 15
)table2,
-- 第二期逾期大于0
(
select 
count(t.custid) as second_0_overdue_people
from
(
select 
t1.custid,
max(t1.latedays) as maxlatedays
from  
transformdata.xsfinrank as t1
inner join
ylhx4.cxh_xinyanfeature as t2 
on 
t1.custid =  t2.custid 
where
date(t1.datadate) - t1.psduedt > 0 --表现期
and
t1.psperdno = 2 --期数
group by t1.custid
)t
where t.maxlatedays > 0
)table3,
-- 第二期逾期大于15
(
select 
count(t.custid) as second_15_overdue_people
from
(
select 
t1.custid,
max(t1.latedays) as maxlatedays
from  
transformdata.xsfinrank as t1
inner join
ylhx4.cxh_xinyanfeature as t2 
on 
t1.custid =  t2.custid 
where
date(t1.datadate) - t1.psduedt > 15 --表现期
and
t1.psperdno = 2 --期数
group by t1.custid
)t
where t.maxlatedays > 15
)table4,
-- 表现期(>0)总人数
(
select 
count(*) as all_people_0
from
(
select t1.custid from 
transformdata.xsfinrank as t1
inner join
ylhx4.cxh_xinyanfeature as t2 
on t1.custid = t2.custid
where
date(t1.datadate) - t1.psduedt > 0 --表现期
group by t1.custid
)t
)table5,
-- 表现期(>15)总人数
(
select 
count(*) as all_people_15
from
(
select t1.custid from 
transformdata.xsfinrank as t1
inner join
ylhx4.cxh_xinyanfeature as t2 
on t1.custid = t2.custid
where
date(t1.datadate) - t1.psduedt > 15 --表现期
group by t1.custid
)t
)table6

方式2

第一期

select 
count(*) "总人数",
count(case when latedays > 15 then a.custid end) "d15逾期人数",
count(case when latedays > 15 then a.custid end)*1.0/count(*) "d15逾期率"
from 
transformdata.xsfinrank a
join
ylhx4.cxh_xinyanfeature b 
on a.custid=b.custid where brrank = 1 and psperdno = 1 and date(datadate)-psduedt > 15;

select 
count(*) "总人数",
count(case when latedays > 0 then a.custid end) "d0逾期人数",
count(case when latedays > 0 then a.custid end)*1.0/count(*) "d0逾期率"
from 
transformdata.xsfinrank a
join
ylhx4.cxh_xinyanfeature b 
on a.custid=b.custid where brrank = 1 and psperdno = 1 and date(datadate)-psduedt > 0;

第二期

-- brrank 确保了custid的唯一
select 
count(*) "总人数",
count(case when latedays > 15 then a.custid end) "d15逾期人数",
count(case when latedays > 15 then a.custid end)*1.0/count(*) "d15逾期率"
from 
transformdata.xsfinrank a
join
ylhx4.cxh_xinyanfeature b 
on a.custid=b.custid where brrank = 1 and psperdno = 2 and date(datadate)-psduedt > 15;

select 
count(*) "总人数",
count(case when latedays > 0 then a.custid end) "d0逾期人数",
count(case when latedays > 0 then a.custid end)*1.0/count(*) "d0逾期率"
from 
transformdata.xsfinrank a
join
ylhx4.cxh_xinyanfeature b 
on a.custid=b.custid where brrank = 1 and psperdno = 2 and date(datadate)-psduedt > 0;