风控数仓简介

这篇文章将介绍信贷风控中的数仓建设，主要包括几张常用的中间表以及如何基于数仓进行指标的分析。文章内容基于本人工作经验和理解，如有不足之处烦请指正。

目录

1. 成功件表
2. mob表及应用
3. npd表及应用
4. 不良率表及应用

一、成功件表

记录成功放款的客户的办单信息，与其对应的还有一张申请信息表。成功件信息表主要记录用户的办单信息，主要包括用户id、产品线、申请时间、放款金额、期数、每期还款额、每月应还款日、是否提前还款/结清、当前状态、当前逾期天数、还款历史等。如果是申请表的话，则包括客户的基本信息、审核状态、办单信息等，记录的是全量申请用户的相关信息。

还款历史字段，一般叫repayment_history或者overdue_string，是将用户的所有期的还款表现集成一个字段。比如一笔6期的贷款，目前表现3期，repayment_history为"F12"，对应的还款表现为第一期正常还款，二、三期逾期。基于这个字段可以了解客户每一期的还款表现，因此可以衍生字段进行分析：比如是否首逾、首逾发生期数、首逾是否超过30天等。

二、mob表及应用

记录每个客户每一期的还款信息，和还款计划表有点类似。主要的字段如下：

比如一个分6期还款的订单，已经表现4期，还款历史为"1F11"，在mob表中就有4条记录，账期以及各期状态如下：

本期较上期状态有4种情况，表示客户当前这一期的账单相较于上一期的变化。

应用1：基于mob表计算Vintage

以M1+Vintage为例，SQL代码如下：

select date_format(open_date,'%Y-%m') as open_month,sum(principal) as principalsum,0 as mob0,

sum(case when mob=1 and current_cycle_status>1 then unrecived_principal end) as 'mob1_m1+',

sum(case when mob=2 and current_cycle_status>1 then unrecived_principal end) as 'mob2_m1+',

sum(case when mob=3 and current_cycle_status>1 then unrecived_principal end) as 'mob3_m1+',

sum(case when mob=4 and current_cycle_status>1 then unrecived_principal end) as 'mob4_m1+'

from `mob表`

group by date_format(open_date,'%Y-%m')

根据放款月分组对各mob阶段M1+案件的未还本金求和，就得到各资产包每个账龄段的M1+金额，即可得到M1+Vintage的具体数值。实际工作中还可以根据省份、地市、策略进行拆分。

应用2：基于mob表计算各资产包迁徙率

1.首先需要将mob表作转置，类似数据透视。SQL代码如下：

create table overdue_mob_detail as(

select app_id,open_date,

max(case when mob=0 then current_cycle_status end) as `mob0`,

max(case when mob=1 then current_cycle_status end) as `mob1`,

max(case when mob=2 then current_cycle_status end)as `mob2`,

max(case when mob=3 then current_cycle_status end) as `mob3`,

max(case when mob=4 then current_cycle_status end)as `mob4`,

max(case when mob=5 then current_cycle_status end) as `mob5`,

max(case when mob=6 then current_cycle_status end)as `mob6`,

from `mob表`

group by app_id,open_date)

即将每个用户的repayment_history字段拆开，每一期的状态对应一列；

2.基于转置之后的表进行各资产包各期回收率、迁徙率的计算。以各资产包各期M1的回收率为例，SQL代码如下：

select substr(open_date,1,7) as open_date,

sum(case when mob1=1 and mob2<=1 then 1 else 0 end)/sum(case when mob1=1 then 1 else 0 end) as `period1`,

sum(case when mob2=1 and mob3<=1 then 1 else 0 end)/sum(case when mob2=1 then 1 else 0 end) as `period2`,

sum(case when mob3=1 and mob4<=1 then 1 else 0 end)/sum(case when mob3=1 then 1 else 0 end) as `period3`,

sum(case when mob4=1 and mob5<=1 then 1 else 0 end)/sum(case when mob4=1 then 1 else 0 end) as `period4`,

sum(case when mob5=1 and mob6<=1 then 1 else 0 end)/sum(case when mob5=1 then 1 else 0 end) as `period5`,

sum(case when mob6=1 and mob7<=1 then 1 else 0 end)/sum(case when mob6=1 then 1 else 0 end) as `period6`,

from overdue_mob_detail

