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

# 第一步，先找出未完成率前50%高的用户ID，注意这里需要的sql试卷
with rote_tab as 
(select t.uid,t.f_rote,row_number()over(order by t.f_rote desc,uid) as rank2
,count(t.uid)over(partition by t.tag)as cnt
from (select er.uid,ef.tag,(sum(if(submit_time is null,1,0))/count(start_time)) as f_rote
from exam_record er left join examination_info ef on ef.exam_id=er.exam_id where tag='SQL' group by uid ) t)


select  #第四步，分用户和月份进行数据统计；同时需要注意，统计的试卷数是所有类型的，不是之前仅有SQL类型
    uid
    ,start_month
    ,count(start_time) as total_cnt
    ,count(submit_time) as complete_cnt
from 
(
select # 第三步，利用窗口函数对每个用户的月份进行降序排序，以便找出最近的三个月；
    uid
    ,start_time
    ,submit_time
    ,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 rank3
from exam_record 
where uid in 
    (select distinct er.uid
    from exam_record er left join user_info uf on uf.uid=er.uid
    where er.uid in (select uid from rote_tab where rank2<=round(cnt/2,0))
    and uf.level in (6,7))  # 第二步，进一步找出满足等级为6或7的用户ID
) t2
where rank3<=3
group by uid,start_month
order by uid,start_month

【场景】：等级值百分比、近几个月

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

分析思路

难点：

统计SQL试卷用户未完成率中的百分比排位使用percent_rank()

（1）统计SQL试卷未完成率

• [条件]：tag = 'SQL'
• [使用]：join ;count(); if()

（2）统计SQL试卷用户未完成率中的百分比排位

• [使用]：percent_rank()

（3）统计SQL试卷用户未完成率中的百分比排位中大于50%，并且是6级和7级用户有试卷作答记录的近三个月

• [使用]：dense_rank()；date_format(start_time,'%Y%m')

（4）最终统计每个用户每个月的答卷数目和完成数目

• [使用]：where ranking <= 3；group by uid,start_month；order by uid,start_month

最终结果

select 查询结果 [用户ID;月份;答题数目;完成数目]
from 从哪张表中查询数据[多个join连接的表]
where 查询条件 [百分比排位中大于50%]
group by 分组条件 [用户ID;月份]
order by 对查询结果排序 [用户ID、月份升序];

扩展：

保留日期中年月的方法

1. substring(start_time,1,7);
2. replace(substring(start_time,1,7),'-','');
3. date_format(start_time,'%Y%m')
4. date(start_time)

求解代码

方法一

with子句

with 
    temp as(
        #统计SQL试卷未完成率
        select
            uid,
            count(if(submit_time is null,1,null)) as incomplete_cnt,#未完成数
            count(start_time) as total_cnt, #作答总数
            count(if(submit_time is null,1,null))/count(start_time) as incomplete_rate #未完成率
        from user_info
        join exam_record using(uid)
        join examination_info using(exam_id)
        where tag = 'SQL'
        group by uid
    )
    ,temp1 as(
        #统计SQL试卷用户未完成率中的百分比排位
        select 
            uid,
            percent_rank() over(order by incomplete_rate) as nt_complete
        from temp
    )
    ,temp2 as(
        #6级和7级用户并且有试卷作答记录的近三个月
        select 
            uid,
            start_time,
            submit_time,
            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 ranking
        from exam_record
        where uid in (
            select 
                uid
            from temp1
            join user_info using(uid)
            join exam_record using(uid)
            where nt_complete >= 0.5
            and level between 6 and 7
        )
    )
#最终统计每个用户每个月的答卷数目和完成数目
select
    uid,
    start_month,
    count(start_time) as total_cnt,
    count(if(submit_time is null,null,1)) as complete_cnt
from temp2
where ranking <= 3
group by uid,start_month
order by uid,start_month

优化代码

同时进行未完成率的筛选和百分比排位

