178.分数排名

标签：窗口函数（rank,dense_rank,roll_number ）

2022年12月19日

题目

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

• 分数应按从高到低排列。
• 如果两个分数相等，那么两个分数的排名应该相同。
• 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
输出: 
+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

方法一

思路：使用子查询，得出高于或等于自己的成绩，然后去重，将重复的数据去掉，再用count函数进行计算，比如班上有两个90分的并列第一，去重后集合内就只有1个元素，就作为自己的排名

select
	a.score,
	(select count(distinct b.score) from Scores b where b.score >= a.score) as 'rank'
from
	Scores a
order by
	a.score desc

方法二

思路：使用窗口函数

select
	score,
	dense_rank() over (order by score desc) as 'rank'
from
	Scores

窗口函数的格式

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数

窗口函数的详细说明