【SQL刷题】根据指定记录是否存在输出不同情况

题目

根据0等级用户未完成情况输出不同结果，若任意0等级用户未完成数大于2，则输出所有0等级用户未完成情况；否则输出有作答记录用户未完成情况。

输出字段

用户编号：uid；
未完成数：incomplete_cnt；
未完成率：incomplete_rate。

输出要求

1.未完成率保留3位小数；
2.按照未完成率升序输出。

表信息

用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）；
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

思路

1.0等级用户是筛选用户信息表user_info的level字段实现，试卷作答记录表exam_record可能不完全涉及所有0等级用户，因此需要对没有出现在exam_record的0等级用户的未完成情况赋值；

2.user_info做主表，左外连接exam_record，按照user_info的uid和level字段分组。若用户有作答记录，则exam_record的start_time不为空；若用户完成试卷，则exam_record的submit_time不为空。统计未完成数可对为空submit_time计数（count），计算未完成率需要先判断start_time是否为空（为空则没有作答记录，赋值0.000)；

3.输出要求是对表的过滤，如果0等级用户未完成数大于2（看成表的一个字段flag1）则输出0等级用户（where level=0），否则输出有作答记录的用户未完成情况（有作答记录看成一个字段flag2，where flag2=取值）。可使用where和case when结合实现；

4.flag1字段的实现：结合level和按照uid、level分组计数的未完成数是否符合要求来赋值result1(if(true,1,0)），由于任意0等级用户未完成数符合要求则result1取值成立，则可用开窗函数实现，即max(result1)over()；

5.添加flag_record字段标注该用户是否在exam_record出现过，有记录则start_time计数大于0；

select 

uid,
incomplete_cnt,	
incomplete_rate

from 

(select 
ui.uid,
ui.level,
if(count(er.start_time),1,0)flag_record,
max(if (level=0 and count(if(er.submit_time,null,er.start_time))>2,1,0))over() flag_0level,
count(if(er.submit_time,null,er.start_time)) incomplete_cnt,
round(if(count(er.start_time),count(if(er.submit_time,null,1))/count(er.start_time),0),3) incomplete_rate
from user_info ui
left join exam_record er on er.uid=ui.uid 
group by ui.uid,ui.level)t

where case when  flag_0level=1 then level=0
else flag_record=1 end
order by incomplete_rate

总结

1.表的选择
根据输出要求选择信息最全的表做主表

2.要求分析
输出结果是对表的过滤，那么所有的存在情况可处理为表的字段，对字段限制实现表的过滤。

3.字段处理
聚合函数需要在小范围和大范围同时使用时，可以结合开窗函数思路实现。partition by 指定分组范围；
情况存在有否经常使用if(）函数实现，设置条件，根据逻辑取值指定不同返回结果。