with 
    temp as(
        #统计SQL试卷用户未完成率中的百分比排位
        select 
            uid,
            percent_rank() over(order by count(if(submit_time is null,1,null))/count(start_time)) as nt_complete
        from user_info
        join exam_record using(uid)
        join examination_info using(exam_id)
        where tag = 'SQL'
        group by uid
    )
    ,temp1 as(
        #6级和7级用户并且有试卷作答记录的近三个月
        select 
            uid,
            start_time,
            submit_time,
            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 ranking
        from exam_record
        where uid in (
            select 
                uid
            from temp
            join user_info using(uid)
            join exam_record using(uid)
            where nt_complete >= 0.5
            and level between 6 and 7
        )
    )
#最终统计每个用户每个月的答卷数目和完成数目
select
    uid,
    start_month,
    count(start_time) as total_cnt,
    count(if(submit_time is null,null,1)) as complete_cnt
from temp1
where ranking <= 3
group by uid,start_month
order by uid,start_month

temp 表输出结果：

1    1001|0.000    
2    1002|0.500    
3    1003|1.000    

temp1 表输出结果：

1    1002|2020-05-06 12:01:01|None|202005|1    
2    1002|2020-05-02 19:01:01|2020-05-02 19:32:00|202005|1    
3    1002|2020-03-02 12:11:01|None|202003|2    
4    1002|2020-03-01 12:01:01|2020-03-01 12:41:01|202003|2        
5    1002|2020-02-02 12:01:01|2020-02-02 12:43:01|202002|3        
6    1002|2020-02-02 12:01:01|None|202002|3        
7    1002|2020-02-01 12:11:01|None|202002|3        
8    1002|2020-01-01 18:01:01|2020-01-01 18:59:02|202001|4        
9    1002|2020-01-20 10:01:01|None|202001|4

方法二

嵌套子查询

#最终统计每个用户每个月的答卷数目和完成数目
select
    uid,
    start_month,
    count(start_time) as total_cnt,
    count(if(submit_time is null,null,1)) as complete_cnt
from(
    #6级和7级用户并且有试卷作答记录的近三个月
    select 
        uid,
        start_time,
        submit_time,
        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 ranking
    from exam_record
    where uid in (
        select 
            uid
        from(
            #统计SQL试卷用户未完成率中的百分比排位
            select 
                uid,
                percent_rank() over(order by count(if(submit_time is null,1,null))/count(start_time)) as nt_complete
            from user_info
            join exam_record using(uid)
            join examination_info using(exam_id)
            where tag = 'SQL'
            group by uid
        ) temp
        join user_info using(uid)
        join exam_record using(uid)
        where nt_complete >= 0.5
        and level between 6 and 7
    )
) temp1
where ranking <= 3
group by uid,start_month
order by uid,start_month

SELECT uid, DATE_FORMAT(start_time, '%Y%m') start_month, count(uid) total_cnt, count(score) complete_cnt
FROM
(SELECT b.*, DENSE_RANK() over (partition by uid order by DATE_FORMAT(start_time, '%Y%m') desc) ranking
FROM user_info a LEFT JOIN exam_record b
ON a.uid = b.uid
WHERE a.level in (6,7) AND a.uid in
(SELECT uid
from
(SELECT b.uid, PERCENT_RANK() over (partition by a.tag order by ROUND((COUNT(b.uid) - COUNT(b.SCORE)) / COUNT(b.uid), 4)) ranking
FROM examination_info a left join exam_record b
on a.exam_id = b.exam_id
where a.tag = 'SQL'
group by b.uid) a
WHERE ranking >= 0.5)) a
where ranking <= 3
group by uid, start_month
order by uid, start_month

select t1.uid,t1.month_d,count(*)as total_cnt,count(t1.submit_time) as complete_cnt from

    -- 先求出未完成率的排名

    (select uid,count(submit_time is null or null)/count(start_time) as num,

    PERCENT_RANK() over(order by count(submit_time is null or null)/count(start_time) ) as ranking

    from exam_record left join examination_info using(exam_id)

    where tag = 'SQL'

    group by uid)t

