题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
http://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
- 题目要求太多了，但是综合分析发现其实最后只是要一个uid，不论是要求作答试卷是SQL，还是未完成率排名前50%，以及要求是6/7级的大佬，最后都是要一个uid而已，所以这种情况下可以先求出需要的uid再使用where...in...去求解。当多个条件都很复杂时，需要用到join：
#查询满足条件的uid，可以连接查询。from里有三个表：
#从未完成数量表中找到的未完成率的排序表join查询出的distinct uid（作求解前50%未完成率uid用）join用户信息表
#根据三个表，给定where条件就可以查询出符合条件的uid了！
select incomplete_rate_table.uid from
#查询uid
(select uid, row_number() over(order by (incomplete_cnt/total_cnt) desc,uid desc) incomplete_rank
from(
#查询未完成题目数量和uid
select uid, sum(if(score is null,1,0)) incomplete_cnt,
count(start_time) total_cnt
from exam_recordwhere exam_id in (select exam_id from examination_info where tag = 'SQL')
group by uid) incomplete_cnt_table) incomplete_rate_table
#保证选出的uid符合要求：level>=6,未完成率的前50%,当条件较多时，可以用join
join (select count(distinct uid) total_user from exam_record) t_u
join user_info on incomplete_rate_table.uid = user_info.uid
where level >= 6
and incomplete_rank <= ceiling(total_user/2)
-
最后要求输出的结果是最近三个月的试卷作答记录，想求得最近三个月当然使用窗口函数（排序函数），这里用到dense_rank()函数，这个函数排序时不跳数字，可以保证即使有相同的月份但是最近三个月的月份也可以入选，最后使用ranking<=3即可。
select uid, date_format(start_time,'%Y%m') start_month,
count(start_time) total_cnt,
sum(if(score is not null,1,0)) complete_cnt
#以上部分是选出需要的字段
from (
select uid, start_time, score,
dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) months_ranking
from exam_record
)recent_tb
#以上部分是要查询的表格
where months_ranking <= 3
and uid in(
count(start_time) total_cnt,
sum(if(score is not null,1,0)) complete_cnt
#以上部分是选出需要的字段
from (
select uid, start_time, score,
dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) months_ranking
from exam_record
)recent_tb
#以上部分是要查询的表格
where months_ranking <= 3
and uid in(
)
#以上部分是uid需要满足的条件
group by start_month, uid
order by uid, start_month
#以上部分是uid需要满足的条件
group by start_month, uid
order by uid, start_month
