题解 | 子查询-未完成率较高的50%用户近三个月答卷情况

# 查询有SQL试卷答题记录的用户的id号、等级和其未完成率（只需关注有答题记录的SQL试卷，使用内连接）
select u.uid, u.level, avg(submit_time is null) incomplete_rate
from examination_info ei
join exam_record er
on ei.exam_id=er.exam_id
join user_info u
on er.uid=u.uid
where ei.tag='SQL'
group by u.uid

# 将用户id按其未完成率排序，并选出前50%的用户号及等级（使用窗口函数+percent_rank()函数）
select uid, level
from (
    select uid, level, percent_rank()over(order by incomplete_rate desc) rank1
    from (
        select u.uid, u.level, avg(submit_time is null) incomplete_rate
        from examination_info ei
        join exam_record er
        on ei.exam_id=er.exam_id
        join user_info u
        on er.uid=u.uid
        where ei.tag='SQL'
        group by u.uid
    ) k1
)k2
where rank1 <=0.5

# （1）筛选出未完成率较高的用户中的6和7级用户
select uid
from(
    select uid, level
    from (
        select uid, level, percent_rank()over(order by incomplete_rate desc) rank1
        from (
            select u.uid, u.level, avg(submit_time is null) incomplete_rate
            from examination_info ei
            join exam_record er
            on ei.exam_id=er.exam_id
            join user_info u
            on er.uid=u.uid
            where ei.tag='SQL'
            group by u.uid
        ) k1
    )k2
    where rank1 <=0.5
) k3
where level=6 or level=7

# 找出（1）中各用户有试卷作答记录的各月里，每个月的答卷数量及完成数量，答题月份标记
select uid, date_format(start_time, '%Y%m') start_month, count(start_time) total_cnt, count(submit_time) complete_cnt, min(start_time) st_tag
from exam_record er
where uid in (
    select uid
    from(
        select uid, level
        from (
            select uid, level, percent_rank()over(order by incomplete_rate desc) rank1
            from (
                select u.uid, u.level, avg(submit_time is null) incomplete_rate
                from examination_info ei
                join exam_record er
                on ei.exam_id=er.exam_id
                join user_info u
                on er.uid=u.uid
                where ei.tag='SQL'
                group by u.uid
            ) k1
        )k2
        where rank1 <=0.5
    ) k3
    where level=6 or level=7
)
group by uid, date_format(start_time, '%Y%m')
# 在找出（1）中各用户有试卷作答记录的各月里，每个月的答卷数量及完成数量，答题月份标记的基础上，为各用户的近期答题月份按从后往前排序编号
select *, rank()over(partition by uid order by st_tag desc) rank_yue
from (
    select uid, date_format(start_time, '%Y%m') start_month, count(start_time) total_cnt, count(submit_time) complete_cnt, min(start_time) st_tag
    from exam_record er
    where uid in (
        select uid
        from(
            select uid, level
            from (
                select uid, level, percent_rank()over(order by incomplete_rate desc) rank1
                from (
                    select u.uid, u.level, avg(submit_time is null) incomplete_rate
                    from examination_info ei
                    join exam_record er
                    on ei.exam_id=er.exam_id
                    join user_info u
                    on er.uid=u.uid
                    where ei.tag='SQL'
                    group by u.uid
                ) k1
            )k2
            where rank1 <=0.5
        ) k3
        where level=6 or level=7
    )
    group by uid, date_format(start_time, '%Y%m')
) k4
# 按月份编号筛选（1）中各用户近三个月的信息，并按题目条件进行排序
select uid, start_month, total_cnt, complete_cnt, rank_yue
from (
    select *, rank()over(partition by uid order by st_tag desc) rank_yue
    from (
        select uid, date_format(start_time, '%Y%m') start_month, count(start_time) total_cnt, count(submit_time) complete_cnt, min(start_time) st_tag
        from exam_record er
        where uid in (
            select uid
            from(
                select uid, level
                from (
                    select uid, level, percent_rank()over(order by incomplete_rate desc) rank1
                    from (
                        select u.uid, u.level, avg(submit_time is null) incomplete_rate
                        from examination_info ei
                        join exam_record er
                        on ei.exam_id=er.exam_id
                        join user_info u
                        on er.uid=u.uid
                        where ei.tag='SQL'
                        group by u.uid
                    ) k1
                )k2
                where rank1 <=0.5
            ) k3
            where level=6 or level=7
        )
        group by uid, date_format(start_time, '%Y%m')
    ) k4
) k5
where rank_yue <= 3
order by uid, rank_yue desc

# 最后按上面查询结果表继续查询，但只查询出题目规定字段
select uid, start_month, total_cnt, complete_cnt
from(
    select uid, start_month, total_cnt, complete_cnt, rank_yue
    from (
        select *, rank()over(partition by uid order by st_tag desc) rank_yue
        from (
            select uid, date_format(start_time, '%Y%m') start_month, count(start_time) total_cnt, count(submit_time) complete_cnt, min(start_time) st_tag
            from exam_record er
            where uid in (
                select uid
                from(
                    select uid, level
                    from (
                        select uid, level, percent_rank()over(order by incomplete_rate desc) rank1
                        from (
                            select u.uid, u.level, avg(submit_time is null) incomplete_rate
                            from examination_info ei
                            join exam_record er
                            on ei.exam_id=er.exam_id
                            join user_info u
                            on er.uid=u.uid
                            where ei.tag='SQL'
                            group by u.uid
                        ) k1
                    )k2
                    where rank1 <=0.5
                ) k3
                where level=6 or level=7
            )
            group by uid, date_format(start_time, '%Y%m')
        ) k4
    ) k5
    where rank_yue <= 3
    order by uid, rank_yue desc
) k6;

以上思路较长，但思路分解较为清晰，主要是每一步都把上一步的子查询嵌进去了，所以显得长

/*完整代码，嵌套多个子循环*/
# 如果没有分步去做，真的写不出来。。
select uid, start_month, total_cnt, complete_cnt
from(
    select uid, start_month, total_cnt, complete_cnt, rank_yue
    from (
        select *, rank()over(partition by uid order by st_tag desc) rank_yue
        from (
            select uid, date_format(start_time, '%Y%m') start_month, count(start_time) total_cnt, count(submit_time) complete_cnt, min(start_time) st_tag
            from exam_record er
            where uid in (
                select uid
                from(
                    select uid, level
                    from (
                        select uid, level, percent_rank()over(order by incomplete_rate desc) rank1
                        from (
                            select u.uid, u.level, avg(submit_time is null) incomplete_rate
                            from examination_info ei
                            join exam_record er
                            on ei.exam_id=er.exam_id
                            join user_info u
                            on er.uid=u.uid
                            where ei.tag='SQL'
                            group by u.uid
                        ) k1
                    )k2
                    where rank1 <=0.5
                ) k3
                where level=6 or level=7
            )
            group by uid, date_format(start_time, '%Y%m')
        ) k4
    ) k5
    where rank_yue <= 3
    order by uid, rank_yue desc
) k6;