题解 | #筛选限定昵称成就值活跃日期的用户#

题目：

• 请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出

• 结果按用户等级降序、占比降序排序

除CASE WHEN条件表达式外，这道题目非常能考验学习者对“表联结”和“窗口函数”的基础掌握水平，我认为是算是中上级水平了

• 也可能是我基础还不够扎实所产生的幻觉，lol

此题有两个解题方法，一个是比较常规的、思路比较结构化，但是比较容冗长的，主要使用的是表联结；另外一个是使用窗口函数的、更加简洁明了的方法

方法 1：

步骤 1：对现有的答题记录，去除未完成的纪录后，再分别打上等级标签

• 对应不同的分数，有不同的等级标签，这里需要用到CASE WHEN条件表达式
• 其次，未完成的答题记录，直接使用WHERE筛选即可
• Ps：这里实际的代码我只SELECT了“level”和CASE WHEN两个字段，不是那么的易阅读，实际上，各位可以在这一步把uid、exam_id、score都选上，会更加易于阅读；但随后就需要删掉，因为后续步骤不需要这些字段

SELECT ui.level,
		/* 打标签要使用CASE WHEN表达式 */
        (CASE WHEN er.score < 60 THEN '差'
              WHEN er.score >= 60 AND er.score < 75 THEN '中'
              WHEN er.score >= 75 AND er.score < 90 THEN '良'
              ELSE '优' END)  AS score_grade
FROM exam_record AS er LEFT OUTER JOIN user_info AS ui /* 使用左联结，为每一行记录打上标签 */
ON er.uid = ui.uid
WHERE er.score IS NOT NULL /* 筛除未完成的答题记录 */

步骤 2：打上标签之后，再根据用户等级和成绩等级标签来分组，分别计算每个用户等级下、不同成绩等级的答题记录数

• 这一步很简单，直接在步骤1的查询语句中多加一个COUNT字段和GROUP BY语句
• 值得注意的是，GROUP BY的是两个字段，一个是level，一个是成绩等级 —— 由于成绩等级是一个CASE WHEN表达式创建出来的字段，因此，在GROUP BY该字段时，也请注意把CASE WHEN表达式完整地copy paste到GROUP BY当中，不要使用别名score_grade

SELECT ui.level,
        (CASE WHEN er.score < 60 THEN '差'
              WHEN er.score >= 60 AND er.score < 75 THEN '中'
              WHEN er.score >= 75 AND er.score < 90 THEN '良'
              ELSE '优' END)  AS score_grade,
        /* 使用COUNT函数来统计记录数 */      
        COUNT(level) AS grade_cnt
FROM exam_record AS er LEFT OUTER JOIN user_info AS ui
ON er.uid = ui.uid
WHERE er.score IS NOT NULL
/* group by用户等级、成绩等级，看着很复杂，其实就是复制粘贴 */
GROUP BY ui.level,
        (CASE WHEN er.score < 60 THEN '差'
                     WHEN er.score >= 60 AND er.score < 75 THEN '中'
                     WHEN er.score >= 75 AND er.score < 90 THEN '良'
                     ELSE '优' END)

步骤 3：查询第二个结果表，统计每个用户等级的总记录数，即，计算成绩等级占比的分母

• 这一步的查询语句和步骤、2非常相似，只是GROUP BY的字段只有level一个
• 注意，仍需去掉未完成的记录行

SELECT ui.level, COUNT(ui.level) AS level_cnt
FROM exam_record AS er LEFT OUTER JOIN user_info AS ui
ON er.uid = ui.uid
WHERE er.score IS NOT NULL
GROUP BY ui.level

步骤 4：联结步骤2和步骤3的两个表，随后就可以计算：在不同用户等级下，不同成绩等级的占比了

• 直接联结量表，不多说了

SELECT t2.level, 
	   t2.score_grade,
       ROUND(t2.grade_cnt / t1.level_cnt, 3) AS ratio /* 结果保留3位数 */
FROM

  /* 步骤3的查询结果 */
  (SELECT ui.level, COUNT(ui.level) AS level_cnt
    FROM exam_record AS er LEFT OUTER JOIN user_info AS ui
    ON er.uid = ui.uid
    WHERE er.score IS NOT NULL
    GROUP BY ui.level) AS t1
 
