MySQL实战演练——如何才能构建逾期用户画像？【数据可视化】_mysql 做用户画像

3 数据描述

这里是用到项目中提供的LCIS数据集，具体字段含义如下表所示。

4 提出问题

4.1 用户角度

• 失信用户画像：初始认证等级、年龄、性别、学历等维度的逾期占比分布情况；
• 失信用户行为分析：是否开淘宝店；是否有征信报告；是否完成本人认证；是否第一次借款；

4.2 业务角度

• 不良贷款率随记录时间变化趋势
• 不良贷款率与年龄、性别之间的相关性分析，以及各年龄段、性别的放贷比列；
• 不良贷款率与初始评级之间的相关性分析，以及初始评级的放贷比列；
• 不良贷款率与借款类型、利率、金额、期限之间的相关性分析，以及放贷比例

基于以上信息，我从用户画像以及不良贷款率两个业务指标出发，按照以下思路进行分析。

选择以下16个维度对数据进行分析：借款金额、借款期限、借款利率、初始评级、借款类型、是否首标、年龄、性别、手机认证、户口认证、视频认证、学历认证、征信认证、淘宝认证、标当前逾期天数、待还本金。

5 数据清洗

5.1 导入数据

首先将csv文件导入数据库当中，这里我用到的是Navicat。

5.2 字段重命名

ALTER TABLE lcis change ListingId 客户编号 char(10);
ALTER TABLE lcis change recorddate 记录日期 date;

将英文字段 ListiingId,recorddate 改为中文字段，方便后面sql查询。

5.3 缺失值、重复值处理

#查看重复值
SELECT 记录日期,count(\*),count(DISTINCT 客户编号),count(客户编号),count(借款金额),
count(借款期限),count(借款利率),count(初始评级),count(借款类型),count(是否首标),
count(年龄),count(性别),count(手机认证),count(户口认证),count(视频认证),count(学历认证),
count(征信认证),count(淘宝认证),count(标当前逾期天数),count(待还本金),count(记录日期)
FROM lcis
GROUP BY 记录日期
ORDER BY 记录日期;

将数据通过对记录时间特征进行分组，统计每个时间段的特征统计值，发现2016-09-30时间段的 ‘count(distinct)客户编号’ 比其余特征统计值少了106个数据，说明在该时间段存在106个重复值，可以对这106个重复值进行去重操作。

#去除重复值和缺失值
CREATE TABLE lcisnew as (SELECT DISTINCT \* from lcis);
#查看结果
SELECT 记录日期,count(\*),count(DISTINCT 客户编号),count(客户编号),count(借款金额),
count(借款期限),count(借款利率),count(初始评级),count(借款类型),count(是否首标),
count(年龄),count(性别),count(手机认证),count(户口认证),count(视频认证),count(学历认证),
count(征信认证),count(淘宝认证),count(标当前逾期天数),count(待还本金),count(记录日期)
FROM lcisnew
GROUP BY 记录日期
ORDER BY 记录日期;

执行上述sql语句，删除重复值和缺失值。

5.4 异常值处理

经过检查发现 户口认证、手机认证、记录时间有存在异常值。

DELETE FROM lcisnew WHERE 户口认证 not in ('未成功认证','成功认证') 
or 手机认证 not in ('未成功认证','成功认证') 
or 记录日期 not in ('2016-09-30','2016-10-31','2016-11-30','2016-12-31','2017-02-28')

执行上述语句删除异常值。

SELECT count(\*) FROM lcisnew

删除异常值之后可以得到 lcisnew 的样本数量为291230。

6 数据分析

6.1 用户维度

6.1.1 逾期用户画像分析用户的基本信息

包括初始认证等级、年龄、性别、学历分布情况.

6.1.1.1 初始评级逾期占比分布

SELECT 初始评级, 
	初始评级逾期数量, 
	初始评级总数,
	CONCAT(ROUND(初始评级逾期数量\*100/初始评级总数,0),'%') AS '逾期占比'
FROM (	
	SELECT
	初始评级,
	sum(CASE WHEN 标当前逾期天数>0 THEN 1 ELSE 0 END) as '初始评级逾期数量',
	sum(CASE WHEN 标当前逾期天数>0 OR 标当前逾期天数=0 THEN 1 ELSE 0 END) AS '初始评级总数'
	FROM lcisnew
	GROUP BY 初始评级
) AS a

从上图表中可以得出结论：

评级为E的逾期占比最大，其次是D、F、C，而AAA和AA级则基本上没有逾期状况发生。

6.1.1.2 年龄段逾期占比分布

SELECT a.年龄段,
	a.年龄段逾期人数,
	b.年龄段总人数,
	concat(round(a.年龄段逾期人数\*100/b.年龄段总人数,2),'%') AS '逾期占比'
FROM (
	SELECT 
		(case