题解 | #分析客户逾期情况#

select pay_ability,concat(round(100*(over_num/(over_num+not_over_num)),1),"%") as "overdue_ratio"
from(

select mid_t1.pay_ability,over_num,not_over_num
from(
select pay_ability,coalesce(count(case when over_mark=1 then 1 end), 0) as "over_num"
from(
select pay_ability, if(overdue_days is not null, 1,0) as "over_mark",
if(overdue_days is null, 1,0) as "not_over_mark"
from loan_tb lt left join customer_tb ct
on lt.customer_id = ct.customer_id
)t1
group by pay_ability
)mid_t1 join (

select pay_ability,coalesce(count(case when over_mark=0 then 1 end), 0) as "not_over_num"
from(
select pay_ability, if(overdue_days is not null, 1,0) as "over_mark",
if(overdue_days is null, 1,0) as "not_over_mark"
from loan_tb lt join customer_tb ct
on lt.customer_id = ct.customer_id
)t1
group by pay_ability
) mid_t2 
on mid_t1.pay_ability = mid_t2.pay_ability
)final_t
order by overdue_ratio desc