2346. 以百分比计算排名_以百分比的形式报告每个学生在其部门中的排名-CSDN博客

本文链接：https://blog.csdn.net/CYJ1844/article/details/143807392

力扣题目跳转（. - 力扣（LeetCode））

表: Students

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

题目要求：

编写一个解决方案，以百分比的形式报告每个学生在其部门的排名，其中排名的百分比使用以下公式计算:

(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

case 1 的建表语句。

Create table If Not Exists Students (student_id int, department_id int, mark int)
Truncate table Students
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')

一我们先根据要求求出分子分母的必须。

select *,
        rank() over (partition by department_id order by mark desc) as rn,
        count(*) over(partition by department_id) as cnt
from students

输出如下

二按照要求进行计算即可，这里注意如果分母为 0 则会返回 null 值，需要对 null 值进行处理。

with tmp as
(select *,
        rank() over (partition by department_id order by mark desc) as rn,
        count(*) over(partition by department_id) as cnt
from students)
select student_id,department_id,
        ifnull(round((rn - 1) * 100 / ((cnt - 1)),2),0) as percentage
from tmp