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

select pay_ability,
concat(round(sum(flg)/sum(rs)*100,1),'%')as overdue_ratio 
#MySQL的某些版本中，如果启用了 PIPES_AS_CONCAT SQL模式，|| 将被视为OR运算符导致结果错误，最好用concat拼接
from
(select
t2.pay_ability,
count(*)over(partition by t1.customer_id,t2.pay_ability)rs, #统计每个还款等级的总人数
count(case when t1.overdue_days is not null then 1 end)over(partition by t1.customer_id,t2.pay_ability) flg #按客户和还款等级分组统计逾期的人数
from loan_tb t1
left join customer_tb t2
on        t1.customer_id=t2.customer_id
)t
group by pay_ability
order by overdue_ratio desc;