INNER JOIN

/* 步骤2的查询结果 */
  (SELECT ui.level,
          (CASE WHEN er.score < 60 THEN '差'
                              WHEN er.score >= 60 AND er.score < 75 THEN '中'
                              WHEN er.score >= 75 AND er.score < 90 THEN '良'
                              ELSE '优' END)  AS score_grade,
          COUNT(level) AS grade_cnt
    FROM exam_record AS er LEFT OUTER JOIN user_info AS ui
    ON er.uid = ui.uid
    WHERE er.score IS NOT NULL
    GROUP BY ui.level,
            (CASE WHEN er.score < 60 THEN '差'
                  WHEN er.score >= 60 AND er.score < 75 THEN '中'
                  WHEN er.score >= 75 AND er.score < 90 THEN '良'
                  ELSE '优' END)) AS t2

ON t1.level = t2.level

ORDER BY t2.level DESC, ratio DESC; /* 最终结果要按照level和ratio降序 */

方法 2：使用窗口函数，这个方法我强烈推荐！至少我本人当时没想到还有如此快速的办法，又补充了我对窗口函数的使用细节！

• 步骤 1：和方法一的步骤1、步骤2是一模一样的，对现有的答题记录，去除未完成的纪录后，再分别打上等级标，再group by统计每个用户等级下、不同成绩等级的答题记录数

SELECT ui.level,
        (CASE WHEN er.score < 60 THEN '差'
              WHEN er.score >= 60 AND er.score < 75 THEN '中'
              WHEN er.score >= 75 AND er.score < 90 THEN '良'
              ELSE '优' END)  AS score_grade,
        COUNT(level) AS grade_cnt
FROM exam_record AS er LEFT OUTER JOIN user_info AS ui
ON er.uid = ui.uid
WHERE er.score IS NOT NULL
GROUP BY ui.level,
        (CASE WHEN er.score < 60 THEN '差'
              WHEN er.score >= 60 AND er.score < 75 THEN '中'
              WHEN er.score >= 75 AND er.score < 90 THEN '良'
              ELSE '优' END)

步骤 2：使用窗口函数和聚合函数的结合，统计出每个用户等级的总答题数

• 神来之笔，这里需要用到SUM函数和窗口函数，而窗口函数只需要对level进行分组（即，PARTITION BY level）、无需再ORDER（若是ORDER了，则会变成累积算法了），即可立马创建一个新的列，该列中，每一行的值都是对应的用户等级的总答题数，如下图

• 之前对聚合函数与窗口函数的结合一直停留在累积算法的运用上，唯独是没想起来还有这个只用PARTITION BY不用ORDER BY的朴素操作，实在是思维固化了！
• 下一步就明朗了，直接上代码：

SELECT level, score_grade,
		/* SUM(grade_cnt) OVER (PARTITION BY level)计算的就是每个用户等级的总记录数
           每一行都会有一个对应的总记录数，因此最后只需要grade_cnt列与该列相除，即可得到最终的ratio */
        ROUND(grade_cnt / SUM(grade_cnt) OVER (PARTITION BY level), 3) AS ratio
FROM
/* 步骤1的查询结果 */
(SELECT ui.level,
        (CASE WHEN er.score < 60 THEN '差'
                            WHEN er.score >= 60 AND er.score < 75 THEN '中'
                            WHEN er.score >= 75 AND er.score < 90 THEN '良'
                            ELSE '优' END)  AS score_grade,
        COUNT(level) AS grade_cnt
FROM exam_record AS er LEFT OUTER JOIN user_info AS ui
ON er.uid = ui.uid
WHERE er.score IS NOT NULL
GROUP BY ui.level,
        (CASE WHEN er.score < 60 THEN '差'
                     WHEN er.score >= 60 AND er.score < 75 THEN '中'
                     WHEN er.score >= 75 AND er.score < 90 THEN '良'
                     ELSE '优' END)) AS t
                      
ORDER BY level DESC, ratio DESC;