- 所需数据：ORDER_INFO_UTF.CSV、USER_INFO_UTF.CSV
- 数据链接:https://pan.baidu.com/s/11ZtjKnv5-nwyf6cMyk_3JQ
- 提取码:yu63
目录：
一、将数据导入数据库
二、SQL--用户消费行为分析
1-统计不同月份的下单人数
2-统计用户三月份的回购率和复购率
3-统计男女的消费频次是否有差异
4-统计多次消费的用户，第一次和最后一次消费时间的间隔
5-统计不同年龄段的用户消费金额是否有差异
6-统计消费的二八法则，消费的top20%用户，贡献了多少额度
一、将数据导入数据库
目的：将两份csv文件导入数据库
步骤：建表、导入数据
前面给出的数据较大，分别是10万和50万条数据，如果用数据库自带的导数工具比较缓慢，像WORKBENCH导入几千行数据就需要3分钟时间，这里可以用cmd命令行导入、或者用KETTLE进行抽取
建表
1、订单明细表
CREATE TABLE ORDERINFO (
ORDERID varchar(10) NULL,--订单ID，主键
USERID varchar(10) NULL,--用户ID，可以和用户表进行关联
ISPAID varchar(10) NULL,--是否支付
PRINCE varchar(10) NULL,--订单价格
PAIDTIME varchar(10) NULL --订单支付时间
)
2、用户表
CREATE TABLE YWUSER.USERINFO (
USERID varchar(10) NULL,--用户ID，主键
SEX varchar(10) NULL,--性别
BIRTH varchar(10) NULL --出生日期
)
导入数据
这里用KETTLE进行数据抽取，没用过KETTLE的同学可以用CMD命令行导入，可以参考秦路老师七周七成为数据分析师视频。
0、kettle转换过程
抽取用户表
如果在导入csv文件的时候乱码，可以添加字段选择节点。
1、csv文件输入
2、字段选择
在csv文件输入的时候可能出现乱码的情况，可以通过字段选择修改数据类型，将encoding修改为UTF-8
3、表输出
上面是用户表的数据导入，导入订单明细表方法类似，更换文件路径、目标表即可。
二、SQL--用户消费行为分析
使用的是SQLSERVER数据库
分析问题：
1-统计不同月份的下单人数
2-统计用户三月份的回购率和复购率
3-统计男女的消费频次是否有差异
4-统计多次消费的用户，第一次和最后一次消费时间的间隔
5-统计不同年龄段的用户消费金额是否有差异
6-统计消费的二八法则，消费的top20%用户，贡献了多少额度
1-统计不同月份的下单人数
select substring(paidtime,1,4)+'-'+ substring(paidtime,6,2) MONTH_,count(distinct USERid) XDRS from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by substring(paidtime,1,4),substring(paidtime,6,2)
1
2-统计用户三月份的回购率和复购率
复购率
--复购率是在本月消费中多少人消费一次以上的占比
select count(tt.USERID) '三月购买的总用户数',count(tt.USERID_count) '多次购买用户数'from
(select
t.USERID USERID,
case when t.USERID_count>1 then 1 else null end USERID_count
from
(select USERID ,count(USERID) USERID_count from YWUSER.ORDERINFO
where ISPAID = '已支付' and substring(paidtime,6,2) = '03'
group by USERID ) T
)tt
2
回购率
--回购率是三月份购买的人数四月份依旧购买
select substring(a.paidtime,1,7) '年月',count(distinct a.USERID) '本月消费的用户数量',count(distinct b.USERID)'本月回购的用户数' from
(select userid,paidtime from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) a
left join (select userid,paidtime from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by userid,paidtime) b on a.userid = b.userid and substring(a.paidtime,6,2) = substring(b.paidtime,6,2)-1
group by substring(a.paidtime,1,7)
2
3-统计男女的消费频次是否有差异
--先统计每个用户的消费频次在统计，在统计男女的平均消费频次
select aa.sex '性别',round(avg(aa.PC_ORDERID),2) '消费频次'from
(select a.USERID,b.sex,count(a.ORDERID) PC_ORDERID from
YWUSER.ORDERINFO a
inner join (select * from YWUSER.USERINFO where SEX is not null)b on a.USERID = B.USERID
group by a.USERID,b.sex)aa
group by aa.sex
思路正确，结果有一点问题
4、统计多次消费的用户，第一次和最后一次消费时间的间隔
--（可以大概理解为生命周期、多次消费的用户：消费频次超过2次的用户；先求每个用户的第一次和最后一次的消费时间间隔，在求平均消费间隔）
select
USERID,
ORDERID_CS,
convert(date,replace(paidtime_max,'/','-'),120) paidtime_max,
convert(date,replace(paidtime_min,'/','-'),120) paidtime_min,
datediff(dd,convert(date,replace(paidtime_min,'/','-'),120),convert(date,replace(paidtime_max,'/','-'),120)) '第一次最后一次消费时间差' from
(select USERID,count(ORDERID) ORDERID_CS,max(paidtime) paidtime_max,min(paidtime) paidtime_min from YWUSER.ORDERINFO
where ISPAID = '已支付'
group by USERID having count(ORDERID)>1)aa
4
5-统计不同年龄段的用户消费金额是否有差异
select aa.NLD,round(avg(aa.PRINCE),2) avg_prince from
(select a.ORDERID,A.USERID,cast(A.PRINCE as float) PRINCE,
case when B.AGE between 10 and 19 then '10-19岁' when B.AGE between 20 and 29 then '20-29岁'
when B.AGE between 30 and 39 then '30-39岁' when B.AGE between 40 and 49 then '40-49岁' when B.AGE between 50 and 59 then '50-59岁'
when B.AGE between 60 and 69 then '60-69岁'when B.AGE between 70 and 79 then '70-79岁'
else null end NLD,
B.AGE from
(select * from YWUSER.ORDERINFO where ISPAID = '已支付')a
inner join (select USERID,year(getdate())-left(BIRTH,4) age from YWUSER.USERINFO where BIRTH is not null) b on a.USERID = b.USERID
)aa group by aa.NLD having aa.NLD is not null
5
6-统计消费的二八法则，消费的top20%用户，贡献了多少额度
select sum(sum_prince) from --top20%用户贡献的总额度
(select *,row_number()over(order by sum_prince desc)as '排序' from
(select userid,round(sum(cast(prince as float)),2) sum_prince from YWUSER.ORDERINFO where ISPAID = '已支付' group by USERID
) aa) tt
where 排序< (select count(distinct USERID)* 0.2 from YWUSER.ORDERINFO where ISPAID = '已支付')
6
如果有其他的分析方法欢迎大家留言，有不会的地方可以写到下面，看到一定回复大家。
关于SQL的练习可以参考我以前写的2篇文章：
1、导入数据时出现的3个问题：【SQL练习】经典SQL练习题
2、总结：SQL练习【SQL经典练习题】
网友评论