mysql 案例_MySQL学习小结+案例分析

*本文数据来自秦路的数据分析课，工具为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(备注从哪看到的我)