【刷题】【力扣】【178】【中等】分数排名

文章目录

• • @[toc]
• 题目描述
• 示例
• • 输入
  • 输出
• MySQL实现
• • 方法1
  • 方法2
  • 方法3
• Pandas实现

题目描述

• 表：Scores

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

• 编写一个解决方案来查询分数的排名，排名按以下规则计算:
  • 分数应按从高到低排列
  • 如果两个分数相等，那么两个分数的排名应该相同
  • 在排名相同的分数后，排名数应该是下一个连续的整数，换句话说，排名之间不应该有空缺的数字
• 按score降序返回结果表

示例

输入

• 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    |
+-------+------+

MySQL实现

方法1

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

方法2

select t1.score,
       (select count(distinct t2.score)
        from Scores t2
        where t2.score >= t1.score) as 'rank'
from Scores t1
order by t1.score desc;

方法3

select t1.score, count(distinct t2.score) as 'rank'
from Scores t1
         inner join Scores t2 on t2.score >= t1.score
group by t1.id, t1.score
order by t1.score desc;

Pandas实现

# -*- coding: utf-8 -*-
# @Time     : 2025/1/11 23:11
# @Author   : 从心
# @File     : 178.py
# @Software : PyCharm

import pandas as pd


def order_scores(scores: pd.DataFrame) -> pd.DataFrame:
    scores['rank'] = scores['score'].rank(method='dense', ascending=False)

    return scores.sort_values('score', ascending=False).drop('id', axis=1)