【leetcode数据库】分数排名

题目链接

分数排名

相关笔记

【分数排名】官方题解

方法一：利用Python里的Pandas库

rank()可以帮助计算沿轴的数值数据排名，可以将方法参数method设置为dense来分配密集排名。密集排名意味着当存在并列的值时，下一个排名不会跳过。相反，所有并列的分数都被分配相同的排名，并且下一个排名递增1。

import pandas as pd
#按照降序对‘score’列应用密集排名
scores['rank]=scores['score'].rank(method='dense',ascending=False)
#对密集排名后的score按升序排列
scores[['score','rank']].sort_values('score',ascending=False)

方法二：MYSQL：dense_rank窗口函数

SELECT S.score,
	DENSE_RANK() OVER(ORDER BY S.score DESC) AS 'rank'
FROM Scores S;

方法三：MYSQL:使用count(distinct…)的相关子查询

使用相关子查询。

• 对于来自Scores表的每个分数，选择再Scores表中大于或等于此分数的不同分数的数量。（得到排名，如：2 1 3 9 4 9 6，要找3的排名是多少，大于等于3的集合是：3 9 4 6，一共有4个，故’3’在降序排名中，其排名为第4）
• 按照score对结果集进行排序。

SELECT 
	S1.score,
	(
	SELECT 
		COUNT(DISTINCT S2.score)
	FROM
		Score S2
	WHERE
		S2.score>=S1.score
	)AS 'rank'
FROM
	Scores S1
ORDER BY 
	S1.score DESC;

方法4：使用inner join和count（distinct…）

这个方法与方法3相同，但是实现方式不同。方法三是每对一个score进行排名时都要进行一次排名计算，方法4则是一次性就将所有排名计算完成。

• 将Score表与自身连接，以便我们得到所有分数大于或等于此分数的所有行。
• 将查询行按id和score值进行分组。
• 计算唯一的分数和数量，它应该大于或等于连接条件中使用的分数（也就是排名）。
• 按````score```值对结果集进行排序。

SELECT
	S.score,
	COUNT(DISTINCT T.score) AS 'rank'
FROM
	Scores S
	INNER JOIN Scores T ON T.score<=S.score
GROUP BY T.score
ORDER BY T.score DESC

为了更清楚地看到上述查询的工作原理，可以看以下子查询实例：

SELECT
  S.id AS S_ID,
  S.score AS S_Score,
  T.id AS T_ID,
  T.score AS T_Score
FROM
  Scores S
  INNER JOIN Scores T ON S.score <= T.score
ORDER BY
  S.id,
  T.score;

+------+---------+------+---------+
| S_ID | S_score | T_ID | T_score |
+------+---------+------+---------+
|    1 |    3.50 |    1 |    3.50 |
|    1 |    3.50 |    2 |    3.65 |
|    1 |    3.50 |    6 |    3.65 |
|    1 |    3.50 |    4 |    3.85 |
|    1 |    3.50 |    3 |    4.00 |
|    1 |    3.50 |    5 |    4.00 |
|    2 |    3.65 |    2 |    3.65 |
|    2 |    3.65 |    6 |    3.65 |
|    2 |    3.65 |    4 |    3.85 |
|    2 |    3.65 |    3 |    4.00 |
|    2 |    3.65 |    5 |    4.00 |
|    3 |    4.00 |    3 |    4.00 |
|    3 |    4.00 |    5 |    4.00 |
|    4 |    3.85 |    4 |    3.85 |
|    4 |    3.85 |    3 |    4.00 |
|    4 |    3.85 |    5 |    4.00 |
|    5 |    4.00 |    3 |    4.00 |
|    5 |    4.00 |    5 |    4.00 |
|    6 |    3.65 |    2 |    3.65 |
|    6 |    3.65 |    6 |    3.65 |
|    6 |    3.65 |    4 |    3.85 |
|    6 |    3.65 |    3 |    4.00 |
|    6 |    3.65 |    5 |    4.00 |
+------+---------+------+---------+

可以看到，在合适的分组上使用COUNT(DISTINCT...)时，得出的就是正确的结果。
以S_ID=1；S_score=3.5为例，表中列出的即为S_score<=T_score的所有组合行，利用COUNT(DISTINCT...)后，去掉S_ID=1，S_score=3.5分组中重复的T_score，剩下4个>=S_score的T_score，故S_score在降序的排序中，排名为4。