SQL练习2：消费者行为分析
2020-11-11 本文已影响0人
jinghenggl
目录：
一、将数据导入数据库
二、SQL--用户消费行为分析
1、统计不同月份的下单人数
2、统计用户三月份的回购率和复购率
3、统计男女的消费频次是否有差异
4、统计多次消费的用户，第一次和最后一次消费时间的间隔
5、统计不同年龄段的用户消费金额是否有差异
6、统计消费的二八法则，消费的top20%用户，贡献了多少额度
一、将数据导入数据库
目的：将两份csv文件导入数据库
步骤：建表、导入数据
建表
1、订单明细表orderinfo：
image.png
2、用户表userinfo：
image.png
3、导入数据
我是用Navicat中的导入向导直接导入。两个数据集分别是10万和50万条数据，导入时间时长较快。
数据集更大时可以用cmd命令行导入、或者用KETTLE进行抽取。我尝试用CMD命令导入几次，但一直报 ERROR 1290 (HY000)，尝试几次没解决故作罢。
二、用户消费行为分析
使用MySQL数据库
分析问题：
1、统计不同月份的下单人数
2、统计用户三月份的回购率和复购率
3、统计男女的消费频次是否有差异
4、统计多次消费的用户，第一次和最后一次消费时间的间隔
5、统计不同年龄段的用户消费金额是否有差异
6、统计消费的二八法则，消费的top20%用户，贡献了多少额度
1、统计不同月份的下单人数
select month(paidTime),count(userID) from orderinfo
where isPaid = '已支付'
group by month(paidTime);
image.png
2、统计用户三月份的复购率和回购率
（1）复购率是在本月消费一次以上用户的占比(区分消费1次及1次以上的)
select count(ct) as 总消费用户,
sum(case when ct>1 then 1 else 0 end) as 复购用户,
sum(case when ct>1 then 1 else 0 end)/count(ct) as 复购率
from(select userID,count(userID) as ct
from orderinfo
where isPaid = '已支付' and month(paidTime)=3
group by userID) t;
image.png
（2）回购率是三月份购买的人数四月份依旧购买
select t1.m as 月份,count(t1.m) as 本月消费用户,count(t2.m) as 本月回购用户 from
(select userID,month(paidTime) as m
from orderinfo
where isPaid='已支付'
group by userID,month(paidTime)) t1
left join
(select userID,month(paidTime) as m
from orderinfo
where isPaid='已支付'
group by userID,month(paidTime)) t2
on t1.userID=t2.userID and t1.m= DATE_SUB(t2.m,INTERVAL 1 MONTH)
group by t1.m;
image.png
3、统计男女的消费频次是否有差异
select sex,avg(ct) from
(select o.userID,sex,count(*) as ct
from orderinfo o
inner join
(select * from userinfo where sex is not null ) t
on o.userID= t.userID group by o.userID,sex) t2;
image.png
4、统计多次消费的用户，第一次和最后一次消费时间的间隔
select userID , DATEDIFF(max(paidTime),min(paidTime)) as 消费间隔
from orderinfo
where isPaid='已支付'
group by userID having count(*)>1;
5、统计不同年龄段的用户消费金额是否有差异
select age,avg(ct) from
(select o.userID,age,count(o.userID) as ct from orderinfo o
inner join
(select userID,ceil((year(now())-year(birth))/10) as age
from userinfo
where birth >'0000-00-00') t
on o.userID = t.userID
group by o.userID,age) t2
group by age;
image.png
6、统计消费的二八法则，消费的top20%用户，贡献了多少额度
select count(userID) ,sum(total) as 前20%用户
from
(select userID,sum(price) as total
from orderinfo
where isPaid='已支付'
group by userID
order by total desc
limit 17000) t;
image.png
