SQL 코드카타: Rising Temperature

Rising Temperature

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the column with unique values for this table.
There are no different rows with the same recordDate.
This table contains information about the temperature on a certain day.

 

Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The result format is in the following example.

 

Example

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  |
+----+
Explanation: 
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

---

LAG 함수를 사용한 풀이

SELECT id
FROM (
    SELECT id,
        recordDate,
        temperature,
        LAG(temperature) OVER (ORDER BY recordDate) AS prev_temperature,
        LAG(recordDate) OVER (ORDER BY recordDate) AS prev_date
    FROM Weather
    ) t
WHERE DATEDIFF(recordDate, prev_date) = 1 AND temperature > prev_temperature;

• OVER
  • LAG() 함수에 "어떤 기준으로 이전 값을 가져올지" 명확히 지정
  • OVER 없이 LAG() 함수는 작동하지 않음
  • 내부에서 정렬 기준(ORDER BY) 지정 가능

-- 🌟 하루가 아니라 이틀이라면?
LAG(temperature, 2)

---

INTERVAL을 사용한 풀이

SELECT w1.id
FROM Weather w1
JOIN Weather w2 
-- 현재 행의 날짜가 w2 행의 날짜보다 하루 뒤인지 확인
ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY) 
WHERE w1.temperature > w2.temperature;

• INTERVAL
  • 날짜 또는 시간 차이를 계산하거나 특정 날짜에 기간을 더하거나 빼는 기능 수행
• LAG() 함수를 쓸 수 없는 경우 대안책
• JOIN을 하기 때문에 상대적으로 느림