【LeetCode】178.分数排名

0 问题描述

   编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值，即：名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

 例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：相同分数采取的是相同排名，下个排名没有间隔。

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

1 数据准备

DROP TABLE IF EXISTS Scores ;
CREATE TABLE Scores (
    `Id` int     comment '',
    `Score` decimal(3,2)  comment ''
) COMMENT 'xxx'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

insert overwrite table Scores
values (1, 3.50),
       (2, 3.65 ),
       (3, 4.00 ),
       (4, 3.85 ),
       (5, 4.00 ),
       (6, 3.65 );

2 思路分析

思路一：开窗函数

select score,
    dense_rank() over (order by score desc) dr
from scores;

开窗函数：
1、row_number() over(order by 列名)
2、rank() over(order by 列名)
3、dense_rank() over(order by 列名)

row_number():行号
  如：1 2 3 4
rank():数据重复会直接跳跃排序
   如：1 2 2 4,会跳过3
dense_rank():数据重复也会连续排序
   如：1 2 2 3 序号连续

 开窗函数详细见：

HiveSQL题——排序函数(row_number/rank/dense_rank)_hive 排序函数-CSDN博客

思路二：常规做法

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

步骤分析

step1：降序排列的分数

select a.Score   
from Scores a
order by a.Score desc-- 直接根据分数降序实现

step2：每个分数对应的排名

  思路：可以先提取出大于等于 score 的所有分数集合 H ，将 H 去重后的元素个数就是 该score 的排名。比如学生A考了98分，大于A分数有[100,99,100,99,98]；去重之后就是[100,99,98]，总共3个元素；所以A同学的排名就是第3名。

先提取满足要求的集合H：

select b.Score 
from Scores b 
where b.Score >= Score ;

再对集合H去重之后的个数作为排名：

select count(distinct b.Score)   -- 去重后的个数当做排名
from Scores b 
where b.Score >= Score  as rank;

上述where条件中的Score 对应的就是a.Score：

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

3 总结

 上述案例介绍了topN场景下的sql解题思路，面试过程中，第二种方式没想到==

参考文章：

https://blog.csdn.net/qq_25443541/article/details/117434962