本文利用MYSQL在数据分析中的作用,将数据导入NAVICAT,对某电商展开数据分析工作:
一、理解数据
字段说明:
(1)orderinfo表
1、orderid:订单编号
2、userid:用户编号
3、isPaid:订单状态,是否已支付
4、price:价格
5、paidTime:支付时间
(2)userinfo表
1、userid:用户编号
2、sex:性别
3、birth:出生日期
二、提出问题
1、统计不同月份的下单人数
2、统计用户三月份的复购率
3、统计用户三月份的回购率
4、统计男女用户消费频次是否有差异
5、统计多次消费的用户,第一次和最后一次消费间隔是多少天
6、统计不同年龄段,用户的消费金额是否有差异
7、统计消费的二八法则,消费的top20%用户,贡献了多少消费额
三、代码实现
1、统计不同月份的下单人数
select
year(paidTime) as '年份'
,month(paidTime) as '月份'
,count(distinct userid) as '人数'
from orderinfo
where paidTime != '0000-00-00 00:00:00' and isPaid = '已支付'
group by year(paidTime),month(paidTime)
对年和月做分组统计,得到数据样本2016年3月下单人数54799人,4月43967人,5月6人。
2、统计用户三月份的复购率
复购率:当月购买了多次的用户占当月用户的比例
select
count(1) as '3月用户'
,sum(if(cons>=2,1,0)) as '3月复购人数'
,concat(sum(if(cons>=2,1,0))*100/count(1),'%') as '3月复购人数占比'
from (select
userid
,count(1) as cons
from orderinfo
where paidTime != '0000-00-00 00:00:00'
and isPaid = '已支付'
and year(paidTime) = 2016
and month(paidTime) = 3
group by userid) a
首先对3月用户进行分组统计,计算消费次数,对消费次数大于1的用户计算为复购人数,最后得到3月用户54799人,3月复购人数16916人,3月复购人数占比30.87%。
3、统计用户三月份的回购率
回购率:上月购买用户中有多少用户本月又再次购买
select
a.month_dt
,count(a.userid) as '当月购买人数'
,count(b.userid) as '回购人数'
,concat(count(b.userid)*100 / count(a.userid),'%') as '回购率'
from (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) a
left join (select
userid,
date_format(paidTime,'%Y-%m-01') as month_dt
from orderinfo
where isPaid="已支付"
group by userid,date_format(paidTime,'%Y-%m-01')) b
on a.userid=b.userid
and date_sub(b.month_dt,interval 1 month)=a.month_dt
group by a.month_dt;
首先对用户编号和年月做分组统计,然后将得到的结果按相邻月份条件做左自连接,这样有回购的就能连接成功,没有回购的为null值,再利用count忽略null值的特性,统计回购人数和购买人数,最后得到各月购买人数、回购人数和回购率。
4、统计男女用户消费频次是否有差异
select
sex
,avg(cons) as '平均消费频次'
from (select
a.userid
,sex
,count(1) as cons
from orderinfo a
inner join userinfo b
on a.userid = b.userid
where sex != ''
group by a.userid ) a
GROUP BY sex
首先统计每个用户的消费次数,然后对性别再做一个消费次数平均计算,最后得到女性平均消费频次1.9459次,男性1.9312次。
5、统计多次消费的用户,第一次和最后一次消费间隔是多少天
select
userid
,min(paidTime) as '第一次消费时间'
,max(paidTime) as '最后一次消费时间'
,datediff(max(paidTime), min(paidTime)) as '消费间隔'
from orderinfo
where isPaid="已支付"
group by userid
having count(1)>=2;
按用户编号做分组统计,筛选出多次消费的用户,然后取出第一次和最后一次的时间,在做时间差。
6、统计不同年龄段,用户的消费金额是否有差异
select
age
,avg(prices) as '平均消费金额'
,avg(cons) as '平均消费频率'
from (select
a.userid
,age
,count(*) as cons
,sum(price) as prices #先求单个用户的总消费,先sum再avg
from (select
userid
,birth
,now()
,timestampdiff(year,birth,now())
,ceil(timestampdiff(year,birth,now())/10) as age
from userinfo
where birth>'1900-00-00'
) a
inner join orderinfo b
on a.userid = b.userid
group by a.userid,age
) a
group by age
order by age
首先计算每个用户的年龄,并对年龄进行分层:0-10为1,11-20为2,21-30为3,以此类推。然后关联订单信息,获取不同年龄段的一个消费频次和消费金额,最后再对年龄分层进行聚合,得到数据样本不同年龄层的消费情况。
7、统计消费的二八法则,消费的top20%用户,贡献了多少消费额
select
count(*)
,sum(sum_p)
,concat(sum(sum_p)*100/(select
sum(price) as total_sum
from orderinfo
where isPaid="已支付"),'%') as '20%用户消费占比'
from (select
userid
,sum(price) as sum_p
from orderinfo
where isPaid="已支付"
group by userid
order by sum_p desc
limit 17000) a
首先统计每个用户的消费金额,并降序排序 ,再统计一共有多少用户,以及总消费金额是多少,取出前20%的用户进行金额统计,最后得到前20%的用户贡献了85%的消费额。