inner join (

    -- 再求出近三个月的练习记录

    select uid,date_format(start_time,'%Y%m') as month_d ,submit_time,exam_id

    ,dense_rank() over( partition by uid order by date_format(start_time,'%Y%m')  desc ) as ranking

    from exam_record

 left join user_info using(uid) where level in(6,7)

    )t1

USING (uid)

where t1.ranking <=3 and t.ranking >=0.5 -- 使用限制找到符合条件的记录

group by t1.uid,t1.month_d

order by t1.uid,t1.month_d

注意求的是所有的答题次数和完成的次数。

注意sql试卷是限制未完成率排名
6，7级用户是限制做题记录

细节较多，写起来比较费劲。在占位百分比计算时最好在本地测试一下，分开运行看一下结果。

select uid,
       start_month,
       total_cnt,
       complete_cnt
from(select uid,
       date_format(start_time,'%Y%m') start_month,
       count(start_time) total_cnt,
       count(submit_time) complete_cnt,
       rank()over(partition by uid order by date_format(start_time,'%Y%m')desc) month_rank
from(select uid
from(select uid,
       percent_rank() over (order by count(submit_time)/count(start_time)) rank1
from exam_record
where exam_id in(select exam_id
                 from examination_info
                 where tag='SQL')
group by uid)t1
where rank1<=0.5 and uid in(select uid
                            from user_info
                            where level in(6,7)))t2
left join exam_record using (uid)
group by uid,start_month)t3
where month_rank in(1,2,3)
order by 1,2

select uid,start_month,total_cnt,complete_cnt from
(select uid,
date_format(start_time,'%Y%m') start_month,
count(start_time) total_cnt,
count(submit_time) complete_cnt,
dense_rank() over (partition by uid order by date_format(start_time,'%Y%m') desc) t_rank
from exam_record
where uid in 
(select a.uid from 
(select uid,
avg(case when submit_time is null then 1 else 0 end) incomplete_rate,
percent_rank() over (order by avg(case when submit_time is null then 1 else 0 end) desc) pr
from exam_record inner join examination_info
on exam_record.exam_id = examination_info.exam_id
where examination_info.tag = 'SQL'
group by uid) as a 
inner join user_info ui on ui.uid = a.uid
where (ui.level = 6&nbs***bsp;ui.level = 7)
and pr <= 0.5)
group by uid,start_month
order by uid,start_month) as b
where t_rank <= 3

select uid,
       date_format(start_time,'%Y%m') start_month,
       count(id) total_cnt,
       count(score) complete_cnt 
from ( 
select a.*,
dense_rank() over(partition by uid order by date_format(start_time,'  %Y%m') desc) r1 
from exam_record a
join user_info b
using(uid) 
where level>=6
and uid in (
select uid from (
select uid,percent_rank() over(order by sum(if(score is null,1,0))/count( er.id)desc) r2 
from exam_record er
join examination_info eo 
using(exam_id)
where tag='SQL'
group by uid
) t1
where r2<=0.5
)
) t2
where r1<=3
group by 1,2 
order by 1,2

with t1 as
(select 
er.uid,
date_format(start_time,'%Y%m') start_month,
score,
dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) d_r 
from exam_record er
join user_info using(uid)),
t2 as
(select 
er.uid,
percent_rank()over(order by (count(1)-count(score))/count(1) desc) p_r
from exam_record er
join examination_info using(exam_id)
where tag='SQL'
group by 1)
select 
uid,
start_month,
count(1) total_cnt,
count(score) complete_cnt
from t1
where uid in (select uid from user_info where level in (6,7))
and uid in 
(select uid from t2 where p_r<=0.5)
and d_r<4 
group by 1,2
order by 1,2

