[LeetCode]178. Rank Scores

問題描述

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

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

For example, given the above Scores table, your query should generate the following report (order by highest score):

score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example Rank.

翻譯

請撰寫一段 SQL 查詢排名以下分數，若兩個分數趨近一樣，將並列為同一名次，下一個名次取次排名，換言之，並列後的分數後不會空出排名。
您必須產出如以下的依據分數從高至低的查詢結果。
注意：如果是以 MySql 作答，你可以在 Rank 前後加入撇號(`)來跳脫保留字當作是欄位名稱。

解題思維

用法： [ROW_NUM()|RANK()|DENSE_RANK()] OVER([PARTITION BY 欄位名稱] ORDER BY 欄位名稱 [DESC|ASC])
解題是利用 Oracle 分析函數進行排名，詳細可參考[Oracle SQL] rank(), dense_rank(), row_number()分析函數用法

解題報告

Level: Medium
Runtime: 544 ms, faster than 93.42% of Oracle online submissions for Rank Scores.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Rank Scores.

程式完整解題

/* Write your PL/SQL query statement below */
SELECT SCORE
      ,DENSE_RANK() OVER (ORDER BY SCORE DESC) AS RANK
  FROM SCORES;

SQL Schema

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2))
Truncate table Scores
insert into Scores (Id, Score) values ('1', '3.5')
insert into Scores (Id, Score) values ('2', '3.65')
insert into Scores (Id, Score) values ('3', '4.0')
insert into Scores (Id, Score) values ('4', '3.85')
insert into Scores (Id, Score) values ('5', '4.0')
insert into Scores (Id, Score) values ('6', '3.65')