*本文数据来自秦路的数据分析课,工具为Mac+Sequel Pro
最近复习了一下SQL语言,虽然也可以通过sqlzoo和leetcode网站的题目来复习(sqlzoo题目不错,需要答案的可以联系我~微:q734550709),但是总觉得通过实际案例来掌握一个技能更合适。所以,找了一下课程,发现秦路的数据分析课里有不错的案例,整理一下,希望对大家的学习有帮助~
废话不多说,题目要求如下:
先说明一下,数据表有两张,orderinfo和userinfo:orderinfouserinfo
一、统计不同月份的下单人数
这一题比较简单,注意使用distinct去重即可:
select DATE_FORMAT(paidTime, '%Y-%m') month,
count(distinct userId) paid_num
from orderinfo
where isPaid = '已支付'
group by month
结果如下:
二、统计用户三月份的回购率和复购率
这一题我十分喜欢,首先是和实际情况结合很好,同时也比较考验实力。
1、先看一下复购率
第一步把三月份已支付的人和支付次数找出来:
select userId, count(userId) ct from orderinfo
where isPaid = '已支付'
and month(paidTime) = 3
group by userId
然后将该结果作为子表,对总购买人次和复购人次分列显示:
select count(ct) total, count(if (ct>1,1,null)) repurchase_3 from
(select userId, count(userId) ct from orderinfo
where isPaid = '已支付'
and month(paidTime) = 3
group by userId) month_3_paid
当然,你可以思考一下,如果求多个月的复购率,有没有什么更好的办法呢?
2、然后是回购率
第一步是查询每个用户消费的月份:
select userId,DATE_FORMAT(paidTime, '%Y-%m-01') paytime
from orderinfo
where isPaid = '已支付'
group by userId, paytime
之后,将得到的表作为子表,left join一下,通过on筛选差1的结果,可以得到:
select * from
(select userId,DATE_FORMAT(paidTime, '%Y-%m-01') paytime
from orderinfo
where isPaid = '已支付'
group by userId, paytime) t1
left join
(select userId,DATE_FORMAT(paidTime, '%Y-%m-01') paytime
from orderinfo
where isPaid = '已支付'
group by userId, paytime) t2
on t1.userId = t2.userId and t1.paytime=date_sub(t2.paytime,interval 1 month)
也就是说,后面两列显示的是该用户是否回购以及在哪个月回购的结果。
select t1.paytime,count(t1.paytime) first, count(t2.paytime) next,
count(t2.paytime)/count(t1.paytime) Repurchase_rate
from
(select userId,DATE_FORMAT(paidTime, '%Y-%m-01') paytime
from orderinfo
where isPaid = '已支付'
group by userId, paytime) t1
left join
(select userId,DATE_FORMAT(paidTime, '%Y-%m-01') paytime
from orderinfo
where isPaid = '已支付'
group by userId, paytime) t2
on t1.userId = t2.userId and t1.paytime=date_sub(t2.paytime,interval 1 month)
group by t1.paytime
最后加一下group by,就可以把每个月的回购情况显示出来了:
看到这里是不是明白了?上一个复购率也可以这么做哦。不过这个方法也有不足之处,你有什么好办法改进吗?
三、统计男女用户的消费频次是否有差异
这一题涉及到关联两张表:
第一步、过滤性别为空的结果:
select * from userinfo
where sex <> ''
第二步、关联两张表
select * from orderinfo o
join
(select * from userinfo
where sex <> '') t
on o.userId = t.userId
第三步、对关联后的两张表按照userId和性别分组
select o.userId, sex, count(1) num from orderinfo o
join
(select * from userinfo
where sex <> '') t
on o.userId = t.userId
group by o.userId, sex
第四步、将结果作为子表查询不同性别消费频次
select sex, avg(num) feq from
(select o.userId, sex, count(1) num from orderinfo o
join
(select * from userinfo
where sex <> '') t
on o.userId = t.userId
group by o.userId, sex) t2
group by sex
这里分享一个小窍门,如果你觉得写SQL太复杂,试着像这样分布去写会有意想不到的惊喜。
四、统计多次消费的用户,第一次和最后一次消费间隔是多少
这题从简,看看你能不能看懂咯:
select userId, max(paidTime), min(paidTime),
datediff(max(paidTime),min(paidTime)) day_diff
from orderinfo
where isPaid = '已支付'
group by userId having count(1)>1
五、统计不同年龄段,用户的消费金额是否有差异
同上,看不懂的可以联系我
select age, sum(comsum) total_com
from(
select o.userId,
ceil((year(now())-year(birth))/10) age
, sum(price) comsum #ceil向上取整
from
orderinfo o
join
userinfo u
on o.userId = u.userId
where birth > '1900-00-00' #筛选去除无效年龄
group by u.userId
) t
group by t.age
六、统计消费的二八法制,消费的top20%用户,贡献了多少额度
最后这一题我也很喜欢,既可以体现分步的重要还可以很好说明业务的情况。
第一步、计算消费总额,倒序排列
select userId, sum(price) total from orderinfo
where isPaid = '已支付'
group by userId
order by total desc
这里可以count一下userId,得到一共的用户数量,20%的用户也能找到咯(这里是151)。
第二步、计算前20%的用户人数和消费总额
select count(1), sum(total) from
(select userId, sum(price) total from orderinfo
where isPaid = '已支付'
group by userId
order by total desc
limit 151) t1
第三步、计算前20%用户消费占比
select sum(t2.total) sum1, sum(t1.total) sum2,
sum(t2.total) /sum(t1.total) rate from
(select userId, sum(price) total from orderinfo
where isPaid = '已支付'
group by userId
order by total desc)t1
left join
(select userId, sum(price) total from orderinfo
where isPaid = '已支付'
group by userId
order by total desc
limit 151) t2
on t1.userId = t2.userId
果然,20-80法则很正确呀。
以上~
这次没准备彩蛋,确实算有的话就是sqlzoo的练习题答案了。需要的联系我~微:q734550709(备注从哪看到的我)