【MySQL】MySQL版本8+ 窗口函数 PERCENT |
您所在的位置:网站首页 › mysql中的rank函数怎么用 › 【MySQL】MySQL版本8+ 窗口函数 PERCENT |
力扣题
1、题目地址
2346. 以百分比计算排名 2、模拟表表:Students Column NameTypestudent_idintdepartment_idintmarkint student_id 包含唯一值。该表的每一行都表示一个学生的 ID,该学生就读的院系 ID,以及他们的考试分数。 3、要求 编写一个解决方案,以百分比的形式报告每个学生在其部门的排名,其中排名的百分比使用以下公式计算:(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。如果两个学生得到相同的分数,他们也会得到相同的排名。以 任意顺序 返回结果表。 4、示例输入: Students 表: student_iddepartment_idmark2265082650719201161031530输出: student_iddepartment_idpercentage710.01150.031100.0220.0820.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 5、代码编写 我的第一种写法 WITH tmp AS ( SELECT student_id, department_id, RANK() over (partition by department_id order by mark desc) AS rn FROM Students ) SELECT one.student_id, one.department_id, ROUND(IFNULL((one.rn-1)*100/(two.num-1), 0), 2) AS percentage FROM tmp AS one LEFT JOIN ( SELECT department_id, COUNT(*) AS num FROM tmp GROUP BY department_id ) AS two USING(department_id) 我的第二种写法 SELECT student_id, department_id, ROUND( IFNULL( (rank() over (partition by department_id order by mark desc) - 1) * 100 / (count(*) over (partition by department_id) - 1), 0 ), 2 ) AS percentage FROM Students 网友写法(PERCENT_RANK 写法) SELECT student_id, department_id, ROUND(PERCENT_RANK() over (partition by department_id order by mark desc)*100, 2) AS percentage FROM Students 知识点PERCENT_RANK 窗口函数用于将每行按照 (rank - 1) / (rows - 1) 进行计算。 rank 为 RANK 窗口函数产生的序号rows 为当前窗口的记录总行数PERCENT_RANK 函数返回介于 0 和 1 之间的小数值 参考[Mysql] PERCENT_RANK()函数 | CUME_DIST()函数 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |