一、知识点总结

把有用的知识写在前面有助与自己复习😊
1）排序窗口函数：有三种排序方式
  • rank() over() 1 2 2 4 4 6  (计数排名，跳过相同的几个，eg.没有3没有5)
  • row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
  • dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)


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

1、题目解读


求：找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，
按试卷完成数和用户ID降序排名。由示例数据结果输出如下：

解释：用户1006近三个有作答试卷的月份为202109、202108、202106，作答试卷数为3，全部完成；
用户1001近三个有作答试卷的月份为202109、202108、202107，作答试卷数为5，完成试卷数为4，因为有未完成试卷，故过滤掉。

题目中隐藏的坑
坑1:是对月份进行排序，需要对时间格式进行处理 date_format(start_time,'%Y-%m')
解释：用户1006近三个有作答试卷的月份为202109、202108、202106，作答试卷数为3，全部完成；
用户1001近三个有作答试卷的月份为202109、202108、202107，作答试卷数为5，完成试卷数为4，因为有未完成试卷，故过滤掉。

2、解题步骤拆分

1)找到每个人近三个有试卷作答记录的月份
 对每个用户的试卷作答记录的时间按照月份进行排序，取离当前最近的前3个月。
 
2)没有试卷是未完成状态的用户
 剔除在3个月中有未完成状态试卷的用户，即作答数=完成数

3)试卷作答完成数

4)按试卷完成数和用户ID降序排名

需求字段：uid    exam_complete_cnt

三、步骤代码

1)对每个用户的试卷作答记录的时间按照月份进行排序，取离当前最近的前3个月。
对相同的数，给相同的排名，且不跳过，例如 1 1 2 3 3 3 4 这样的排名方式，故而选择dense_rank() over() 函数进行排序
dense_rank()over(partition by uid order by start_time desc) AS rk 当rk<=3时满足题设需求。
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;

2）剔除在3个月中有未完成状态试卷的用户，即作答数=完成数
满足1)的前提下，count(start_time)=count(submit_time)

3)试卷作答完成数
count(submit_time) AS exam_complete_cnt
 
4)按试卷完成数和用户ID降序排名
order by exam_complete_cnt desc,uid desc

四、完整代码组装 

WITH t1 AS (
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
)

SELECT uid,count(submit_time) exam_complete_cnt
FROM t1
WHERE rk<=3 -- 选取排名前三的月份
GROUP BY uid
HAVING count(start_time)=count(submit_time) -- 没有题目未完成状态
ORDER BY exam_complete_cnt DESC,uid DESC -- 按照试卷完成数和用户ID降序排名
;