账龄分析-基于SQL

信贷与风控

银行的信贷业务是指银行向个人或企业提供贷款和信用产品的业务，信贷业务是银行的核心业务之一。信贷业务通过提供资金支持，满足客户的各类资金需求；通过放款收回本金与利息、扣除成本后获得利润，是银行重要的资产业务和盈利手段。

银行的风控业务是指银行在信贷管理过程中通过一系列的风险管理措施和策略，识别、评估、控制和监控各类风险，对用户风险进行管理和规避，以确保银行的稳健经营和资金安全。

账龄分析

账龄分析来源

Vintage起源于葡萄酒行业，因为不同年份的日照、温度、湿度、降水、病虫害等因素会影响当年采摘的葡萄品质，进而导致酿造的葡萄酒的品质存在差异，但一般葡萄酒的品质在窖藏一定年份后会趋于稳定，这个年份被称为葡萄酒的成熟期。

下表是不同年份窖藏的葡萄酒在不同入窖年龄时对葡萄酒品质的评分。

基于上表绘制不同入窖年份葡萄酒在不同入窖年龄时的品质评分图。

附:绘图代码

import pandas as pd
import matplotlib.pyplot as plt

#导入数据
vintage_data=pd.read_excel("账龄分析.xlsx")
vintage_data.set_index("账龄",inplace = True)

plt.figure(figsize=(12, 10))
plt.plot(vintage_data)
plt.title("葡萄酒入窖年份及品质评分图")
plt.xlabel("葡萄酒入窖年龄")
plt.ylabel("葡萄酒品质评分")
plt.xticks(rotation=0)
plt.ylim(60, 100)
plt.grid(True, linestyle="--", color="gray", linewidth="0.5", axis="both")
plt.legend(labels=vintage_data.columns,bbox_to_anchor=(1.0, 1.0), loc='upper left')
plt.show()

从上图可以看出

• 不同入窖年份葡萄酒的品质都会随窖藏时间的增长而提高；
• 该种葡萄酒在入窖6年后品质趋于稳定，成熟期为6年；
• 2000年入窖的葡萄酒最终品质最好，2002年入窖的葡萄酒最终品质最差，可以对这两年影响葡萄的因素进行分析并针对性改善葡萄酒品质。

账龄分析在金融行业的应用

银行的风控有诸多方法，从数据角度来说可以实施一系列的风险数据分析，比如在信贷资产质量分析中常用的账龄分析。信贷中的账龄分析是一种用于评估贷款组合质量和风险的方法。它基于对不同贷款发放时间点的表现进行分析，以了解贷款组合的整体表现和质量。

与葡萄酒的品质分析方法类似，此处介绍账龄分析在银行客户贷款逾期行为方面的应用。

在账龄分析中需要把不同放款月份对齐到账龄上。如23年1月放款的在1月底是0账期，即MOB0，在2月底为MOB1，在3月底为MOB2；23年2月放款的在2月底是MOB0，在3月底为MOB1，在4月底为MOB2。所以不同放款月份的MOB1在自然月上可能指的是不同月份。

信贷基础指标：

• 账龄（Month Of Book , MOB）：贷款从放款开始的期数，期数一般以月份计，即资产放款月份。MOB0表示放款日至当月月底，MOB1表示放款后第一个完整的月份，MOB2表示放款后第二个完整的月份，以此类推。最大值取决于当前产品的周期，比如12期的贷款产品最大账龄为MOB12。
• 逾期期数（Bucket）：贷款逾期的月份数，逾期1个月记为M1，逾期2个月为M2，逾期3个月以上可以记作M3+。

在做账龄分析时，有以下几步：

• 产品与客群选择：首先要确定对哪种信贷产品进行分析，不同产品之间差异比较大，使用账龄分析可能会得到截然不同的结论，此处说的产品可以类比上述葡萄酒案例中选择的葡萄酒品种；客群的划分一般以时段为基准，此处说的客群可以类比上述葡萄酒案例中葡萄酒的入窖年份。

• 账龄的切分方式：账龄的切分方式有两种，一种是借款当月的月底到下月月底作为MOB1；另一种是借款当日到30天后，一般取第一种算法。此处说的账龄可以类比上述葡萄酒案例中葡萄酒的入窖年龄。

• 逾期率：逾期率可类比上述葡萄酒案例中葡萄酒的品质评价标准。

  其一，逾期指标：客户被判定为逾期的标准有多种方式，比如逾期时间在一个月内就算逾期，记为M1，此时对于每个时间维度的MOB1都可能不为0，比如逾期在90天以上判定为逾期，记为M4+。

  其二，逾期率：逾期率的计算有两种口径，第一种从客户角度计算，逾期率 = 逾期客户数 / 总放贷客户数；第二种从金额角度计算，逾期率 = 逾期剩余本金 / 总放贷本金；对于每种计算口径，逾期率的计算还分为曾经逾期与当前逾期两种观测方法：

  • 曾经逾期（ever）：截止到观察点，只要用户曾经发生过逾期（如M1、M2、M3等），不管观察点是否结清，都认为该笔借据处于逾期。因此，这种口径下的逾期率能保证账龄曲线单调不减；
  • 当前逾期（current）：用户在观察点当前是否处于逾期状态（如M1、M2、M3等）。如果借据在观察点上已经结清，则认为该借据正常未逾期。

