2346. 以百分比计算排名（难度：中等）

题目描述

（通过次数396 | 提交次数1,011，通过率39.17%）

表: Students
+---------------+------+
| Column Name   | Type |
+---------------+------+
| student_id    | int  |
| department_id | int  |
| mark          | int  |
+---------------+------+
student_id 是该表的主键。
该表的每一行都表示一个学生的 ID，该学生就读的院系 ID，以及他们的考试分数。

编写一个 SQL 查询，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:
(student_rank_in_the_department - 1) * 100 / (the_number_of_students_in_the_department - 1)。percentage 应该四舍五入到小数点后两位。
student_rank_in_the_department由mark的降序决定，mark 最高的学生是 rank 1。如果两个学生得到相同的分数，他们也会得到相同的排名。
以 任意顺序 返回结果表。

查询结果格式如下所示。
示例 1:
输入: 
Students 表:
+------------+---------------+------+
| student_id | department_id | mark |
+------------+---------------+------+
| 2          | 2             | 650  |
| 8          | 2             | 650  |
| 7          | 1             | 920  |
| 1          | 1             | 610  |
| 3          | 1             | 530  |
+------------+---------------+------+
输出: 
+------------+---------------+------------+
| student_id | department_id | percentage |
+------------+---------------+------------+
| 7          | 1             | 0.0        |
| 1          | 1             | 50.0       |
| 3          | 1             | 100.0      |
| 2          | 2             | 0.0        |
| 8          | 2             | 0.0        |
+------------+---------------+------------+
解释: 
对于院系 1:
 - 学生 7:percentage = (1 - 1)* 100 / (3 - 1) = 0.0
 - 学生 1:percentage = (2 - 1)* 100 / (3 - 1) = 50.0
 - 学生 3:percentage = (3 - 1)* 100 / (3 - 1) = 100.0
对于院系 2:
 - 学生 2: percentage = (1 - 1) * 100 / (2 - 1) = 0.0
 - 学生 8: percentage = (1 - 1) * 100 / (2 - 1) = 0.0

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/compute-the-rank-as-a-percentage

//测试数据
Create table If Not Exists Students (student_id int, department_id int, mark int);

insert into Students (student_id, department_id, mark) values ('2', '2', '650');
insert into Students (student_id, department_id, mark) values ('8', '2', '650');
insert into Students (student_id, department_id, mark) values ('7', '1', '920');
insert into Students (student_id, department_id, mark) values ('1', '1', '610');
insert into Students (student_id, department_id, mark) values ('3', '1', '530');

解题思路

Students表保存了所有的学生信息。包括学生ID、部门ID、分数。
题目要求：计算每个学生在其部门内的排名。
部门内的排名是根据一个计算公式得出的：(student_rank_in_the_department – 1) * 100 / (the_number_of_students_in_the_department – 1)。
其中，student_rank_in_the_department表示学生分数在部门内的排名。如果分数相同，则排名相同。很明显，可以使用开窗排序的方法实现。
the_number_of_students_in_the_department表示部门内的学生数量。这个简单，直接根据部门GROUP BY汇总即可。
分别计算出每个学生的以上两个值后，代入公式计算即可。
不过，需要注意的是，如果某个部门仅有一名学生，公式中的分母为0，计算失败。此时题目要求，直接返回0的结果。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with
tmp1 as (
    select
        student_id,
        rank() over(partition by department_id order by mark desc) rk
    from Students
),
tmp2 as (
    select
        department_id,
        count(1) cnt
    from Students
    group by department_id
)
select
    a.student_id,
    a.department_id,
    case when c.cnt = 1 then 0.00 else round((b.rk-1)*100/(c.cnt-1),2) end percentage
from Students a
inner join tmp1 b
on a.student_id = b.student_id
inner join tmp2 c
on a.department_id = c.department_id;