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

题目主要信息：

统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目
筛选的时候用户按未完成率降序排名，相同情况按uid降序

问题拆分：

先筛选出每个用户的ID、未完成的试卷数、总试卷数：
- 要计算每个用户的信息，因此要以uid分组。知识点：group by
- 用户ID直接选择，每个人总试卷数统计每个人的开始时间出现的次数。知识点：count()
- 每个用户的未完成的试卷数，我们统计得分，如果得分为null记为1，否则记为0，将每个人的这个if值求和就是得分为null出现的次数即每个人未完成试卷的份数。sum(if(score is null, 1, 0)) as incomplete_cnt 知识点：if()、sum()
- 筛选出的信息记为incomplete_cnt_table
从incomplete_cnt_table中筛选出每个用户ID、未完成率排名：
- incomplete_cnt_table已经分组了，所以是每个ID对应一份数据，直接选出ID，不用分组。
- 利用上述筛选出的每人的未完成试卷数和总试卷数计算未完成率，然后以先未完成率降序、再uid降序排名。row_number() over(order by (incomplete_cnt / total_cnt) desc, uid desc) as incomplete_rank 知识点：row_number()over()、order by
- 筛选出的信息记为incomplete_rate_table
对exam_record表中的不同的uid计数，统计总共多少用户做了试卷，计数信息记为表t_u。 select count(distinct uid) as total_user from exam_record 知识点：distinct、count()
因为我们需要用户等级信息、总人数信息及未完成率排名，因此要将incomplete_rate_table与t_u直接连接在一起，然后通过uid再与user_info连接在一起。知识点：join
筛选出未完成率较高的50%及用户等级为6或7级的用户ID：
- 从三个连接的表中选出用户ID，条件是level>=6，并且排名小于等于总人数除2的向上取整（按照题目的意思，奇数人数要取中间因此向上取整）。where level >= 6 and incomplete_rank <= ceiling(total_user / 2) 知识点：where、ceiling()
从exam_record中筛选出用户ID、开始做试卷时间、得分及做题月份的降序排名：
- 用户ID、开始做题时间、得分可以直接得到
- 做题月份的降序排名我们使用dense_rank()over()，因为可能会出现重复月份，我们要的是最大的三个月份，排名以uid分组，统计每个人的月份排名。dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months 知识点：dense_rank() over()、partition by、order by、date_format()
- 筛选出的结果记为recent_table
从recent_table中筛选出满足条件的用户ID及及其最近三个月的做题情况：
- 需要统计每个用户每个月的情况，因此要以uid和start_month为分组。知识点：group by
- 对于每个分组统计完成总题数，直接对recent_table中的开始做题时间计数；统计完成的题目数，直接对recent_table中的得分次数做计数。 知识点：count()
- 当然筛选还有条件，就是月份排名小于等于3，即最近三个月，然后uid要位于上述筛选出来的前50%的uid中。知识点：where...and ... in...
最后按照用户ID排序输出即可。知识点：group by

代码：

select uid, 
       date_format(start_time, '%Y%m') as start_month,
       count(start_time) as tatol_cnt,
       count(score) as complete_cnt
from(
    select uid, start_time, score,
           dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months
    from exam_record
) recent_table
where recent_months <= 3
and uid in(
     select incomplete_rate_table.uid
     from(
         select uid,
            row_number() over(order by (incomplete_cnt / total_cnt) desc, uid desc) as incomplete_rank
         from(
            select uid,
                   sum(if(score is null, 1, 0)) as incomplete_cnt,
                   count(start_time) as total_cnt
            from exam_record
            group by uid
             ) incomplete_cnt_table
     ) incomplete_rate_table join(
         select count(distinct uid) as 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)
)
group by uid, start_month
order by uid

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

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

题目主要信息：

问题拆分：

代码：