前言: 排名的影響
每個人從小到大,考過無數的考試、比過無數的競賽,有些人或許透過名次獲得自信,有些人卻因為名次而感到自卑,可見名次的影響力在我們每個人成長的過程中無處不在。
如同上面講的,只要是競賽類型這種需要比較的活動,排名次都是一件不可避免的事情,我們每個階段都會遇到這個情境(考試、考績、比賽…族繁不及備載),它早已充斥著我們的生活。
挑戰 SQL 的學習之路接續進行,讓我們一起探索排名的這個主題吧~
題目
-- Table: Scores
-- +-------------+---------+
-- | Column Name | Type |
-- +-------------+---------+
-- | id(PK) | int |
-- | score | decimal |
-- +-------------+---------+
排序條件
- 分數高到低
- 同分的話,排名要相同
- 同名後的名次是連續的整數,排名間不會有空隙
-- Ex:
-- +----+-------+
-- | id | score |
-- +----+-------+
-- | 1 | 3.50 |
-- | 2 | 3.65 |
-- | 3 | 4.00 |
-- | 4 | 3.85 |
-- | 5 | 4.00 |
-- | 6 | 3.65 |
-- +----+-------+
-- Output:
-- +-------+------+
-- | score | rank |
-- +-------+------+
-- | 4.00 | 1 |
-- | 4.00 | 1 |
-- | 3.85 | 2 |
-- | 3.65 | 3 |
-- | 3.65 | 3 |
-- | 3.50 | 4 |
-- +-------+------+
解題過程
老樣子,一開始先憑印象寫出後執行看看長怎麼樣
SELECT score, RANK() FROM Scores ORDER BY score DESC;
-- Output
Runtime Error
window function rank requires an OVER clause
LINE 2: SELECT score, RANK() FROM Scores ORDER BY score DESC;
^
看起來並不是我所想的那樣簡單,從這個錯誤訊息看出,PostgreSQL 應該是有提供這樣子的功能,只是並不是簡單的 RANK()
就好了,需要搭配 OVER
的關鍵字進行才可以發揮作用
上 Google 查了後發現應該是這樣搭配 OVER
才會起作用
SELECT score, RANK() OVER(ORDER BY score DESC) FROM Scores ORDER BY score DESC;
-- Output
-- | score | rank |
-- | ----- | ---- |
-- | 4 | 1 |
-- | 4 | 1 |
-- | 3.85 | 3 | => 這裡開始就不是我要的
-- | 3.65 | 4 |
-- | 3.65 | 4 |
-- | 3.5 | 6 |
這段執行後發現只剩下排名並不是接續的整數,所以可能不是 RANK()
這個函數或是可能還需要額外處理。另外, OVER
裡面已經排序過了,因此最後的 ORDER BY
應該可以直接去掉才對
後來又再查詢,使用 DENSE_RANK()
這個函數,並將排名欄位定為 rank
以符合題目要求
SELECT
score,
DENSE_RANK() OVER(ORDER BY score DESC) AS rank
FROM Scores;
-- Output
-- | score | rank |
-- | ----- | ---- |
-- | 4 | 1 |
-- | 4 | 1 |
-- | 3.85 | 2 |
-- | 3.65 | 3 |
-- | 3.65 | 3 |
-- | 3.5 | 4 |
成功了!!!!!
結果
知識點
- Window Function
根據 PostgreSQL 正體中文使用手冊裡面提到
窗函數(window function)提供了在一個資料表中,進行資料列與資料列之間的關連運算。這部份可以和彙總函數的功能相呼應。然而,窗函數並無法像彙總函數一樣,把多個資料列運算合併為單一資料列的結果。取而代之的是,這些資料列仍然是分開並列的狀態。在這樣的情境下,窗函數能讓查詢結果的每一個資料列,都得到更多資訊。
經過我的理解,應該是類似於 GROUP BY
,但會分開呈現的一種方法。
結構如下:
-- 語法結構
<WINDOW_FUNC> OVER (
PARTITION BY <分區欄位名> => 用於分組,若沒有通通視為一組
ORDER BY <排序欄位名>
)
根據我們題目描述的,我們要根據 score
欄位排名,因此,我們這部分就寫成 OVER(ORDER BY score DESC)
- RANK()
RANK()
: 排名,可以並列排名DENSE_RANK()
: 同RANK()
,但是會是接續整數ROW_NUMBER()
: 排名,但同名次的仍然會排名
心得
在成功解決上一個簡單的題目後(題目連結),我開始感覺到自信了(os: 心態開始膨脹)。於是我抱持著這樣地自信挑選了這個 Medium 難度的題目 — Rank Scores。雖然這題的難度被標記為 Medium,但我覺得它並不需要過於冗長或複雜的解法,因此,我選擇將這題做為這次的挑戰。
這個題目其實在我看來並不算太難,只要對 SQL 語法熟悉,應該就能夠應對自如。題目的情境算常見,而且題目的英文也不長,因此,我覺得理解題目應該不會太困難。
在解這題的過程中,我學會了 RANK()
的相關用法。雖然在書本中可能曾經見過,當時可能也以為這是個相當直觀、簡單的函數。然而,但我認為看過固然重要,但實際做過(或犯錯過)才會使我更印象深刻、使知識更為牢固。這次的挑戰讓我逐步嘗試並修正,這種一步一步的試錯過程對我的學習效果來說是相當有益的。
參考資料
PostgreSQL 正體中文使用手冊 — 3.5. 窗函數
PostgreSQL rank() over, dense_rank(), row_number() 的区别
SQL 窗口函數 Window Function:三大應用快速教學 by 好豪