PostgreSQL_case when

描述

查询6月份借款用户，在7.15的d1，d2的逾期率，分新老用户

• 用户借款时间为6.01-6.31
• 根据逾期天数分为d1和d2，d1表示逾期大于1天
• brrank确定新用户和老用户，新用户brrank=1，老用户brrank>1
• psperdno = 1 表示第一期借款

-- d1 新用户（brrank=1）d2(修改latedays > 1)
select 
count(distinct custid) "总人数",
count(distinct case when latedays > 0 then custid end) "d1新用户逾期人数",
count(distinct case when latedays > 0 then custid end)*1.0/count(*) "d1新用户逾期率"
from 
transformdata.xsfinrank 
where brrank = 1 and psperdno = 1 and  ('2018-06-30'::date - givemoneydate) >= 0  and ('2018-06-01'::date - givemoneydate) <= 0;


-- d1 老用户（brrank>1）d2(修改latedays > 1)
select 
count(distinct custid) "总人数",
count(distinct case when latedays > 0 then custid end) "d1老用户逾期人数",
count(distinct case when latedays > 0 then custid end)*1.0/count(*) "d1老用户逾期率"
from 
transformdata.xsfinrank 
where brrank > 1 and psperdno = 1 and  ('2018-06-30'::date - givemoneydate) >= 0  and ('2018-06-01'::date - givemoneydate) <= 0;

通过case when合并

select 
case when brrank=1 then 1 when brrank > 1 then 2 end brrank,
count(distinct custid) "总人数",
count(distinct case when latedays > 0 then custid end) "d1逾期人数",
count(distinct case when latedays > 0 then custid end)*1.0/count(*) "d1逾期率",
count(distinct case when latedays > 1 then custid end) "d2逾期人数",
count(distinct case when latedays > 1 then custid end)*1.0/count(*) "d2逾期率"
from 
transformdata.xsfinrank 
where (givemoneydate between to_date('2018-06-01','YYYY-MM-DD') and to_date('2018-06-30','YYYY-MM-DD')) 
and psperdno = 1 --第一期
group by 1;

描述2

计算老用户逾期人数中 前期借款逾期 超过15天的人数

select 
count(distinct a.custid) "逾期超过15"
from 
transformdata.xsfinrank a
inner join
(
select 
custid,
brrank
from 
transformdata.xsfinrank 
where (givemoneydate between to_date('2018-06-01','YYYY-MM-DD') and to_date('2018-06-30','YYYY-MM-DD')) 
and psperdno = 1 and brrank >1 --第一期
)b
on a.custid = b.custid
where a.brrank<b.brrank and latedays > 15 

select 
a.custid,
max(a.latedays)
from 
transformdata.xsfinrank a
inner join
(
select 
custid,
brrank
from 
transformdata.xsfinrank 
where (givemoneydate between to_date('2018-06-01','YYYY-MM-DD') and to_date('2018-06-30','YYYY-MM-DD')) 
and psperdno = 1 and brrank >1 --第一期
)b
on a.custid = b.custid and a.brrank<b.brrank
group by 1 order by 2 desc;