197. Rising Temperature
表格: Weather
+---------------+---------+
+---------------+---------+
| 列名 | 類型 |
+---------------+---------+
| id | int |
| recordDate | date |
| temperature | int |
+---------------+---------+
id 是此表中具有唯一值的列。 沒有不同的行具有相同的 recordDate。 此表包含某一天的溫度信息。
編寫一個解決方案來查找所有溫度比前一天（昨天）更高的日期的 Id。
你可以按任意順序返回結果表。
id 是此表中具有唯一值的列。 沒有不同的行具有相同的 recordDate。 此表包含某一天的溫度信息。
編寫一個解決方案來查找所有溫度比前一天（昨天）更高的日期的 Id。
你可以按任意順序返回結果表。
範例：
輸入：
Weather 表:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
輸出：
輸出：
+----+
| id |
+----+
| 2 |
| 4 |
+----+
解釋： 在 2015-01-02，溫度比前一天高 (10 -> 25)。 在 2015-01-04，溫度比前一天高 (20 -> 30)。
解釋： 在 2015-01-02，溫度比前一天高 (10 -> 25)。 在 2015-01-04，溫度比前一天高 (20 -> 30)。
MySQL
-- Write your MySQL query statement below
SELECT w1.id
FROM Weather w1
JOIN Weather w2 ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature > w2.temperature;
359ms
Pandas
import pandas as pd
def rising_temperature(weather: pd.DataFrame) -> pd.DataFrame:
# Convert the recordDate column to datetime to ensure proper date operations
weather['recordDate'] = pd.to_datetime(weather['recordDate'])
# Sort the dataframe by recordDate to ensure the dates are in order
weather = weather.sort_values(by='recordDate')
# Use the shift method to compare the temperature with the previous day's temperature
weather['prev_temp'] = weather['temperature'].shift(1)
weather['prev_date'] = weather['recordDate'].shift(1)
# Filter the rows where the temperature is higher than the previous day's temperature
# and the previous date is exactly one day before the current date
rising_temp = weather[(weather['temperature'] > weather['prev_temp']) &
(weather['recordDate'] == weather['prev_date'] + pd.Timedelta(days=1))]
# Select only the id column for the result
result = rising_temp[['id']]
return result
70.35MB, 445ms
PostgreSQL
-- Write your PostgreSQL query statement below
SELECT w1.id
FROM Weather w1
JOIN Weather w2
ON w1.recordDate = w2.recordDate + INTERVAL '1 day'
WHERE w1.temperature > w2.temperature;
0.00MB, 257ms