下面以一份客户还款逾期情况数据为例，讲解使用SQL进行账龄分析的步骤。

数据介绍

样例数据下载-提取码: 4e5s

数据集 credit_record.csv 包含如下三个字段：

字段英文名	字段中文名	字段解释
ID	客户编号	客户编号＋月份为主键
MONTHS_BALANCE	还款月份	0表示当前月份，-1表示当前月份的前一个月，依次类推…每个ID对应的最小月份数表示放款月份
STATUS	还款状态	0:逾期1-29 天、1:逾期30-59 天、2:逾期60-89 天、3:逾期90-119 天、4:逾期120-149 天、5:逾期150天以上或坏账、 C: 当月已还清、X: 当月无借款

利用SQL进行账龄分析

查看数据情况

先来建表、导入数据并初步看下数据情况

--建表导入数据
drop table if exists credit_record;
create table credit_record(
	ID varchar(10)
   ,MONTHS_BALANCE int
   ,STATUS varchar(10)
);

--查看数据
select * from credit_record order by ID,MONTHS_BALANCE;

credit_record表记录了每个客户(ID)，每个还款月份(MONTHS_BALANCE)的还款情况(STATUS)。

其中还款月份字段以数字形式展现，0表示当前月份，-1表示当前月份的前一个月，我们将0定为 202308 月，则-1表示 202307 月，其他值对应的还款月份以此类推。其最小值所在的月份表示 放款月份 ，后面客群的划分也将以放款月为基准；其最大值表示客户的 最后还款月份 ；最大值与最小值之差表示客户的 还款观察期数 ；还款月份与放款月份之差表示 账龄 。

还款情况字段以码值形式展现，0:逾期1-29 天、1:逾期30-59 天、2:逾期60-89 天、3:逾期90-119 天、4:逾期120-149 天、5:逾期150天以上或坏账、 C: 当月已还清、X: 当月无借款。

基于此字段可选择不同的逾期指标来计算逾期率，比如要以客户逾期60-89天作为逾期的标准，则可令 STATUS=‘2’ 。

确定账龄分析步骤标准

针对账龄分析的几个步骤，本次账龄分析约定如下：

• 产品与客群选择：产品的选择取credit_record表中 还款观察期数>20 的客户数据；客群以客户 放款月份 来划分，此次分析取所有放款月份。

• 账龄的切分方式：账龄的切分方式以借款当月的月底到下月月底作为MOB1，比如在202301月放款的客户，以202302作为MOB1。

• 逾期率：其一，逾期指标：此次以客户逾期60-89天作为逾期的标准，记为M2。

  其二，逾期率计算口径：从客户角度计算，逾期率 = 逾期客户数 / 总放贷客户数；逾期率观测口径：

  • 曾经逾期（ever）：截止到观察点，只要用户曾经发生过逾期（如M1、M2、M3等），不管观察点是否结清，都认为该笔借据处于逾期。因此，这种口径下的逾期率能保证账龄曲线单调不减；

基于上述口径，我们使用SQL来加工账龄数据并绘图展示。

数据加工

加工账龄分析所需基础指标

--1.加工基础指标
drop table if exists credit_record01;
create table credit_record01 as 
select id --客户编号
	  ,months_balance --还款月份编码(0表示当前月份，-1表示当前月份的前一个月，依次类推...每个ID对应的最小月份数表示放款月份)
	  ,year_month --还款月份
	  ,status --还款状态(0:逾期1-29 天、1:逾期30-59 天、2:逾期60-89 天、3:逾期90-119 天、4:逾期120-149 天、5:逾期150天以上或坏账、 C: 当月已还清、X:  当月无借款)
	  ,mob --账龄
	  ,fk_m --放款月份编码
	  ,fk_month --放款月份
	  ,end_m --最后还款月份编码
	  ,end_month --最后还款月份
	  ,gcq --还款观察期数
	  ,case when status='2' then 1 else 0 end as status_60_89 --逾期60-89天的，1: 逾期 0 未逾期
