JobHoppoer-SQL-178. Rank Scores

很有代表性的一道题

类型：从已有的 columns 中 衍生出 新的 column；需要UDF；但是 计算的逻辑 不是简单 加减乘除，UDF 的设计需要一些 算法，有一点点 动态规划。

考点：

- 定义 变量

- 定义 lambda 函数

- 复杂度的考察

-- An intuitive way to get the Rank column. 
-- ORDER BY score, then go row by row to assign the value of Rank for each row. Need to write a function. 
-- Time complexicity is O(N(logN) + N), basically sorting complexicity.

SELECT score, (@rank := @rank + (@prev != (@prev := score))) as 'Rank'
FROM Scores, (SELECT @rank := 0, @prev := -100) as init
ORDER BY score DESC

第二个方法，其实思路是比较不那么直接的，但也是一种方法吧。我一开始是没想到这种方法的，是看了评论区的讨论。所以就姑且借鉴，同时写得更美观一些。

-- Join 2 tables using a range predicate, then count() by id.
-- This is a different way of thinking and solution. 
-- For every row, it scans the distince table (as the comparing reference). 
-- So the time complexicity is high, it is a cartesian product (O(N^2)).

SELECT scores.score, count(distinctScores.score) as 'Rank'
FROM scores, (SELECT DISTINCT scores.score FROM scores) as distinctScores
WHERE scores.score <= distinctScores.score
GROUP BY scores.id
ORDER BY scores.score DESC;