用MYSQL分析网络销售案例

2019-04-04 本文已影响7人 LucasOoo

数据来源于某网站的销售统计，主要分为两部分：
1、网络订单数据；
2、用户信息。
数据来源：链接：https://pan.baidu.com/s/1urm8NR2hvlCsKK2Ip9uoTw
提取码：qj4b

阅读路线：

0、数据导入
1、不同月份的下单人数
2、用户三月份的回购率和复购率
3、统计男女用户的消费频次
4、统计多次消费用户，分析第一次和最后一次的消费间隔
5、统计不同年龄段用户的消费金额差异
6、统计消费的二八法则：消费top20%的用户贡献了多少消费额度

0、数据导入

1）通过workbench新建一个schema。

create schema data1;

导入以上两个表到data1中，并设置列的类型：
order_info 表格如下设置：

image.png

设置orderID为主键，且不能为空值；
PaidTime为时间戳类型。

user_info按如下设置：

image.png

userID为主键，且不能为空；
Birth生日设置为DATE类型。

导入后各个表数据如下所示：

orderinfo数据结果

userinfo数据结果

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')

运算结果：

不同月份的下单人数

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

用户三月份的复购率

三月份用户的复购率为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

用户回购率的结果

三月用户的回购率为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

各用户消费频次统计

再对以上数据进行分组，对频次求均值即可求出男女用户的消费频次：

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

男女用户的消费频次

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

第一次和最后一次的消费间隔结果

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'

年龄段划分结果

时间差函数：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)

不同年龄段的消费情况

年龄在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

用户消费总额排序结果

计算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

top20%用户的消费总额

所有用户的消费总额:

select sum(price)
from orderinfo

所有用户的消费总额

top20%用户的消费总额占比情况:top20%用户的消费总额/所有用户的消费总额=73.93%
top20%的用户贡献了73.93%消费额度。

用MYSQL分析网络销售案例

阅读路线：

0、数据导入

1、不同月份的下单人数

2、用户三月份的复购率和回购率

3、统计男女用户的消费频次

4、统计多次消费用户，分析第一次和最后一次的消费间隔

5、不同年龄段的消费差异

6、统计消费的二八法则：消费top20%的用户贡献了多少消费额度

猜你喜欢

热点阅读