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

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

http://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c

一、知识点总结

把有用知识点写在前面，以方便自个儿收藏观看😊。

1）5个常用的排序函数

rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)\
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

percent_rank() over() 按照数字所在的位置进行百分位分段
ntile(n)over() 将数字按照大小平均分成n段
lead(字段名，n)over()把字段数据向前移n个单元格
lag(字段名，n)over()把字段数据向后移n个单元格

案例：

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、题目解读

求：请统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。
题目中的坑：未完成率较高的50%用户，即为完成率低于50%的用户。
需求字段：uid 、 start_month、 total_cnt complete_cnt

2、步骤拆分

1）SQL试卷上未完成率较高的50%用户。

完成率=用户完成的题/用户答题数:COUNT(submit_time) / COUNT(start_time)
对完成率进行排序：PERCENT_RANK()OVER()
SQL试卷

2）6级和7级用户在有试卷作答记录的近三个月中。

查找6，7级用户的uid
对作答记录进行排序取近3个月

3）每个月的答卷数目和完成数目。
4）按用户ID、月份升序排序。

三、步骤代码

1）SQL完成率=用户完成的题/用户答题数

用户完成题数：COUNT(submit_time)
用户答题数：COUNT(start_time)
完成率=COUNT(submit_time) / COUNT(start_time)
SELECT exam_id FROM examination_info WHERE tag='SQL'

2)对完成率进行排序，以及完成率排名后50%的用户

PERCENT_RANK()OVER( ORDER BY count(submit_time)/count(start_time) ) rate_rk
rate_rk<=50%

SELECT uid,COUNT(submit_time)/COUNT(start_time) rk_com ,PERCENT_RANK()over( ORDER BY COUNT(submit_time)/COUNT(start_time) ) rate_rk FROM exam_record WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag='SQL') GROUP BY uid /*对SQL用户的完成率进行排序*/

3)查找6，7级用户的uid

SELECT uid FROM user_info WHERE level IN (6,7);

SELECT * 
	FROM (
		SELECT uid
		,PERCENT_RANK()over( ORDER BY count(submit_time)/count(start_time) ) rate_rk
		FROM exam_record
		WHERE  exam_id IN 
		(SELECT exam_id FROM examination_info WHERE tag='SQL')
		GROUP BY uid
		) A -- 对用户完成率进行排序
	WHERE rate_rk<=0.5 -- 查找完成率排名低的50%用户
	AND uid IN (SELECT uid FROM user_info WHERE level IN (6,7))-- 查找6，7级用户

以上找出来满足题设需求的用户”SQL试卷上未完成率较高的50%用户中，6级和7级用户“只有1002

4)对作答记录时间进行排序取近3个月

SELECT * ,dense_rank()over(partition by uid order by date_format(start_time,'%Y-%m') desc) time_rk -- 对作答时间进行排序
FROM exam_record

5）每个用户每个月的答卷数目和完成数目，并按用户ID、月份升序排序。

SELECT t1.uid,
    DATE_FORMAT(start_time,'%Y%m')start_month,  -- 取月份数
    COUNT(start_time) total_cnt, -- 答卷数
    COUNT(submit_time) complete_cnt  -- 完成数
FROM (
	SELECT * ,
    dense_rank()over(partition by uid order by date_format(start_time,'%Y-%m') desc) time_rk -- 对作答时间进行排序
	FROM exam_record
	)t1    
WHERE time_rk<=3 -- 查找作答时间最近的3个月
GROUP BY uid,start_month 
ORDER BY uid,start_month -- 按照用户id和月份进行升序排序
;

四、完整代码组装

SELECT t1.uid,
    DATE_FORMAT(start_time,'%Y%m')start_month,  -- 取月份数
    COUNT(start_time) total_cnt, -- 答卷数
    COUNT(submit_time) complete_cnt  -- 完成数
FROM (
    SELECT * ,
    dense_rank()over(partition by uid order by date_format(start_time,'%Y-%m') desc) time_rk -- 对作答时间进行排序
    FROM exam_record
    )t1
    
RIGHT JOIN (
    SELECT * 
    FROM (
        SELECT uid,
        PERCENT_RANK()over( ORDER BY count(submit_time)/count(start_time) ) rate_rk -- 对完成率进行分数排序
        FROM exam_record
        WHERE  exam_id IN 
        (SELECT exam_id FROM examination_info WHERE tag='SQL') -- SQL试卷
        GROUP BY uid
        ) A
    WHERE rate_rk<=0.5  -- 查找排名低于50%的用户
    AND uid IN (SELECT uid FROM user_info WHERE level IN (6,7)) -- 查找6.7级用户
    )t2 ON t1.uid=t2.uid
    
WHERE time_rk<=3 -- 查找作答时间最近的3个月
GROUP BY uid,start_month 
ORDER BY uid,start_month -- 按照用户id和月份进行升序排序
;