题解 | #每个6/7级用户活跃情况#

每个6/7级用户活跃情况

http://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1

一、知识点汇总与拓展

把有用的知识写在前面,以方便自个儿复习观看😊

1)查看每一个用户的情况,一般用GROUP BY 配合聚类函数进行查询。

a.group by的常见搭配:常和以下聚合函数搭配 

  •     avg()-- 求平均值
  •     count()-- 计数
  •     sum()-- 求和
  •     max() -- 最大值
  •     min()-- 最小值

b.group by 的进阶用法,和with rollup一起使用。 

2)常用的判断语句
a.简单条件判断语句
case when...(条件1)then...(结果A)else...(结果B) end 
if(条件1,结果A,结果B)
满足条件1则返回结果A,否则返回结果B
b.搜索式条件判断语句
case when 条件1 then 结果1
         when 条件2 then 结果2
         when 条件3 then 结果3
         ...
 else 结果n end
每个条件依次返回不同的结果

3)常用时间函数

a.常用的截取时间的函数有:year(),month(),day()
b.常用的时间差函数:timestampdiff(时间格式,开始时间,结束时间)
  • timestampdiff (minute,start_time,submit_time)<5
c.截取年月日等 date_format(时间字段,‘%年-%月-%日’)
  • date_format(submit_time,'%Y-%m-%d')
d.时间加减date_add(时间字段,interval n 时间格式)
  • date_add(start_time,interval 1 day) '加1天'

4)常用的连接
左连接:表1 left join 表2 on 表1.字段=表2.字段  (以表1为准,表2进行匹配)
右连接:表1 right join 表2 on 表1.字段=表2.字段  (以表2为准,表1进行匹配)
全连接:表1 union all 表2 (表1 和表2的列数必须一样多,union 去除重复项,union all 不剔除重复项)
内连接:表1 inner join 表2(取表1和表2相交部分)
外连接:表1 full outer join 表2 (取表1和表2不相交的部分)
ps:MYSQL 不支持外连接,可以用左右连接后再全连接代替

二、题目解读与解题步骤拆分

1、题目解读

题目:请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数。按照总活跃月份数、2021年活跃天数降序排序。

坑1:总活跃月份数的字段需求是历史全量数据,其他的皆为2021年
坑2:要查看每一个6/7级用户的活跃情况,哪怕数据为空的数据也需要体现。

examination_info(试卷信息表)
user_info(用户信息表)
exam_record(试卷作答记录表)
practice_record(题目练习记录表)

2、解题步骤拆分

求6/7级用户的:总活跃月份数(不做年份限制),2021年活跃天数,2021年试卷作答活跃天数,2021年答题活跃天数

因为既要查看试卷作答表又要查看题目练习表,故而选择把试卷作答表和题目练习表进行全连接。
依次拆分每一个字段的需求
1)用户的总活跃月份数
     COUNT(DISTINCT mon_time) act_total -- 对每个用户的活跃月份数据进行去重后计数
2)2021年活跃天数
    a.截取‘%年-%月-%日’字段:exam_record表 date_format(start_time,'%Y-%m-%d') as day_time; practice_record表 date_format(submit_time,'%Y-%m-%d') as day_time
    b.year(day_time)=2021
3)2021年试卷作答活跃天数
    对试卷进行定义'exam' as type
4)2021年答题活跃天数
    对练习题进行定义'practice' as type
5)6/7级用户
    user_info表:uid IN(‘6’,‘7’)
6)按照总活跃月份数、2021年活跃天数降序排序。
总活跃月份数DESC、2021年活跃天数降序DESC

三、步骤代码

STEP1:拼接exam_record 和 practice_record

SELECT uid,date_format(start_time,'%Y-%m-%d') day_time,date_format(start_time,'%Y-%m') mon_time,'exam' as type
FROM exam_record
UNION ALL
SELECT uid,date_format(submit_time,'%Y-%m-%d') day_time,date_format(submit_time,'%Y-%m') mon_time,'practice' as type
FROM practice_record

组装后的表格

STEP2:各个字段定义代码

查看每个用户的数据情况,故而以uid为关键字段进行聚类查看。
SELECT uid,字段1,字段2,... FROM 表1 GROUP BY uid

1)用户的总活跃月份数

