• 所需数据：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转换过程

抽取用户表

2、字段选择

二、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经典练习题】