用MYSQL分析网络销售案例
数据来源于某网站的销售统计，主要分为两部分：
1、网络订单数据；
2、用户信息。
数据来源：链接：https://pan.baidu.com/s/1urm8NR2hvlCsKK2Ip9uoTw
提取码：qj4b
阅读路线：
0、数据导入
1、不同月份的下单人数
2、用户三月份的回购率和复购率
3、统计男女用户的消费频次
4、统计多次消费用户，分析第一次和最后一次的消费间隔
5、统计不同年龄段用户的消费金额差异
6、统计消费的二八法则：消费top20%的用户贡献了多少消费额度
0、数据导入
- userID为主键，且不能为空；
- Birth生日设置为DATE类型。
导入后各个表数据如下所示：
image.png
image.png
1、不同月份的下单人数
思路：对orderinfo按月分组并对userID去重、计数。
select date_format(paidtime,'%Y-%c')as dtmonth,
count(distinct userID) as count_users
from orderinfo
where ispaid='已支付'
group by date_format(paidtime,'%Y-%c')
运算结果：
image.png
2、用户三月份的复购率和回购率
复购率：自然月内，购买多次的用户占比
回购率：曾经购买过的用户在某一时期内再次购买的占比
2.1 用户复购率计算：
首先，计算三月份每个用户的购买次数：
select userID,count(userID)as ct
from orderinfo
where month(paidtime)=3 and IsPaid='已支付'
group by userID
再嵌套个select函数，计算复购率：购买次数大于1的人数/购买总人数。
select count(if(t.ct>1,1,null)) /count(1)as 三月复购率
from(
select userID,count(userID)as ct
from orderinfo
where month(paidtime)=3 and IsPaid='已支付'
group by userID
) as t
image.png
三月份用户的复购率为30.87%
2.2 用户回购率计算
首先对数据进行过滤，将已支付的用户userID，支付日期月份筛选出来：
select userID,date_format(paidtime,'%Y-%m-01')as m
from orderinfo
where ispaid='已支付'
group by userID,date_format(paidtime,'%Y-%m-01')
image.png
采用date_sub函数，将上表与下月消费的userID进行表联结，即可筛选出本月消费的userID和下月回购的userID，即可计算每月的回购率：
select t1.m,
count(t1.m) as 消费总人数,
count(t2.m) as 回购人数,
count(t2.m)/count(t1.m) as 回购率
from
(select userID,date_format(paidtime,'%Y-%m-01')as m
from orderinfo
where ispaid='已支付'
group by userID,date_format(paidtime,'%Y-%m-01'))as t1
left join
(select userID,date_format(paidtime,'%Y-%m-01')as m
from orderinfo
where ispaid='已支付'
group by userID,date_format(paidtime,'%Y-%m-01'))as t2
on t1.userID=t2.userID
and t1.m=date_sub(t2.m,interval 1 month)
group by t1.m
image.png
- 三月用户的回购率为23.94%
3、统计男女用户的消费频次
分析思路：将orderinfo和BVV
+userinfo进行表联结，并统计每个人的消费频次。
select userinfo.UserID,
userinfo.sex,
count(1)
from orderinfo
left join userinfo
on userinfo.userID=orderinfo.userID
where IsPaid='已支付'
and userinfo.sex <>''
group by userinfo.UserID
image.png
再对以上数据进行分组，对频次求均值即可求出男女用户的消费频次：
select t.sex,avg(t.ct) as 平均消费频次
from(
select userinfo.UserID,
userinfo.sex,
count(1) as ct
from orderinfo
left join userinfo
on userinfo.userID=orderinfo.userID
where IsPaid='已支付'
and userinfo.sex <>''
group by userinfo.UserID
) t
group by t.sex
image.png
4、统计多次消费用户，分析第一次和最后一次的消费间隔
通过datediff函数计算每个用户消费日期的最大值和最小值的间隔天数，过滤掉最大值和最小值相等的值，或者间隔天数为0的用户，即为多次消费用户第一次和最后一次的消费间隔：
select userID,datediff(max(paidtime),min(paidtime))as 消费间隔
from orderinfo
where orderinfo.ispaid='已支付'
group by userID
having max(paidtime)!=min(paidtime)
#or
having datediff(max(paidtime),min(paidtime))!=0
image.png
5、不同年龄段的消费差异
首先通过表表联结的方式给不同用户划分年龄段，以10年为间隔进行划分，过滤掉出生日期为1900-00-00的异常值：
select orderinfo.*,ceil(timestampdiff(year,userinfo.birth,now())/10) as age
from orderinfo
left join userinfo
on orderinfo.userID=userinfo.userID
where orderinfo.ispaid='已支付'
and userinfo.birth>'1901-00-00'
image.png
- 时间差函数：TIMESTAMPDIFF(unit,begin,end); 根据单位返回时间差,对于传入的begin和end不需要相同的数据结构,可以存在一个为Date一个DateTime；unit可等于（year,quarter,week,day,hour,second,microsecond等）；
DATEDIFF(date1，date2) 函数返回两个日期之间的天数； - CEIL(X) 返回不小于X的最小整数值。(天花板)
FLOOR(X) 返回不大于X的最大整数值。(地板)
再对以上年龄段分组求消费均值：
select t.age,avg(t.price)
from(
select orderinfo.*,ceil(timestampdiff(year,userinfo.birth,now())/10) as age
from orderinfo
left join userinfo
on orderinfo.userID=userinfo.userID
where orderinfo.ispaid='已支付'
and userinfo.birth>'1901-00-00'
) as t
group by t.age
order by avg(t.price)
image.png
- 年龄在90-100之间的人均消费最高，达到了653.96元（不排除乱填信息的情况）；
- 各个年龄段人均消费较为平均，极差为120元。
6、统计消费的二八法则：消费top20%的用户贡献了多少消费额度
计算每个用户的消费总额并排序：
elect userID,sum(price)as sp
from orderinfo
where ispaid='已支付'
group by userID
order by sum(price) desc
image.png
计算top20%的用户数：
select floor(count(1)*0.2)
from
(select userID,sum(price)as sp
from orderinfo
where ispaid='已支付'
group by userID
order by sum(price) desc )as t
image.png
在源程序的基础上，计算top20%用户的消费总额：
select sum(t.sp) as sum_top20
from
(select userID,sum(price)as sp
from orderinfo
where ispaid='已支付'
group by userID
order by sum(price) desc limit 17192)as t
image.png
所有用户的消费总额:
select sum(price)
from orderinfo
image.png
top20%用户的消费总额占比情况:top20%用户的消费总额/所有用户的消费总额=73.93%
top20%的用户贡献了73.93%消费额度。
