一、知识总结拓展&答案

1、知识点总结与拓展

1）CASE WHEN 的使用
  • CASE WHEN 常常和SUM一起使用，便于做不同类别的数据统计，SUM( CASE WHEN ...THEN 1 ELSE 0)
  • 也可用于分类统计的情况，把列换成行。
以上两种用法可以看这个例子（直接用题目的数据做的） 
SELECT level,
	SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) '差' ,
    SUM(CASE WHEN score<75 and score>=60 THEN 1 ELSE 0 END) '中' ,
	SUM(CASE WHEN  score<90 and score>=75 THEN 1 ELSE 0 END) '良',
	SUM(CASE WHEN  score>=90 THEN 1 ELSE 0 END) '优' #对用户的分数进行等级定义
FROM exam_record a LEFT JOIN user_info b ON a.uid=b.uid 
WHERE score IS NOT NULL
GROUP BY level; #剔除未完成的试卷
2）窗口函数的使用
 目前累计了9个和排序相关的窗口函数啦
  • 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个单元格
  • last_value(字段名1) over (order by   rows between unbounded preceding and unbounded following) 根据排序返回字段名1最后一行，也可以加上partition by 一起使用
  • frist_value(字段名2) over (order by   rows between unbounded preceding and unbounded following) 根据排序返回字段名2第一行，也可以加上partition by 一起使用
5个常用的聚合窗口函数
  • min()over() :不改变表结构的前提下，计算出最小值
  • max()over():不改变表结构的前提下，计算出最大值
  • count()over():不改变表结构的前提下，计数
  • sum()over():不改变表结构的前提下，求和
  • avg()over():不改变表结构的前提下，求平均值

2、完整代码 

WITH t1 AS
(SELECT level,score,
	COUNT(level)OVER(PARTITION BY level) level_cn, #各个level 的试卷数
	(CASE WHEN score<60 THEN '差' 
		  WHEN score<75 and score>=60 THEN '中' 
		  WHEN score<90 and score>=75 THEN '良'
		  ELSE '优' END) grade #对用户的分数进行等级定义
FROM exam_record a LEFT JOIN user_info b ON a.uid=b.uid 
WHERE score IS NOT NULL #剔除未完成的试卷
)
SELECT level,grade, ROUND(COUNT(grade)/level_cn,3)ratio FROM t1
GROUP BY level,grade
ORDER BY level DESC,ratio DESC ;

二、题目解读与解题步骤拆分

1、题目解读

1）题目
为了得到用户试卷作答的定性表现，我们将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间），请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序。

2）题目中隐藏的坑或者难点
  • a、根据3各分界点用户的4个级别具体如下：score<60 差, (score<75 and score>=60) 中, (score<90 and score>=75) 良, score>=90 优
  • b、不同等级的占比：是指各个级别里的等级占比，例如0级别的‘优秀’用户占比，就是0级别里的'A'类用户数/全部0级别的用户数。

需求字段

  • level：用户等级
  • score_grade：分数级别
  • ratio：比率

2、步骤拆分

 第1步先整理出一张子表包含每张试卷作答的用户级别（level），各级别答卷数（level_cn），答卷分数的分数等级（score_grade）。第2步再统计各个level里各个score_grade数即可

三、步骤代码

1）整理子表 
SELECT level,score,
	COUNT(level)OVER(PARTITION BY level) level_cn, #各个level 的试卷数
	(CASE WHEN score<60 THEN '差' 
		  WHEN score<75 and score>=60 THEN '中' 
		  WHEN score<90 and score>=75 THEN '良'
		  ELSE '优' END) grade #对用户的分数进行等级定义
FROM exam_record a LEFT JOIN user_info b ON a.uid=b.uid 
WHERE score IS NOT NULL; #剔除未完成的试卷

2）汇总统计数据 
WITH t1 AS
(SELECT level,score,
	COUNT(level)OVER(PARTITION BY level) level_cn, #各个level 的试卷数
	(CASE WHEN score<60 THEN '差' 
		  WHEN score<75 and score>=60 THEN '中' 
		  WHEN score<90 and score>=75 THEN '良'
		  ELSE '优' END) grade #对用户的分数进行等级定义
FROM exam_record a LEFT JOIN user_info b ON a.uid=b.uid 
WHERE score IS NOT NULL #剔除未完成的试卷
)
SELECT level,grade, ROUND(COUNT(grade)/level_cn,3)ratio FROM t1
GROUP BY level,grade
ORDER BY level DESC,ratio DESC ;