使用MySQL分析网络销售数据
用MySQL分析网络销售数据
数据来自某个销售网站的数据
1.网络订单数据
2.用户信息
点击获取数据 提取码：udek
分析步骤
0、数据导入
1、不同月份的下单人数
2、用户三月份的回购率和复购率
3、统计男女用户的消费频次
4、统计多次消费用户，分析第一次和最后一次的消费间隔
5、统计不同年龄段用户的消费金额差异
6、统计消费的二八法则：消费top20%的用户贡献了多少消费额度
0 数据导入
首先需要先创建对应的数据库和相应的表
1.创建orderinfo 表
2.创建userinfo表
3.使用navicat导入csv文件
4.登录mysql，观察数据，对时间进行处理 ; 更新字符串为日期格式
# 登录
mysql -uroot -p
# 时间进行处理，更新字符串为日期格式
update orderinfo set paidtime=replace(paidtime,'/','-') where paidtime is not null
update orderinfo set paidtime=str_to_date(paidtime,'%Y-%m-%d %H:%i') where paidtime is not null
5.查看数据
1 不同月份的下单人数
思路 ：按月份进行分组，对用户进行去重统计
select month(paidTime) as dtmonth, count(distinct userId) as count_users from orderinfo where isPaid = '已支付' group by dtmonth
2 用户三月份的回购率和复购率
- 复购率 ： 自然月内，购买多次的用户占比
- 首先先找出已支付中3月份的用户id和对应次数，按用户分组
- 然后再嵌套一层，复购率：购买次数大于1/ 总购买次数
select count(ct),count(if(ct>1,1,null)) from(
select userID,Count(userId) as ct from orderinfo
where isPaid = '已支付'
and month(paidTime) = 3
group by userId
order by userId) t
复购率： 16916 / 54799 = 0.308
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')
然后使用date_sub函数，将表关联，筛选出本月的消费的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')) 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')) t2
on t1.userId = t2.userId and t1.m = date_sub(t2.m, interval 1 month)
group by t1.m
3 统计男女用户的消费频次
- userinfo因为性别有空值，需要筛选出t orderinfo 再和表t连接 统计出用户男女消费次数
-
select o.userId,sex,count(o.userId)as ct from orderinfo o inner join (select * from userinfo where sex != '') t on o.userId = t.userId group by userId,sex order by userId
- 根据上表，在进行子查询，统计出男女消费平均频次
-
select sex,avg(ct) from( select o.userId,sex,count(o.userId)as ct from orderinfo o inner join (select * from userinfo where sex != '') t on o.userId = t.userId group by userId,sex order by userId)t2 group by sex
4 统计多次消费用户，分析第一次和最后一次的消费间隔
- 首先把多次消费的用户,和相应第一次最后一次消费时间提取出来
- 然后使用datediff 计算时间间隔，以天为单位
select userId,max(paidTime),min(paidTime),datediff(max(paidTime),min(paidTime)) from data.orderinfo
where isPaid = '已支付'
group by userId having count(1) > 1
order by userId
5 统计不同年龄段用户的消费金额差异
通过表联结，给用户划分不同的年龄段，以10年为基准，过滤出生日期为1900-00-00的异常值,筛选出用户消费频次和消费金额
select o.userId,age,sum(price),count(o.userId)as ct from orderinfo o
inner join (
select userId, ceil((year(now()) - year(birth))/10) as age
from userinfo
where birth > 1901-00-00) t
on o.userId = t.userId
where isPaid = '已支付'
group by userId
order by userId
统计出年龄段的消费频次和消费金额
select t2.age,avg(ct),avg(sp) from (
select o.userId,age,sum(price) as sp,count(o.userId)as ct from orderinfo o
inner join(
select userId, ceil((year(now()) - year(birth))/10) as age
from userinfo
where birth > 1901-00-00)t
on o.userId = t.userId
where ispaid = '已支付'
group by userId, age) t2
group by age
order by age
- ceil : 向上取整
6 统计消费的二八法则：消费top20%的用户贡献了多少消费额度
按照用户消费总额排序
select userId,sum(price) as total from orderinfo o
where isPaid = '已支付'
group by userId
order by total desc
查看总用户数和总金额
select count(userId),sum(total) from (
select userId,sum(price) as total from orderinfo o
where isPaid = '已支付'
group by userId
order by total desc) as t
select count(userId)*0.2,sum(total) from (
select userId,sum(price) as total from orderinfo o
where isPaid = '已支付'
group by userId
order by total desc)as t
limit限制前17000用户
select count(userId),sum(total) from (
select userId,sum(price) as total from orderinfo o
where isPaid = '已支付'
group by userId
order by total desc
limit 17129) t
