【SQLPlanet】基于迁徙率等指标浅析拍拍贷逾期数据（未完待续）

1、背景介绍

拍拍贷是一家金融科技公司，2007年成立于上海，并在2017年11月10日成功于美国纽交所上市。根据官方消息，截至2018年9月30日，拍拍贷累计成交额已突破1300亿，15-29天及30-59天的逾期率分别为0.83%和1.21%，自2015年，拍拍贷推出魔镜系统用于对每一笔借款进行风险评估，通过对借款人资质审核并预测未来逾期概率，对相应风险进行定价，基于大数法则保证整体的可观收益。

本文将从整体业务及通过迁徙率等指标进行分析，从数据中走进拍拍贷，了解拍拍贷平台在业务中出现过的问题及采取的信贷策略。

2、数据处理

2.1数据来源

数据来自拍拍贷真实业务数据（点击查看）从2015-01-01到2017-01-30的所有信用标的10%sample样本。

LCIS表中标的特征表共有37个字段，包括借款人ID，借款金额，期限，利率，年龄等，详见下图。Listingid为主键。其中历史信用记录相关字段均是拍拍贷内部的记录，即只有在拍拍贷有过历史借款的用户才有这部分记录，首次借款用户历史信用记录各项均默认为0。

2.2数据清洗

1. 将LCSI表中的数据导入navicat premium。
2. 更改原数据中ListingId和recorddate两个字段的列名及数据格式。

ALTER TABLE lcis CHANGE ListingId 用户编号 VARCHAR(10);

ALTER TABLE lcis CHANGE recorddate 记录日期 VARCHAR (10);

3. 数据重复值和异常值检查。检查发现记录日期是2016/9/30的数据有重复；另外，除5个记录日期外，存在非标准日期数据；后续均需删除。通过和前文列示的数据字典进行对比，排查其他字段的异常值、空值。（仅举例部分字段）

SELECT 记录日期,COUNT(*) AS 记录数量, COUNT(DISTINCT 用户编号) AS 不重复记录
FROM lcis
GROUP BY 记录日期
ORDER BY COUNT(*) DESC;

4. 为后续数据处理做准备，创建一张新表。添加逾期量化状态列和逾期账期列。

CREATE TABLE lcis_new AS SELECT DISTINCT
	lcis.*, 
	( 
	CASE WHEN 标当前状态 = '逾期中' THEN 1 ELSE 0 END
	) AS 逾期量化状态,
	(
	CASE WHEN 标当前状态 = '逾期中' THEN
	CONCAT('M',FLOOR(标当前逾期天数 / 30) + 1)
	ELSE 'C' END
	) AS 逾期账期
FROM
	lcis;

5. 在新表中剔除异常值和重复值

DELETE FROM lcis_new
WHERE (手机认证 NOT IN ('成功认证','未成功认证'))
  OR (户口认证 NOT IN ('成功认证','未成功认证'))
  OR (记录日期 NOT IN ('2016/9/30','2016/10/31','2016/11/30','2016/12/31','2017/2/28'))
  OR (标当前状态 NOT IN ('正常还款中','逾期中','已还清'));

6. 检查新表重复数据情况

SELECT 记录日期,COUNT(*) AS 记录数量, COUNT(DISTINCT 用户编号) AS 不重复记录 
FROM lcis_new
GROUP BY 记录日期