产品能力提升之mysql练习题（1）

vlambda
2020-06-19

产品能力提升之mysql练习题（1）

这是记录日常学习的第一篇总结

最近在B站学习数据分析的课程，为了加深自己的印象以及督促自己每天能有所学习，为此将进行每日总结；

首先需要导入两张表到数据库中，分别为orderinfo、userinfo，再针对表中内容进行相应的查询；

产品能力提升之mysql练习题（1）

题目一、统计不同月份的下单人数

思路：

找出要查询的表是哪张-->再从表中获取月份数据并分组-->表中含有‘未支付的数据’，所以再筛选出已支付的用户-->一个用户可能一月里多次下单，所以需要对已支付用户去重后再统计数量

语句：

SELECT MONTH (paidTime), COUNT(DISTINCT userID)#用户去重并计数FROM DATA .orderinfo#查询表WHERE isPaid = '已支付'#筛选出已支付的用户GROUP BY MONTH (paidTime)#获取月份数据并分组

结果：

产品能力提升之mysql练习题（1）

题目二、统计用户三月份的回购率和复购率

说明：

回购率指用4月份还买了的用户数/3月份买了的用户数

复购率指3月份下单次数大于1的用户数/3月份下过单的总用户数

回购率思路（由于没有两张表，所以只能做嵌套查询）：

找出要查询的表是哪张-->再从表中筛选出当月购买数据-->表中含有‘未支付的数据’，所以再筛选出已支付的用户-->进行多表关联并筛选出大一月的数据-->最后统计3月份的购买人数和4月的购买人数

语句：

SELECT t1.m,#月份 COUNT(t1.m),#当月购买人数 COUNT(t2.m)#次月还购买的人数FROM ( SELECT userId, DATE_FORMAT(paidTime, '%Y-%m-01') AS m#规范日期格式 FROM DATA .orderinfo WHERE isPaid = '已支付' GROUP BY userID, DATE_FORMAT(paidTime, '%Y-%m-01')#按月统计用户是否有过消费 ) t1LEFT JOIN (#两表进行关联 SELECT userId, DATE_FORMAT(paidTime, '%Y-%m-01') AS m FROM DATA .orderinfo WHERE isPaid = '已支付' GROUP BY userID, DATE_FORMAT(paidTime, '%Y-%m-01')) t2 ON t1.userId = t2.userIdAND t1.m = DATE_SUB(t2.m, INTERVAL 1 MONTH)#限制区间，t1的月份一定是要比t2的月份小1个月GROUP BY t1.m#设置主要列

结果：

产品能力提升之mysql练习题（1）

复购率思路：

找出要查询的表是哪张-->再从表中筛选出每个用户当月是否消费过并分组-->表中含有‘未支付的数据’，所以再筛选出已支付的用户-->再统计当月支付成功的总用户数-->再统计当月支付次数大于1的用户数-->最后两者相除得出三月份的复购率

语句：

SELECT COUNT(ct),#总下单人数 COUNT(IF(ct > 1, 1, NULL)),#下单次数大于1的人数 COUNT(IF(ct > 1, 1, NULL)) / COUNT(ct)#复购率FROM ( SELECT userID, COUNT(userID) AS ct#对用户下单的次数计数，ct指重新命名 FROM DATA .orderinfo WHERE isPaid = '已支付'#筛选出已支付的用户 AND MONTH (paidTime) = 3#筛选出3月份的数据 GROUP BY userID#下单用户分组 ) t#t代表重命名

结果：

题目三：统计男女用户的消费频次是否有差异

思路：

找出要查询的表是哪张-->再从表中过滤掉空值（无效数据）-->进行多表关联统计不同用户的消费频次-->最后统计不同性别的消费平均数

语句：

SELECT sex,avg(ct) from(#avg指男女用户用户的消费频次 SELECT o.userID,sex,COUNT(1) as ct from data.orderinfo o#筛选出每个用户的消费频次 INNER JOIN (#inner的作用指两表之间的交集 SELECT * FROM data.userinfo WHERE sex <> '') t#过滤掉空值 ON o.userID = t.userID GROUP BY userID,sex) t2GROUP BY sex#设置主要列并分组

结果：

题目来源：B站

end

vlambda博客
学习文章列表