【案例1】征信指标加工

案例介绍

风控建模常用到征信指标，本笔记在已落入数据库的结构化数据（标签）的基础上，使用MYSQL实现个人征信指标加工。

目标

每份征信报告生成一行指标。

需求分析

一份征信报告，有的标签出现一次，有的出现多次，后者通过聚合才能降维成一行指标；有的标签是人的属性，有的标签是账户的属性。账户可以有多个，所以也需要做聚合，另外账户有很多分类，所以聚合前往往要先筛选。
可见，可以从“直属对象”和“是否唯一”两个维度将标签分为以下四类：

标签分类	标签唯一	标签不唯一
人的属性	个人唯一标签（直接提取）	个人非唯一标签（聚合）
账户的属性	账户唯一标签（先筛选后聚合）	账户非唯一标签（先多条件筛选后聚合）

• 个人唯一标签，如性别、学历等，与征信报告的数量关系是1:1；
• 个人非唯一标签，如手机号变更日期等，与征信报告的数量关系是n:1；
• 账户唯一标签，如授信金额、余额、开立日期等，与账户的数量关系是1:1，账户与征信报告的数量关系是n:1；
• 账户非唯一标签，如近60个月的还款状态和逾期金额，与账户的数量关系是n:1。

实现过程

加工上述四类标签，并拼接为一张表格，创建视图以备复用。

需求1、加工个人唯一标签

性别、学历

对于一个人来说，性别、学历是唯一的，直接提取相应字段即可。

select 
	rpt_no , 		-- 征信报告编号
	pb01ad01,		-- 性别
	pb01ad02		-- 学历
from ind_str_iden_info

需求2、个人非唯一标签相关指标加工

最近一次手机号变更日期
最近一次手机号变更日期距报告日期天数
最近一次手机号变更日期距报告日期月份数

对于一个人来说，手机号、手机号变更日期可能有多个，所以上述指标需要以人（征信报告编号）为对象做聚合操作；另外，相距天数、月份数相关指标需要先求日期差再做聚合。

select 
	rpt_no,
	max(pb01br01),									-- 最近一次手机号变更日期
	min(timestampdiff(day,pb01br01,pa01ar01)),		-- 最近一次手机号变更日期距报告日期天数
	min(timestampdiff(month,pb01br01,pa01ar01))		-- 最近一次手机号变更日期距报告日期月份数
from ind_str_phonenum_info left join ind_str_rpthead_inf using(rpt_no) -- 关联报告日期所在表格
group by rpt_no

需求3、加工账户唯一标签

贷记卡账户授信金额合计
贷记卡账户余额合计
贷记卡账户最早开卡日期
贷记卡账户最早开卡日期距报告日期天数
贷记卡账户最早开卡日期距报告日期月份数

对于一个人来说，账户可能有多个，所以上述指标也需要做聚合；另外，贷记卡仅是账户类型中的一种，所以聚合前首先要按照“账户类型为贷记卡”这一条件进行筛选。

select 
	t1.rpt_no ,
	sum(case when pd01ad01='r2' then pd01aj02 else 0 end) as pd01aj02_r2_sum,									-- 贷记卡账户授信金额合计
	sum(case when pd01ad01='r2' then pd01bj01 else 0 end) as pd01bj01_r2_sum,									-- 贷记卡账户余额合计
	min(case when pd01ad01='r2' then pd01ar01 else null end) as pd01ar01_r2_min,								-- 贷记卡账户最早开户日期
	max(case when pd01ad01='r2' then timestampdiff(day,pd01ar01,pa01ar01) else null end) as pd01ar01_diffd_max,	-- 最早开立日期距报告日期天数
	max(case when pd01ad01='r2' then timestampdiff