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

0.知识点

percent_rank() over() 按照数字所在的位置进行百分位分段

select * ,
    row_number()over(order by uid) row_number1, /*按照uid的大小不重不漏1 2 3 4 5 6 7 */
    rank()over(order by uid) rank1, /*按照uid的大小并列第一无第二，1 1 1 4 5 5 7*/
    dense_rank()over(order by uid) dense_rank1,/*按照uid的大小并列第一有第二，1 1 1  2 3 3 4*/
    percent_rank()over(order by uid) percent_rank1,/*按照uid的大小进行百分法排序*/
    ntile(2)over(order by uid) ntile1,/*按照uid的大小，把uid评价分成2组*/
    lead(uid)over(order by uid) lead1,/*把uid向上推1个位置*/
    lag(uid)over(order by uid) lag1 /*把uid向下推1个位置*/    
from user_id;

1.给未完成率排序

Select er.uid,
          percent_rank() over ( order by 
        count(submit_time)/count(start_time))  as pr
          from exam_record as er
          join examination_info as ei
          on er.exam_id=ei.exam_id
          where tag='SQL'
          group by uid

2.取未完成率较高的50%

Select uid
     from 
         (Select er.uid,
          percent_rank() over ( order by 
        count(submit_time)/count(start_time))  as pr
          from exam_record as er
          join examination_info as ei
          on er.exam_id=ei.exam_id
          where tag='SQL'
          group by uid) as q1
     where pr<=0.5

3.取6.7级用户

select q2.uid
from 
    (Select uid
     from 
         (Select er.uid,
          percent_rank() over ( order by 
        count(submit_time)/count(start_time))  as pr
          from exam_record as er
          join examination_info as ei
          on er.exam_id=ei.exam_id
          where tag='SQL'
          group by uid) as q1
     where pr<=0.5) as q2
join user_info as ui
On q2.uid=ui.uid
Where level=6 or level=7

4.组装

select uid,start_month,count(start_time) as total_cnt,
count(submit_time) as complete_cnt
from
    (select *,date_format(start_time,'%Y%m') as start_month,
     dense_rank() over (partition by uid order by
            date_format(start_time,'%Y%m') desc) as rn
    from exam_record ) as q
where rn<=3 and uid in
(select q2.uid
from 
    (Select uid
     from 
         (Select er.uid,
          percent_rank() over ( order by 
        count(submit_time)/count(start_time))  as pr
          from exam_record as er
          join examination_info as ei
          on er.exam_id=ei.exam_id
          where tag='SQL'
          group by uid) as q1
     where pr<=0.5) as q2
join user_info as ui
On q2.uid=ui.uid
Where level=6 or level=7
)
group by uid,start_month
order by uid,start_month