拍拍贷用户逾期数据分析sql

2020-07-17 本文已影响0人 a_big_cat

0 分析逾期情况

1 数据清洗

1.1 查看所有字段

SHOW FULL COLUMNS 
FROM
    lcis0;

客户编号    int     YES             select,insert,update,references 
借款金额    decimal(10,0)       YES             select,insert,update,references 
借款期限    int     YES             select,insert,update,references 
借款利率    float       YES             select,insert,update,references 
借款成功日期  date        YES             select,insert,update,references 
初始评级    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
借款类型    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
是否首标    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
年龄  int     YES             select,insert,update,references 
性别  varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
手机认证    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
户口认证    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
视频认证    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
学历认证    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
征信认证    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
淘宝认证    varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
历史成功借款次数    int     YES             select,insert,update,references 
历史成功借款金额    decimal(10,0)       YES             select,insert,update,references 
总待还本金   decimal(10,0)       YES             select,insert,update,references 
历史正常还款期数    int     YES             select,insert,update,references 
历史逾期还款期数    int     YES             select,insert,update,references 
我的投资金额  decimal(10,0)       YES             select,insert,update,references 
当前到期期数  int     YES             select,insert,update,references 
当前还款期数  int     YES             select,insert,update,references 
已还本金    decimal(10,0)       YES             select,insert,update,references 
已还利息    decimal(10,0)       YES             select,insert,update,references 
待还本金    decimal(10,0)       YES             select,insert,update,references 
待还利息    decimal(10,0)       YES             select,insert,update,references 
标当前逾期天数 int     YES             select,insert,update,references 
标当前状态   varchar(255)    utf8mb4_0900_ai_ci  YES             select,insert,update,references 
上次还款日期  date        YES             select,insert,update,references 
上次还款本金  decimal(10,0)       YES             select,insert,update,references 
上次还款利息  decimal(10,0)       YES             select,insert,update,references 
下次计划还款日期    date        YES             select,insert,update,references 
下次计划还款本金    decimal(10,0)       YES             select,insert,update,references 
下次计划还款利息    decimal(10,0)       YES             select,insert,update,references 
记录日期    date        YES             select,insert,update,references

1.2 查看年龄分布

SELECT
    年龄,
    count( * )  AS 客户数
FROM
    lcis0
GROUP BY
    年龄 
ORDER BY
    年龄

1.2.png

总结:
1.客户年龄分布呈正偏态分布,客户群体偏年轻化,其中20-35为消费主力军
2.后续可将年龄分组为[16,22,28,34,40,46,52,58],进一步观察

1.4 依次对各字段进行查看,了解其分布情况(略)

2 数据处理

2.1 创建新视图,

CREATE VIEW news AS 
SELECT
    客户编号,
    性别,
    借款金额,
    借款类型,
    初始评级,
IF( 标当前状态 = '逾期中', 1, 0 ) AS 是否逾期,

(CASE
        WHEN 借款利率 <= 4 THEN
        '[0-4]' 
        WHEN 借款利率 <= 8 THEN
        '[4-8]' 
        WHEN 借款利率 <= 12 THEN
        '[8-12]' 
        WHEN 借款利率 <= 16 THEN
        '[12-16]' 
        WHEN 借款利率 <= 20 THEN
        '[16-20]' 
        WHEN 借款利率 <= 24 THEN
        '[20-24]' ELSE '[24-30]' 
        END ) AS 借款利率区间,
(CASE
                
        WHEN 借款期限 <= 4 THEN
                '[0-4]' 
        WHEN 借款期限 <= 8 THEN
                '[4-8]' 
        WHEN 借款期限 <= 12 THEN
                '[8-12]' 
        WHEN 借款期限 <= 16 THEN
                '[12-16]' 
        WHEN 借款期限 <= 20 THEN
                '[16-20]' 
        WHEN 借款期限 <= 24 THEN
                '[20-24]' ELSE '[24-30]' 
        END ) AS 借款期限区间,
