题解 139 | #近三个月未完成试卷数为0的用户完成情况#

【场景】：近几个月

【分类】：专用窗口函数、子查询

分析思路

难点：

1.如何求有作答记录的近三个月（区别连续三个月）

（1）查询每个用户作答试卷最近月份的信息

• [使用]：dense_rank() 不会跳过重复的序号 例如：1,1,2,3

（2）找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名

排名前三的月份就是近三个月

• [条件]：count(start_time) = count(submit_time) 去除有试卷未完成的用户

• [使用]：用group by 对uid分组求count得到exam_complete_cnt

最终结果

select 查询结果 [用户ID;试卷作答完成数]
from 从哪张表中查询数据[子查询]
where 查询条件 [排名小于等于3]
group by 分组条件 [用户ID] having 判断条件 [完成试卷]
order by 按照指定条件排序 [试卷作答完成数降序;用户ID降序]

扩展

前往查看： MySQL 窗口函数

求解代码

方法一

with子句

with
    main as(
        #查询每个用户作答试卷最近月份的信息
        select 
            uid,
            start_time,
            submit_time,
            dense_rank() over (partition by uid order by date_format(start_time, '%Y%m') desc) as ranking
        from exam_record
    )
#找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名
select 
    uid,
    count(uid) as exam_complete_cnt
from main
where ranking <= 3
group by uid having count(start_time) = count(submit_time)
order by exam_complete_cnt desc, uid desc

方法二

from子查询

#找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数，按试卷完成数和用户ID降序排名
select 
    uid,
    count(uid) 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 ranking
    from exam_record
) main
where ranking <= 3
group by uid having count(start_time) = count(submit_time)
order by exam_complete_cnt desc, uid desc