2)2021年活跃天数
COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' then day_time ELSE NULL END)) act_2021 -- 对每个用户在2021年的活跃日数据进行去重后计数(比字段1多了个日期限制)
3)2021年试卷作答活跃天数
COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='exam' then day_time ELSE NULL END)) act_exam -- 对每个用户在2021年的作答类型为试卷的活跃日数据进行去重统计(比字段2多了个类型限制)
4)2021年答题活跃天数
COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='practice' then day_time ELSE NULL END)) act_practice -- 对每个用户在2021年的作答类型为答题的活跃日数据进行去重统计(和字段3相似)
5)6/7级用户
SELECT uid FROM user_info WHERE uid IN('6','7')

四、完整代码组装

WITH t1 AS (
SELECT uid,date_format(start_time,'%Y-%m-%d') day_time,date_format(start_time,'%Y-%m') mon_time,'exam' as type
FROM exam_record
union all
SELECT uid,date_format(submit_time,'%Y-%m-%d') day_time,date_format(submit_time,'%Y-%m') mon_time,'practice' as type
FROM practice_record
)
/* 全连接 exam_record表和practice_record表,并重命名为t1*/
SELECT t2.uid,
	COUNT(DISTINCT mon_time) act_total,
    COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' then day_time ELSE NULL END)) act_2021,
    COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='exam' then day_time ELSE NULL END)) act_exam,
    COUNT( DISTINCT (CASE WHEN YEAR(day_time)='2021' AND type='practice' then day_time ELSE NULL END)) act_practice
FROM t1
RIGHT JOIN user_info  t2 ON t1.uid=t2.uid
/*因为要查看每一个6/7级用户的情况,故而右连user_info表*/
WHERE t2.level IN('6','7')
GROUP BY uid
ORDER BY act_total DESC,act_2021 DESC
/*按照总活跃月份数、2021年活跃天数降序排序*/
;
SQL解题集 文章被收录于专栏

这是牛客SQL相关的解题集

全部评论
step1真得太太太聪明了!
点赞 回复 分享
发布于 2023-04-25 22:53 上海
WHERE t2.level IN('6','7'),这里6,7需要打引号吗?
点赞 回复 分享
发布于 2023-02-21 16:56 广东
活跃天数为什么只对日期去重?为什么不像17题一样,对 uid和date_format同时去重:round((count distinct uid, date_format(submit_time, '%y%m%d')) / count(distinct uid), 2)
点赞 回复 分享
发布于 2022-06-21 19:00
为什么是right join join 为什么不通过全部用例?
点赞 回复 分享
发布于 2022-05-20 15:57

相关推荐

06-27 12:54
已编辑
门头沟学院 Java
累了,讲讲我的大学经历吧,目前在家待业。我是一个二本院校软件工程专业。最开始选专业是觉得计算机感兴趣,所以选择了他。本人学习计算机是从大二暑假结束开始的,也就是大三开始。当时每天学习,我个人认为Java以及是我生活的一部分了,就这样持续学习了一年半,来到了大四上学期末,大概是在12月中旬,我终于找的到了一家上海中厂的实习,但我发现实习生的工作很枯燥,公司分配的活也不多,大多时间也是自己在自学。就这样我秋招末才找到实习。时间来到了3月中旬,公司说我可以转正,但是转正工资只有7000,不过很稳定,不加班,双休,因为要回学校参加答辩了,同时当时也是心高气傲,认为可以找到更好的,所以放弃了转正机会,回学校准备论文。准备论文期间就也没有投递简历。然后时间来到了5月中旬,这时春招基本也结束了,然后我开始投递简历,期间只是约到了几家下场面试。工资也只有6-7k,到现在我不知道该怎么办了。已经没有当初学习的心劲了,好累呀,但是又不知道该干什么去。在家就是打游戏,boss简历投一投。每天日重一次。26秋招都说是针对26届的人,25怎么办。我好绝望。要不要参加考公、考研、央国企这些的。有没有大佬可以帮帮我。为什么感觉别人找工作都是顺其自然的事情,我感觉自己每一步都在艰难追赶。八股文背了又忘背了又忘,我每次都花很长时间去理解他,可是现在感觉八股、项目都忘完了。真的已经没有力气再去学习了。图片是我的简历,有没有大哥可以指正一下,或者说我应该走哪条路,有点不想在找工作了。
码客明:太累了就休息一下兄弟,人生不会完蛋的
如果实习可以转正,你会不...
点赞 评论 收藏
分享
点赞 评论 收藏
分享
评论
48
12
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务