(CASE
            
        WHEN 借款金额 <= 1000 THEN
            '[1000以下]' 
        WHEN 借款金额 <= 3000 THEN
            '[1000-3000]' 
        WHEN 借款金额 <= 5000 THEN
            '[3000-5000]' 
        WHEN 借款金额 <= 10000 THEN
            '[5000-10000]' 
        WHEN 借款金额 <= 40000 THEN 
            '[10000-40000]'
            else '[40000以上]' 
        END ) AS 借款金额区间,
(CASE
                
        WHEN 年龄 < 22 THEN
            '[18-22]' 
        WHEN 年龄 < 30 THEN
            '[22-30]' 
        WHEN 年龄 < 40 THEN
            '[30-40]' 
        WHEN 年龄 < 50 THEN
            '[40-50]' 
        WHEN 年龄 < 60 THEN
            '[50-60]' ELSE '[60-65]' 
        END ) AS 年龄区间,

CONCAT( YEAR ( 借款成功日期 ), 'Q', QUARTER ( 借款成功日期 ) ) AS 借款季度,

( CASE WHEN 手机认证 = '成功认证' THEN '成功认证' ELSE '未成功认证' END ) AS 手机认证,
( CASE WHEN 户口认证 = '成功认证' THEN '成功认证' ELSE '未成功认证' END ) AS 户口认证,
( CASE WHEN 视频认证 = '成功认证' THEN '成功认证' ELSE '未成功认证' END ) AS 视频认证,
( CASE WHEN 征信认证 = '成功认证' THEN '成功认证' ELSE '未成功认证' END ) AS 征信认证,
( CASE WHEN 淘宝认证 = '成功认证' THEN '成功认证' ELSE '未成功认证' END ) AS 淘宝认证

from lcis0

2.2 查看新视图

SELECT
    * 
FROM
    news
LIMIT 10

2.2.png

3 数据分析

3.0查看各季度下借款金额和人均金额情况

SELECT
    借款季度,
    round( sum( 借款金额 ) / 1000000, 2 ) AS '借款总额(百万)',
    round( sum( 借款金额 ) / count( * ), 2 ) AS 人均金额
    news 
GROUP BY
    借款季度 
ORDER BY
    借款季度

3.0.png

1.相对其他季度,2015Q4 的借款总额严重不足,可能是数据丢失,或业务层面出现问题
2.总体上,每年各季度较上季度借款总额逐步提升,同时人均金额逐步下降,至5000左右趋稳

3.1 性别与逾期率的关系

SELECT
    性别,
    count( * ) AS 客户数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率
FROM
    news 
GROUP BY
    性别

3.1.png

1.客户群体中,男性占比较高,达65%之多
2.而逾期情况,男性也高于女性,现实生活中女性更注重信用

3.2查看各借款类型与逾期率的关系

SELECT
    借款类型,
    count( * ) AS 客户总数,
    sum( 是否逾期 ) AS 逾期数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率
FROM
    news 
GROUP BY
    借款类型 
ORDER BY
    客户总数 DESC

3.2.png

借款产品共分为普通,其他,APP闪电,电商,应收安全标

其中普通,其他,APP闪电,为主要产品

APP闪电的逾期数略高于其他产品

3.3 查看各借款区间与逾期率的关系

SELECT
    借款金额区间,
    count( * ) AS 客户人数,
    sum( 是否逾期 ) AS 逾期人数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款金额区间

3.3.png

主要借款金额集中在1000-10000之间,而这区间的逾期率也是相对其他较低

3.4 不同借款期限的客户数与逾期率的关系

SELECT
    借款期限区间,
    count( * ) AS 客户总数,
    sum( 是否逾期 ) AS 逾期客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款期限区间 
ORDER BY
    借款期限区间

3.4.png

客户借款期限集中在[4-12] 中间

[ 20-24 ]区间的逾期率高的7%,远远高于其他区间

3.5 不同借款利率的客户数与逾期率的关系

SELECT
    借款利率区间,
    count( * ) AS 客户总数,
    sum( 是否逾期 ) AS 逾期客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款利率区间 
ORDER BY
    借款利率区间

3.5.png

