使用MS Access SQL进行简单的数据分析

hebergeur-ms-access

本篇文章使用MS Access SQL对Lendingclub 2015年1月——6月的数据进行提取和简单的分析。主要内容包括贷款的关键指标,包括金额,笔数,利息收入。以及贷款用户的地域和职业分布,还款情况和贷款期限等数据。下面是贷款数据表的截图。

loanstats
关键指标及趋势

第一部分是获得数据表中的关键指标,这里包括贷款总笔数,总金额,总利息收入和贷款金额的范围以及均值等指标。

贷款总笔数及金额
首先对数据表的用户id列member_id进行计数,取名为count,对贷款金额列loan_amnt进行求和,取名为sum,对利息总收入列total_rec_int进行求和,取名为total_int。

SELECT COUNT(member_id)as count,SUM(loan_amnt)as sum,ROUND(SUM(total_rec_int),2) as total_int
FROM loanstats;

贷款笔数及金额

贷款金额范围及均值
然后对数据表的贷款金额列loan_amnt求最大值和最小值,以及均值。计算贷款金额的范围和均值。

SELECT MAX(loan_amnt)as MAX,MIN(loan_amnt)as MIN,ROUND(AVG(loan_amnt),2)as AVG
FROM loanstats;

贷款金额范围及均值

月贷款金额及笔数趋势
按贷款发生时间进行汇总,分别对用户id列进行计数,对贷款金额列进行求和,获得按月贷款金额及笔数变化趋势数据。

SELECT issue_d,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
GROUP BY issue_d;

用户属性

第二部分是获取数据表中的用户属性数据,包括用户所在的地域城市,贷款用户的职业以及贷款用途。

用户地域
按用户所在城市字段addr_state对数据表中的用户id进行计数,对贷款金额进行汇总,并按贷款金额对数据进行逆序排序。获得不同城市用户贷款金额的排名数据。

SELECT addr_state,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
GROUP BY addr_state
ORDER BY SUM(loan_amnt) DESC;

用户所在城市

用户职业(贷款笔数最多的前20个职业)
按用户职业字段对数据表中的用户id进行计数,贷款金额进行求和,这里由于用户职业字段并不是必填项,因此部分用户的职业信息为空。需要在查询过程中排除掉为空的字段。然后对查询结果按用户id进行逆序排序。获得贷款次数最多的前20个职业。

SELECT TOP 20 emp_title,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
WHERE emp_title IS NOT NULL
GROUP BY emp_title
ORDER BY COUNT(member_id) DESC;

用户职业

贷款用途(贷款笔数最多的前20个用途)
与前面的方法类似,将用户职业换成贷款用途字段purpose,对用户id计数,贷款金额汇总,获得贷款次数排名前20的贷款用途。这里需要说明的是贷款用途字段没有空值,因此不需要排除为空的字段。

SELECT TOP 20 purpose,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
GROUP BY purpose
ORDER BY COUNT(member_id) DESC;

贷款用途

产品数据

第三部分是获取数据表中贷款利息和贷款期限的数据。

贷款利率分布
按贷款利率对用户id和贷款金额进行汇总,并按贷款次数进行逆序排序,获得出现最多的前20个利率。

SELECT TOP 20 int_rate,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
GROUP BY int_rate
ORDER BY COUNT(member_id) DESC;

贷款利率分布

贷款期限分布
按贷款期限对用户id和贷款金额进行汇总,获得不同贷款期限的贷款次数和贷款金额。由于只有两个贷款期限分类,因此无需进行排序。

SELECT term,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
GROUP BY term

贷款期限分布

运营数据

最后一部分是获取运营数据,包括用户等级分布,贷款的还款情况,以及还款情况最好和最差的前10个排名城市。

用户等级分布
首先按用户信用等级和子信用等级对用户id和贷款金额进行汇总,并按贷款次数进行逆序排序。获得不同等级和子等级的贷款次数排序。可以看到,贷款次数最多的是等级C,其中C1和C2级的最多。

SELECT grade,sub_grade,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
GROUP BY grade,sub_grade
ORDER BY COUNT(member_id) DESC;

用户等级分布
还款情况分布
按贷款状态loan_status对数据表中的贷款次数和贷款金额进行汇总,并按贷款次数逆序排序。从结果中可以看到,大部分的贷款目前还在进行中并未完结。其余的状态包括已经还款,坏账和逾期。

SELECT loan_status,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
GROUP BY loan_status
ORDER BY COUNT(member_id) DESC;

还款情况分布

还款最好的TOP 10城市
筛选出贷款状态为已还款的数据,按城市字段对贷款笔数和贷款金额进行汇总排序,查看排名前10的还款最好的城市。

SELECT TOP 10 addr_state,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
WHERE loan_status='Fully Paid'
GROUP BY addr_state ORDER BY COUNT(member_id) DESC;

还款最好的10个城市

还款最差的TOP 10城市
筛选出贷款状态为坏账和逾期的数据,按城市对贷款笔数和贷款金额进行汇总并排序,查看排名前10的还款最差的城市。

SELECT TOP 10 addr_state,COUNT(member_id)as count,SUM(loan_amnt)as sum
FROM loanstats
WHERE loan_status='Charged Off' OR loan_status='Late (16-30 days)' OR loan_status='Late (31-120 days)'
GROUP BY addr_state
ORDER BY COUNT(member_id) DESC;

还款最差的10个城市
—【所有文章及图片版权归 蓝鲸(王彦平)所有。欢迎转载,但请注明转自“蓝鲸网站分析博客”。】—

Comments

  1. mu says:

    代码在哪里输入呢?

mu进行回复 取消回复

*