group substr(open_date,1,7）

可以看到首期的回收率较高，第2、3期的回收率较低。各资产包各期迁徙率计算同理。

三、npd表及应用

记录每个订单每一期的第n(一般有3、7、15、30)天的逾期情况。具体字段有：

以p2_1day为例，有三种状态分别为1(逾期)、0(未逾期)、-1(未表现)。npd表有互斥和非互斥两种，假设某客户第1期逾期后回收第2期又发生逾期，在互斥的npd表中只有p1_1day为1，在非互斥npd表中p1_1day和p2_1day都为1。

应用1：基于npd表计算首/次逾指标

计算各资产包的FPD1%、FPD7%、SPD1%、SPD7%指标的SQL代码：

select substr(open_date,1,7) as open_date,

sum(case when p1_1day=1 then 1 else 0 end)/sum(case when p1_1day!=-1 then 1 else 0 end) as 'fpd1%',

sum(case when p1_7day=1 then 1 else 0 end)/sum(case when p1_7day!=-1 then 1 else 0 end) as 'fpd7%',

sum(case when p2_1day=1 then 1 else 0 end)/sum(case when p2_1day!=-1 then 1 else 0 end) as 'spd1%',

sum(case when p2_7day=1 then 1 else 0 end)/sum(case when p2_7day!=-1 then 1 else 0 end) as 'spd7%',

from `NPD表`

group by substr(open_date,1,7)

应用2：基于npd表计算各资产包首逾回收率

计算各资产包发生首逾之后的回收率，SQL代码如下：

select substr(open_date,1,7)open_date,

sum(case when p1_1day=1 and p1_30day=0 then 1 else 0 end)/sum(p1_1day) as `fpd1-30`,

sum(case when p2_1day=1 and p2_30day=0 then 1 else 0 end)/sum(p2_1day) as `2pd1-30`,

sum(case when p3_1day=1 and p3_30day=0 then 1 else 0 end)/sum(p3_1day) as `3pd1-30`,

sum(case when p4_1day=1 and p4_30day=0 then 1 else 0 end)/sum(p4_1day) as `4pd1-30`,

sum(case when p5_1day=1 and p5_30day=0 then 1 else 0 end)/sum(p5_1day) as `5pd1-30`,

sum(case when p6_1day=1 and p6_30day=0 then 1 else 0 end)/sum(p6_1day) as `6pd1-30`

from `NPD表`

group by substr(open_date,1,7)

首逾发生期数越往后，相应的回收率越高，因为通过前面几期的正常还款用户已养成还款习惯。

应用3：基于npd表与mob表计算各资产包首逾率

将mob表与npd表连接，可以计算各资产包各期逾期中首逾的占比情况，SQL代码如下：

select substr(a.open_date,1,7) as open_date,

sum(case when p1_1day=1 then 1 else 0 end)/sum(case when mob1=1 then 1 else 0 end) as 1pd1,

sum(case when p2_1day=1 then 1 else 0 end)/sum(case when mob2=1 then 1 else 0 end) as 2pd1,

sum(case when p3_1day=1 then 1 else 0 end)/sum(case when mob3=1 then 1 else 0 end) as 3pd1,

sum(case when p4_1day=1 then 1 else 0 end)/sum(case when mob4=1 then 1 else 0 end) as 4pd1,

sum(case when p5_1day=1 then 1 else 0 end)/sum(case when mob5=1 then 1 else 0 end) as 5pd1,

sum(case when p6_1day=1 then 1 else 0 end)/sum(case when mob6=1 then 1 else 0 end) as 6pd1,

from `NPD表`a

inner join overdue_mob_detail b

on a.app_id=b.app_id

group by substr(a.open_date,1,7)

可以看到，到第3期时发生逾期中有30%左右是首次发生逾期，越往后首逾占比越低，即越往后逾期中反复逾期的用户居多。

四、不良率表及应用

不良率表记录每日各逾期阶段的余额、不良额、应收账款等，具体字段如下：

可以根据这张表计算各阶段的逾期率、不良率、坏账率，了解整体的资产分布状况。这种方法算出来的指标是资产占比口径的逾期率、不良率，会受到新增投放量的影响，实际的逾期率、不良率也可以看vintag口径下的各项指标，更具参考意义。

【作者】：Labryant

【原创公众号】：风控猎人

【简介】：某创业公司策略分析师，积极上进，努力提升。乾坤未定，你我都是黑马。

【转载说明】：转载请说明出处，谢谢合作！~