[16-20]区间的利率最受欢迎

逾期率随着利率的上升而上升

3.6 年龄分布与逾期率的关系

SELECT
    年龄区间,
    count( * ) AS 客户总数,
    sum( 是否逾期 ) AS 逾期客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    年龄区间 
ORDER BY
    年龄区间

3.6.png

[20-30]年龄的客户最多,

30岁以上,年龄越大,客户越少

[18-22],[40-50的]逾期率最高,可能逾期消费能力弱,及还款意识差有关

3.7 初始评级与逾期率的关系

SELECT
    初始评级,
    count( * ) AS 客户总数,
    sum( 是否逾期 ) AS 逾期客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    初始评级 
ORDER BY
    初始评级

3.7.png

总体上,初始评级越高,逾期率越低

3.8 认证情况分析

3.8.1 手机认证与逾期率的关系

SELECT
    借款季度,
    手机认证,
    count( * ) AS 客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款季度,手机认证 
ORDER BY
    借款季度

3.8.1.png

2015Q数据异常,可排除分析

2015年度手机成功认证的客户远远大于未认证的

2016年度手机成功认证的客户则小于为认证的,可能是因为平台未来扩大市场,放宽了对认证的审查.

逾期率与手机是否认证没有明显相关性

3.8.2 户口认证与逾期率的关系

SELECT
    借款季度,
    户口认证,
    count( * ) AS 客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款季度,户口认证
ORDER BY
    借款季度

3.8.2.png

户口未成功认证的客户数逐年递增

逾期率与户口是否认证没有明显相关性

3.8.3 视频认证与逾期率的关系

SELECT
    借款季度,
    视频认证,
    count( * ) AS 客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款季度,视频认证 
ORDER BY
    借款季度

3.8.3.png

视频未成功认证的客户数逐年递增

逾期率与视频是否认证没有明显相关性

3.8.4 征信认证与逾期率的关系

SELECT
    借款季度,
    征信认证,
    count( * ) AS 客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款季度,征信认证 
ORDER BY
    借款季度

3.8.4.png

征信未成功认证的客户数逐年递增

逾期率与征信是否认证没有明显相关性

3.8.5 淘宝认证与逾期率的关系

SELECT
    借款季度,
    淘宝认证,
    count( * ) AS 客户总数,
    concat( round( sum( 是否逾期 ) * 100 / count( * ), 2 ), '%' ) AS 逾期率 
FROM
    news 
GROUP BY
    借款季度,淘宝认证 
ORDER BY
    借款季度

3.8.5.png

淘宝未成功认证的客户数逐年递增

逾期率与淘宝是否认证没有明显相关性

4 总结与建议

2015Q数据异常,检查技术或业务方面是否存在问题

客户群体偏年轻化,考虑到逾期情况,可主打[22-40]岁为主要客户

女性用户少,逾期率低,可作为潜在群体运营

加强初级评级,严格控制评级等级低的客户

重点发展利率在[4-16]之间的产品

各项认证对于逾期率无明显相关,可放宽认证力度

拍拍贷用户逾期数据分析sql

0 分析逾期情况

1 数据清洗

1.1 查看所有字段

1.2 查看年龄分布

1.4 依次对各字段进行查看,了解其分布情况(略)

2 数据处理

2.1 创建新视图,

2.2 查看新视图

3 数据分析

3.0查看各季度下借款金额和人均金额情况

3.1 性别与逾期率的关系

3.2查看各借款类型与逾期率的关系

3.3 查看各借款区间与逾期率的关系

3.4 不同借款期限的客户数与逾期率的关系

3.5 不同借款利率的客户数与逾期率的关系

3.6 年龄分布与逾期率的关系

3.7 初始评级与逾期率的关系

3.8 认证情况分析

3.8.1 手机认证与逾期率的关系

3.8.2 户口认证与逾期率的关系

3.8.3 视频认证与逾期率的关系

3.8.4 征信认证与逾期率的关系

3.8.5 淘宝认证与逾期率的关系

4 总结与建议

猜你喜欢

热点阅读