【SQL】比「昨天」溫度還高的紀錄
這題是 LeetCode 上的 SQL 題目 197. Rising Temperature，任務是:
找出氣溫比「昨天」更高的日期紀錄。
題目
找出與前一天(昨天)相比溫度更高的所有日期的ID。以任意順序傳回結果表。
Table: Weather
| Column Name | Type |
|---|---|
| id | int |
| recordDate | date |
| temperature | int |
請參考以下範例:
Input: Weather table
| id | recordDate | temperature |
|---|---|---|
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
Output:
| id |
|---|
| 2 |
| 4 |
解釋說明:
- 2015-01-02 溫度比前一天 (2015-01-01) 高 (25 > 10)。
- 2015-01-04 溫度比前一天 (2015-01-03) 高 (30 > 20)。
解法
解題核心觀念: 自己JOIN自己。
Self Join
- 從
Weather表取兩份表，取名為today和yesterday。 - 把
today的資料，和前一天yesterday的資料配對起來。
1 | FROM Weather today |
算日期差
- 用
DATEDIFF()算日期差。 DATEDIFF(a, b)是a - b，我們希望today - yesterday = 1，代表today是比yesterday晚一天。- 故
DATEDIFF(today.recordDate, yesterday.recordDate) = 1意思即是today.recordDate - yesterday.recordDate = 1，所以如果差一，我們就可以得到今天跟昨天的配對資料。
1 | JOIN Weather yesterday ON DATEDIFF(today.recordDate, yesterday.recordDate) = 1 |
怎麼配對資料呢?
把today表和yesterday表中recordDate的所有資料，兩兩配對起來。
today.recordDate |
yesterday.recordDate |
DATEDIFF() |
|---|---|---|
| 2015-01-01 | 2015-01-01 | 0 |
| 2015-01-01 | 2015-01-02 | -1 |
| 2015-01-01 | 2015-01-03 | -2 |
| 2015-01-01 | 2015-01-04 | -3 |
| 2015-01-02 | 2015-01-01 | 1 ✅ ←選中這個組合 |
| 2015-01-02 | 2015-01-02 | 0 |
| 2015-01-02 | 2015-01-03 | -1 |
| 2015-01-02 | 2015-01-04 | -2 |
| 2015-01-03 | 2015-01-01 | 2 |
| 2015-01-03 | 2015-01-02 | 1 ✅ ←選中這個組合 |
| 2015-01-03 | 2015-01-03 | 0 |
| 2015-01-03 | 2015-01-04 | -1 |
| 2015-01-04 | 2015-01-01 | 3 |
| 2015-01-04 | 2015-01-02 | 2 |
| 2015-01-04 | 2015-01-03 | 1 ✅ ←選中這個組合 |
| 2015-01-04 | 2015-01-04 | 0 |
然後只留下「差一天」的組合
today.recordDate |
yesterday.recordDate |
DATEDIFF() |
|---|---|---|
| 2015-01-02 | 2015-01-01 | 1 ✅ ←選中這個組合 |
| 2015-01-03 | 2015-01-02 | 1 ✅ ←選中這個組合 |
| 2015-01-04 | 2015-01-03 | 1 ✅ ←選中這個組合 |
比較溫度
用WHERE篩選出today溫度比yesterday溫度高的紀錄。
1 | WHERE today.temperature > yesterday.temperature |
| today id |
today recordDate |
today temperature |
yesterday recordDate |
yesterday temperature |
比較溫度 | 是否保留 |
|---|---|---|---|---|---|---|
| 2 | 2015-01-02 | 25 | 2015-01-01 | 10 | 25 > 10 | ✅ |
| 3 | 2015-01-03 | 20 | 2015-01-02 | 25 | 20 < 25 | ❌ |
| 4 | 2015-01-04 | 30 | 2015-01-03 | 20 | 30 > 20 | ✅ |
選出id
最後只要回傳today的id就可以了。
1 | SELECT today.id |
| id |
|---|
| 2 |
| 4 |
完整程式碼
1 | SELECT today.id |
Input: Weather table
| id | recordDate | temperature |
|---|---|---|
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
Output:
| id |
|---|
| 2 |
| 4 |