题解 | #近三个月未完成试卷数为0的用户完成情况#
近三个月未完成试卷数为0的用户完成情况
https://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa
函数
- date_format(start_time, '%Y%m'): 可以取出来年份和月份进行排序
坑
看了下题解下的评论
- 近三个有记录的月份,用户只有1个月和2个月的记录也应该算的。
解题步骤
找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数,按试卷完成数和用户ID降序排名
方法一:这是一开始自己的方法,总之就是复杂了
1 找到近三个有试卷作答记录的月份
详细:开窗,按uid分区,year,month排序,取最新的年份和最新的三个月的月份,要统计这三个月所有的试卷,用dense_rank ,这个表作为t表
SELECT uid, start_time, submit_time, DENSE_RANK() OVER(PARTITION BY uid ORDER BY YEAR(start_time) DESC, MONTH(start_time) DESC) AS rk FROM exam_record
2 取前三位
SELECT uid, start_time, submit_time FROM t WHERE t.rk <= 3
3 把submit_time is null 的uid过滤掉,计算每个账号近三个有试卷记录的记录数,计算每个账号近三个有试卷记录的记录数
详细:
- 筛选出submit_time is null的uid
SELECT DISTINCT uid FROM t WHERE t.rk <= 3 AND t.submit_time IS NULL
- 按uid not in (submit_time is null的uid)过滤
SELECT uid, COUNT(*) AS exam_complete_cnt FROM t WHERE t.rk <= 3 AND uid NOT IN ( SELECT DISTINCT uid FROM t WHERE t.rk <= 3 AND t.submit_time IS NULL ) GROUP BY uid ORDER BY uid, exam_complete_cnt
4 拼接
SELECT uid, COUNT(*) AS exam_complete_cnt FROM ( SELECT uid, start_time, submit_time, DENSE_RANK() OVER( PARTITION BY uid ORDER BY YEAR(start_time) DESC, MONTH(start_time) DESC ) AS rk FROM exam_record ) t WHERE t.rk <= 3 AND uid NOT IN ( SELECT DISTINCT uid FROM ( SELECT uid, start_time, submit_time, DENSE_RANK() OVER( PARTITION BY uid ORDER BY YEAR(start_time) DESC, MONTH(start_time) DESC ) AS rk FROM exam_record ) t WHERE t.rk <= 3 AND t.submit_time IS NULL ) GROUP BY uid ORDER BY exam_complete_cnt desc, uid desc
优化
第1步:优化1:可以用date_format函数:date_format(start_time, '%Y%m') 直接取出来年份和月份进行排序,不用用YEAR(start_time) DESC, MONTH(start_time) DESC排两次
SELECT uid, COUNT(*) AS exam_complete_cnt FROM ( SELECT uid, start_time, submit_time, DENSE_RANK() OVER( PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') desc ) AS rk FROM exam_record ) t WHERE t.rk <= 3 AND uid NOT IN ( SELECT DISTINCT uid FROM ( SELECT uid, start_time, submit_time, DENSE_RANK() OVER( PARTITION BY uid ORDER BY YEAR(start_time) DESC, MONTH(start_time) DESC ) AS rk FROM exam_record ) t WHERE t.rk <= 3 AND t.submit_time IS NULL ) GROUP BY uid ORDER BY exam_complete_cnt desc, uid desc
第3步:优化2:不用这么像第3步那么麻烦,直接筛选出count(submit_time) = count(start_time),就是近三个有记录的月份未提交试卷数为0的用户
优化后代码:
SELECT uid, COUNT(submit_time) AS exam_complete_cnt FROM( SELECT uid, start_time, submit_time, DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') DESC) AS rk FROM exam_record) t WHERE rk <= 3 GROUP BY uid HAVING COUNT(start_time) = COUNT(submit_time) #筛选出近三个有卷子记录的月份未完成试卷数为0的记录 ORDER BY exam_complete_cnt desc, uid desc