搜狐视频网站联盟怎么做,综合网站建设课程设计,鞍山百度做网站,小说手机网站建设SQL题#xff1a;未完成率较高的50%用户近三个月答卷情况
这是一道牛客网上SQL进阶图库中的一道困难题目#xff0c;个人花了近两个小时才通过所有用例。之所以想记录下来是因为这道题算是一个很考验基本功的题目#xff0c;也不乏一些SQL中的技巧。下面我们逐步分析#…SQL题未完成率较高的50%用户近三个月答卷情况
这是一道牛客网上SQL进阶图库中的一道困难题目个人花了近两个小时才通过所有用例。之所以想记录下来是因为这道题算是一个很考验基本功的题目也不乏一些SQL中的技巧。下面我们逐步分析
描述
现有用户信息表user_infouid用户IDnick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间
iduidnick_nameachievementleveljobregister_time11001牛客1号32007算法2020-01-01 10:00:0021002牛客2号25006算法2020-01-01 10:00:0031003牛客3号22005算法2020-01-01 10:00:00
试卷信息表examination_infoexam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间
idexam_idtagdifficultydurationrelease_time19001SQLhard602020-01-01 10:00:0029002SQLhard802020-01-01 10:00:0039003算法hard802020-01-01 10:00:0049004PYTHONmedium702020-01-01 10:00:00
试卷作答记录表exam_recorduid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分
iduidexam_idstart_timesubmit_timescore1100190012020-01-01 09:01:012020-01-01 09:21:599015100290012020-01-01 18:01:012020-01-01 18:59:029013100190012020-01-02 10:01:012020-01-02 10:31:01892100290012020-01-20 10:01:013100290012020-02-01 12:11:015100190012020-03-01 12:01:016100290012020-03-01 12:01:012020-03-01 12:41:01904100390012020-03-01 19:01:017100290012020-05-02 19:01:012020-05-02 19:32:009014100190022020-01-01 12:11:018100190022020-01-02 19:01:012020-01-02 19:59:01699100190022020-02-02 12:01:012020-02-02 12:20:019910100290022020-02-02 12:01:0111100290022020-02-02 12:01:012020-02-02 12:43:018112100290022020-03-02 12:11:0117100190022020-05-05 18:01:0116100290032020-05-06 12:01:01
请统计SQL试卷上未完成率较高的50%用户中6级和7级用户在有试卷作答记录的近三个月中每个月的答卷数目和完成数目。按用户ID、月份升序排序。
由示例数据结果输出如下
uidstart_monthtotal_cntcomplete_cnt100220200231100220200321100220200521
解释各个用户对SQL试卷的未完成数、作答总数、未完成率如下
uidincomplete_cnttotal_cntincomplete_rate1001370.42861002480.50001003111.0000
1001、1002、1003分别排在1.0、0.5、0.0的位置因此较高的50%用户排位0.5为1002、1003
1003不是6级或7级
有试卷作答记录的近三个月为202005、202003、202002
这三个月里1002的作答题数分别为3、2、2完成数目分别为1、1、1。
###解法
这道题看起来很复杂需要我们划分多个步骤进行多次SQL嵌套才能完成。
**步骤一.**首先需要统计各个用户对SQL试卷的未完成数、作答总数、未完成率。其中需要确保试卷是SQL试卷。需要注意的是这一步需要考虑多增加一列未完成率排名排名应该使用开窗函数。SQL写法如下
select exam_record.uid,
sum(case when submit_time is null then 1 else 0 end) incomplete_cnt,
count(1) total_cnt,
round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4) incomplete_rate ,
user_info.level,
row_number() over(order by round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4)) r
from exam_record
inner join user_info
on user_info.uid exam_record.uid
inner join examination_info
on exam_record.exam_id examination_info.exam_id
where examination_info.tag SQL
group by exam_record.uid
order by incomplete_rate下一步则根据上一步所得出的数据筛选出哪些用户未完成率排在前50%且是6级或7级用户加上将上一步SQL所得出的表命名为表a可写如下sql进行筛选
select a.uid from a
where r (select floor(count(distinct uid)/2) from exam_record) 1 and (a.level 6 or a.level 7)此时我们就得出了应该被算入最终统计结果的所有用户uid。
**步骤二.**下一步需要考虑统计用户近三个月的总答题数和完成数。此时需要注意的是需要选出近三个月因而至少需要一次针对不同用户uid和start_month的排序。代码如下
select exam_record.uid,
date_format(exam_record.start_time,%Y%m) start_month,
count(1) over(partition by exam_record.uid, date_format(exam_record.start_time,%Y%m)) total_cnt,
sum(case when exam_record.submit_time is null then 0 else 1 end) over(partition by exam_record.uid , date_format(exam_record.start_time,%Y%m)) complete_cnt,
dense_rank() over(partition by exam_record.uid order by date_format(exam_record.start_time,%Y%m) desc) x
from exam_record上段代码包含了复杂的开窗其实主要是针对不同用户uid和start_month进行聚合统计当月的答题总数total_cnt和当月的总完成数complete_cnt。需要注意的是我们添加了一次排序使用的是dense_rank()进行排序目的是同时达到筛选前三个月的数据和去重。将上一个SQL所得出的表命名为表tSQL写法如下
select t.uid,t.start_month,t.total_cnt, t.complete_cnt
from t
where t.x 3
group by t.uid,t.start_month,t.total_cnt, t.complete_cnt
order by t.uid,t.start_month以上代码很重要同时达到去重和选取固定行数的目的是重要的SQL技巧。
**步骤三.**下面我们将以上两个步骤的所有代码结合起来得出最终的解
select t.uid,t.start_month,t.total_cnt, t.complete_cnt /*除去下面注释部分所标注的内容都是步骤二所完成查询*/
from (
select exam_record.uid,
date_format(exam_record.start_time,%Y%m) start_month,
count(1) over(partition by exam_record.uid , date_format(exam_record.start_time,%Y%m)) total_cnt,
sum(case when exam_record.submit_time is null then 0 else 1 end) over(partition by exam_record.uid , date_format(exam_record.start_time,%Y%m)) complete_cnt,
dense_rank() over(partition by exam_record.uid order by date_format(exam_record.start_time,%Y%m) desc) x
from exam_record
where exam_record.uid in ( /*这里对uid的筛选其实主要是从步骤一中得出的结果中筛选*/
select a.uid from
(
select exam_record.uid,
sum(case when submit_time is null then 1 else 0 end) incomplete_cnt,
count(1) total_cnt,
round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4) incomplete_rate ,
user_info.level,
row_number() over(order by round(sum(case when submit_time is null then 1 else 0 end)/(count(1)), 4)) r
from exam_record
inner join user_info
on user_info.uid exam_record.uid
inner join examination_info
on exam_record.exam_id examination_info.exam_id
where examination_info.tag SQL
group by exam_record.uid
order by incomplete_rate
) a
where r (select floor(count(distinct uid)/2) from exam_record) 1 and (a.level 6 or a.level 7)
)
) t
where t.x 3
group by t.uid,t.start_month,t.total_cnt, t.complete_cnt
order by t.uid,t.start_month比较复杂详细查看前两步才能看懂最终结合的逻辑。
