对面大批量的数据来说,SQL的处理速度会比Excel快上许多。今天我将使用一次几十万行的电商购买行为数据进行练习。
一、理解数据
二、提出问题
·统计不同月份下单人数
·统计用户三月份的回购率和复购率
·统计男女消费频次是否有差异
·统计多次消费的用户,第一次购买和最后一次购买的消费间隔是多少?
·统计不同年龄段,用户消费金额是否有差异?
·统计消费的二八法则,消费top20%的用户,贡献了多少额度?
三、分析问题
1、统计不同月份下单人数
select ispaid,paidTime,count(distinct userId) 下单人数 from orderinfo
where ispaid = "已支付"
group by month(paidTime)
2、统计用户三月份的回购率和复购率
①复购率
select count(if(test>=2,1,null))/count(test) 复购率 from (
select userId, count(1) test from orderinfo
where ispaid = "已支付"
and month(paidTime) = 3
GROUP BY userId
) t;
②回购率
select t1.m,COUNT(t1.m),COUNT(t2.m) from (
select userId,date_format(paidTime,'%Y-%m-01') 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') 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、统计男女消费频次是否有差异
select sex,avg(ct) 消费频次 from (
select o.userId,sex,count(1) ct from orderinfo o
join userinfo u
ON o.userId = u.userId and ispaid = '已支付' and sex in ('男','女')
group by userId,sex ) t
group by sex
4、统计多次消费的用户,第一次购买和最后一次购买的消费间隔是多少?
select userId,max(paidTime),min(paidTime),
datediff(max(paidTime),min(paidTime)) 消费间隔 from orderinfo
where ispaid = '已支付'
group by userId
having count(1) >= 2
这里的消费间隔我们可以粗略知道一个用户的生命周期是多少。
5、统计不同年龄段,用户消费金额是否有差异
select round(datediff(curdate(),birth)/356,0) 年龄,
truncate(avg(price),2) 平均消费金额
from orderinfo o
inner join userinfo u
on o.userId = u.userId and month(birth) <> 00 and ispaid = '已支付'
group by round(datediff(curdate(),birth)/356,0)
6、统计消费的二八法则,消费top20%的用户,贡献了多少额度?
这里需要分两个步骤:
①首先将将消费金额TOP20%的用户数量找出来
select ceil(count(1)* 0.2) from (
select userId,SUM(price) from orderinfo
where ispaid = '已支付'
group by userId) t
这里得出来的结果为17310是TOP20%用户的数量
②根据以上结果使用limit就可以算出来消费总金额
select sum(su) 消费总金额 from (
select userId,SUM(price) su from orderinfo
where ispaid = '已支付'
group by userId
order by sum(price) desc
limit 17130) t
根据二八法则我们可以知道这20%的用户贡献了80%的消费金额,持续关注这些用户能够让我们业务事半工倍。