-- 解法一：
select
    t.uid,
    date_format(start_time,'%Y%m') as start_month,
    count(start_time) as total_cnt,
    count(submit_time) as complete_cnt
from (
    select
        *,
        dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as ranking
    from exam_record
) t
right join user_info ui 
on ui.uid = t.uid
where level in (6,7)
and t.ranking <= 3
and t.uid in (
    select uid from (
        select 
            uid,
            percent_rank() over(order by sum(isnull(submit_time)) / count(start_time) desc) pr
        from exam_record er join examination_info ei
        on er.exam_id = ei.exam_id
        where tag = 'SQL'
        group by uid
    ) t2
    where pr <= 0.5
)
group by t.uid,date_format(start_time,'%Y%m')
order by t.uid,date_format(start_time,'%Y%m');

-- 解法二：
with t1 as (
    select 
        uid,
        date_format(start_time,'%Y%m') start_month,
        count(start_time) total_cnt,
        count(submit_time) complete_cnt,
        row_number() over(partition by uid order by date_format(start_time,'%Y%m') desc) rn
    from exam_record er
    join examination_info ei
    on er.exam_id = ei.exam_id
    where uid in (
                select uid from (
                    select 
                        uid,
                        percent_rank() over(order by sum(isnull(submit_time)) / count(start_time) desc) irpr 
                    from exam_record er
                    join examination_info ei
                    on er.exam_id = ei.exam_id
                    where tag = 'SQL'
                    group by uid
                ) t2
                where irpr <= 0.5)
    group by uid,start_month
)
select
    uid,
    start_month,
    total_cnt,
    complete_cnt
from t1
where uid in (select uid from user_info where level in (6,7))
and rn <= 3
order by uid,start_month;

with tb as
(select uid,
date_format(start_time,'%Y%m') start_month,
count(start_time) total_cnt,
count(score) complete_cnt,
row_number() over(partition by uid order by date_format(start_time,'%Y%m') desc) rw
from exam_record
where uid in
(select uid from
(select *,max(rn) over() m from 
(select *,row_number() over(order by rate)-1 rn 
from (select uid,1-count(score)/count(1) rate from exam_record group by uid)tb1
)tb2
)tb3 where rn >= 0.5*m)
and uid in (select uid from user_info where level in (6,7))
group by uid,date_format(start_time,'%Y%m'))
select uid,
start_month,
total_cnt,
complete_cnt from tb
where rw <= 3
order by uid,start_month;

#按步骤走，平均20分钟写完主要逻辑，10分钟检查一些细节


# 每个人近三个有试卷作答记录的月份

with temp1 as(
select uid,dat from(
select uid,dat,row_number() over(partition by uid order by dat desc) r
from (
    select distinct uid,substr((date(start_time)),1,7) dat
    from exam_record
)a
)b
where r<4
),

# 统计SQL试卷上未完成率较高的50%用户中，6级和7级用户
    temp2 as(
select uid from(
select u.uid,
        max(level) level,
        count(submit_time) cnt1,
        count(start_time) cnt2,
        percent_rank() over(order by count(submit_time)/count(start_time) desc) percent
from user_info u 
    join exam_record e1 on u.uid=e1.uid
    join examination_info e2 on e1.exam_id=e2.exam_id
where tag="SQL"
group by u.uid
)a where percent>=0.5 and level>5
)
# 在有试卷作答记录的近三个月中，temp1已求得
# 每个月的答卷数目和完成数目。
select temp1.uid,
    date_format(start_time,"%Y%m") start_month,
    count(start_time) total_cnt,
    count(submit_time) complete_cnt
from temp1 join temp2 on temp1.uid=temp2.uid
            join exam_record e1  
            on temp1.uid=e1.uid and temp1.dat=substr((date(e1.start_time)),1,7)
group by temp1.uid,start_month
order by temp1.uid,start_month
# 按用户ID、月份升序排序。