开窗SQL139|#近三个月未完成试卷数为0的用户完成情况#

#需求：找到每个人近三个有试卷作答记录的月份中,没有试卷是未完成状态的用户,的试卷作答完成数
#输出：uid、exam_complete_cnt(试卷完成作答数)
#拆分：对uid的筛选：有作答记录的近三个月中，没有试卷存在未完成记录。以dense_rank开窗，对作答时间的月份排名，筛选取排名<=3的
#各人的试卷作答完成数：count(submit_time)
#输出按exam_complete_cnt、uid降序：order by
select uid,count(submit_time)
from(
    select uid,submit_time,
    dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) ck1
    #要取近X个月的，所以对月份desc；同一用户一个月内可能多次答题，使用连续排名dense_rank
    from exam_record
) w1
where ck1<=3
#要求中参考的是用户的近3个月记录，只有1个月或2个月记录的也属于近3个月的范畴，所以用<=3筛选
group by 1
having count(uid)=count(submit_time)
#判断是否用户数等于完成作答数，其实就是where submit_time is not null的意思，这一步如果写在where中会漏筛数据，使uid1001的数据也被输出，所以写在having中
order by 2 desc,1 desc
#总结：两个坑，一是having筛选那部分比较难以理解，不如where直观，二是dense_rank中对时间的字段使用submit_time会导致uid1001的数据被输出，与答案不一致，改为start_time则没有问题，但并没有支持这一步的逻辑，只能归咎于出题有问题
#3/23补充：having 中不能用 submit_time is not null的原因，前面group by已经聚合，submit_time是作为count后的统计数输出的，只有数字没有null值；
#查看源数据表，uid1001的近三月数据共五条，其中7月有3条，有一条完成时间为null值，那么uid1001的数据不能被输出；所以对uid和submit_time计数会不相等，可以用第16行的having剔除
#dense_rank中使用start_time，相对是比较取巧的一点，这一步主要是对时间进行排序限制时间范围为近三月，使用start_time会忽视开始作答和完成时间跨月的情况；但在现有的代码中使用submit_time就会出现总结提到的问题；