# 先找到sql试卷未完成率较高的前一半用户,筛选出level是6、7的 # 这些用户的有作答记录的近三个月,分组统计每月的答卷数和完成数 WITH temp AS ( # 联结表 SELECT a.uid, c.start_time, c.score, a.level, b.tag FROM exam_record c LEFT JOIN user_info a USING(uid) LEFT JOIN examination_info b USING(exam_id) # WHERE b.tag = 'SQL' #AND a.level IN (6,7) ), # 找到较高的50%的用户,还是要排序在前50% 50_temp AS ( SELECT uid, ROUND(SUM(IF(score IS NULL, 1, 0))/COUNT(start_time), 4) incomplete_rate FROM temp WHERE tag = 'SQL' GROUP BY uid ), u_temp AS ( SELECT uid, PERCENT_RANK() OVER(ORDER BY incomplete_rate) rate_rank FROM 50_temp ) SELECT uid, DATE_FORMAT(start_time, "%Y%m") start_month, COUNT(start_time) total_cnt, COUNT(score) complete_cnt FROM ( SELECT uid, start_time, score, DENSE_RANK() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, "%Y%m") DESC) rank_month # 按时间对记录排序 FROM temp WHERE uid IN ( # uid的确定 SELECT uid FROM u_temp WHERE rate_rank >= 0.5 # 未完成率在前50% ) AND level IN (6,7) ) b WHERE rank_month < 4 # 近三个月限定 GROUP BY uid, start_month # 按照这两个字段分组 ORDER BY uid, start_month
题目拆解:1.SQL试卷上未完成率较高的50%用户中——限定了用户范围,是在sql上未完成率排序前50%的用户(最开始看示例,我还以为是未完成率高于50%的人)
2.6级和7级用户在有试卷作答记录的近三个月中,每个月的答卷数目和完成数目——跟上一题相似
新知识点:
1.percent_rank() over() 按照数字所在的位置进行百分位分段
2.ntile(n)over() 将数字按照大小平均分成n段
3.在标准的SQL中,一个查询只能有一个WITH子句,但是可以在单个WITH子句中定义多个公用表表达式(CTE)。每个CTE之间用逗号分隔。
4.dense_rank() over() 1 2 2 3 3 4 (不跳过排名,可以理解为对类别进行计数)
踩的坑:
1.看示例以为是按未完成率的大小筛选,但是根据题意还是要排序
2.题意是限制了用户范围,但是没有限制作答记录的题目类型,最开始我给限制了,老是少一条记录
思路:
1.主查询用来查询结果示例的三个字段。需要满足的条件是用户是在一定范围内的;用户等级是由限制的;作答记录的日期是由限制的
2.根据上述限制,我们要先把用户,特别是SQL试卷上未完成率较高的50%用户中 找出来,然后再同时找到符合等级的用户。
3.用户确定了,再找到每个用户近三个月的作答记录,再统计总作答和实际完成数,最后排序。
做法:
1.将三个表有用的字段联结起来,内联结或左连接都可以,创建了第一个临时表
2.SQL试卷上未完成率较高的50%用户中 :根据SQL题初步筛选用户,并且计算未完成率,使用if函数,创建第二个临时表;创建第三个,来对未完成率进行百分比排序,使用pertcent_rank,我先使用正序排列未完成率,这个时候未完成率小的排前面的,所以在主查询采用>=
3.主查询将其组配起来