from (
	select id --客户编号
		  ,months_balance --还款月份编码(0表示当前月份，-1表示当前月份的前一个月，依次类推...每个ID对应的最小月份数表示放款月份)
		  ,ADD_MONTHS(to_date('20230801','yyyymmdd'),months_balance) as year_month --还款月份
		  ,status --还款状态(0:逾期1-29 天、1:逾期30-59 天、2:逾期60-89 天、3:逾期90-119 天、4:逾期120-149 天、5:逾期150天以上或坏账、 C: 当月已还清、X:  当月无借款)
		  ,months_balance-min(months_balance) over(partition by id) as mob --账龄
		  ,min(months_balance) over(partition by id) as fk_m --放款月份编码
		  ,ADD_MONTHS(to_date('20230801','yyyymmdd'),min(months_balance) over(partition by id)) as fk_month --放款月份
		  ,max(months_balance) over(partition by id) as end_m --最后还款月份编码
		  ,ADD_MONTHS(to_date('20230801','yyyymmdd'),max(months_balance) over(partition by id)) as end_month --最后还款月份
		  ,max(months_balance) over(partition by id)-min(months_balance) over(partition by id) as gcq --还款观察期数
	from credit_record
) a 
where gcq>20
;

注：postgre数据库没有ADD_MONTHS函数，需要自己先创建这个函数，函数创建代码如下：

CREATE OR REPLACE FUNCTION ADD_MONTHS(var_dte DATE,cnt INT) RETURNS setof DATE AS
$$
DECLARE
qry text;
BEGIN
qry = format( 'select (''%s''::date + interval ''%s'')::date',var_dte,cnt||' month') ;
RETURN QUERY
  EXECUTE qry;
END
$$
LANGUAGE plpgsql

加工放款月份与账龄对照表以及每个放款月份的客户数

drop table if exists credit_record02;
create table credit_record02 as 
select a.fk_m,a.fk_month,a.mob,b.fk_num as sta_sum --每个月发放的用户数
from (
	--放款月份与账龄对照
	select fk_m,fk_month,mob 
	from credit_record01
	group by fk_m,fk_month,mob
) a 
left join (
	--每月发放的客户数（credit_record1表会删除一些客户，因此基于原始credit_record表计算）
	select fk_m,count(distinct id) as fk_num --每个月发放的用户数
	from (
		select distinct id
			  ,min(months_balance) over(partition by id) as fk_m --放款月份
		from credit_record
	) a 
	group by fk_m
) b 
on a.fk_m=b.fk_m
;

计算逾期率

drop table if exists credit_record03;
create table credit_record03 as 
select to_char(fk_month,'yyyymm') as fk_m
	  ,mob
	  ,yq_count --逾期人数
	  ,sta_sum --放款月份客户数
	  ,round(yq_count::numeric/sta_sum::numeric,6) as yq_rate --逾期率
from (
	select fk_month,mob
		  ,count(distinct id) as yq_count --逾期人数
		  ,max(sta_sum) as sta_sum --每个月发放的用户数
	from (
		select a.fk_m,a.fk_month,a.mob,b.mob as mob2,b.id,a.sta_sum --每个月发放的用户数
		from credit_record02 a 
		left join (
			select id,fk_m,mob,status_60_89
			from credit_record01
			where status_60_89=1
		) b 
		on a.fk_m=b.fk_m and b.mob<=a.mob
	) c
	group by fk_month,mob
) d
order by fk_m,mob
;

注：上面计算逾期率的方法略微繁琐，其实可以使用下面的开窗函数方式来计算，此处之所以没用这种方法，是因为postgre数据库不支持在开窗函数里面使用distinct语法，但在其他数据库中则可以。

drop table if exists credit_record03;
create table credit_record03 as 
select a.fk_m
	  ,a.mob
	  ,b.yq_count --逾期人数
	  ,a.sta_sum --放款月份客户数
	  ,round(yq_count::numeric/sta_sum::numeric,6) as yq_rate --逾期率
from credit_record02 a 
inner join (
	select distinct fk_m,mob,yq_count
	from (
		select id,fk_m,mob,status_60_89
			  ,count(distinct case when status_60_89=1 then id else null end) over(partition by fk_month order by mob) as due_count --逾期人数(累加)
		from credit_record01
	) a
) b
on a.fk_m=b.fk_m and a.mob=b.mob
order by a.fk_m,a.mob
;

至此，已经加工完账龄分析所需数据，我们把表credit_record03的数据导出到csv，然后利用python画出账龄分析图：

import pandas as pd
import matplotlib.pyplot as plt

#导入账龄数据
vintage_data=pd.read_csv("账龄分析.csv")
vintage_data = vintage_data.pivot(index = 'mob',
                             columns = 'fk_m',
                             values = 'yq_rate')
vintage_data.head(10)

plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']
plt.figure()
plt.plot(vintage_data.iloc[0:,0:12]) #此处可以自行设置要取的放款月份数量
plt.title("逾期60天以上客户账龄分析")
plt.xlabel("账龄")
plt.ylabel("逾期率")
plt.xticks(rotation=0)
plt.grid(True, linestyle="--", color="gray", linewidth="0.5", axis="both")
plt.legend(labels=vintage_data.columns,bbox_to_anchor=(1.0, 1.0), loc='upper left')
plt.show()