通过代码

SELECT
    level,
    score_grade,
    round(lei/tol,3) ratio
FROM
(SELECT
    level,
    score_grade,
    count(*)over(
        partition by level,score_grade
    ) lei,
    count(*)over(
        partition by level) tol
from(
SELECT
    user_info.uid,
    score,
    level,
    (case 
     when score between 0 and 59 then '差'
     when score between 60 and 74 then '中'
     when score between 75 and 89 then '良'
     when score between 90 and 100 then '优'
     end
    ) score_grade
FROM
    exam_record 
RIGHT JOIN
    user_info
ON
    exam_record.uid = user_info.uid
WHERE 
    score is not NULL
)e)t
GROUP BY
    level,score_grade
ORDER BY
    level desc,ratio DESC

思路

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

1.分数转化然后初步筛选出答过试卷的用户

2.窗口函数查出来相应count

3.计算相应数据并排序

分数转化然后初步筛选出答过试卷的用户

SELECT
    user_info.uid,
    score,
    level,
    (case 
     when score between 0 and 59 then '差'
     when score between 60 and 74 then '中'
     when score between 75 and 89 then '良'
     when score between 90 and 100 then '优'
     end
    ) score_grade
FROM
    exam_record 
RIGHT JOIN
    user_info
ON
    exam_record.uid = user_info.uid
WHERE 
    score is not NULL

窗口函数查出来相应count

SELECT
    level,
    score_grade,
    count(*)over(
        partition by level,score_grade
    ) lei,
    count(*)over(
        partition by level) tol
from(1.代码)e

计算相应数据排序

SELECT
    level,
    score_grade,
    round(lei/tol,3) ratio
FROM(2.代码)t
GROUP BY
    level,score_grade
ORDER BY
    level desc,ratio DESC

这样逻辑清晰，过程很容易理解，但是使用了三层子查询

我们可以考虑进行一个小优化也就是：

在第一层就把总数给算出来，这样只需要两层就可以

通过代码2

SELECT
    level,
    score_grade,
    round(count(*)/tol,3) ratio
FROM
(SELECT
    user_info.uid,
    score,
    level,
    (case 
     when score between 0 and 59 then '差'
     when score between 60 and 74 then '中'
     when score between 75 and 89 then '良'
     when score between 90 and 100 then '优'
     end
    ) score_grade,
    count(*)over(
        partition by level) tol
FROM
    exam_record 
RIGHT JOIN
    user_info
ON
    exam_record.uid = user_info.uid
WHERE 
    score is not NULL
)t
GROUP BY
    level,score_grade
ORDER BY
    level desc,ratio DESC

如果第一次就group by会把数据综合，导致二次group的时候计数错误，所以这里用了窗口函数。