[LeetCode] MySQL 197. Rising Temperature (SELF JOIN 예제)

Rising Temperature - LeetCode

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature on a certain day.

Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example.

 

Example 1 :

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).

• DATE_SUB() 함수를 이용하면 날짜를 빼줄 수 있다.
  • interval : 얼마나 시간을 계산해줄 것인지 지정

 

• SELF JOIN을 통해 새로운 yesterday 테이블을 생성하고 연결해준다.
  • 비교 할 today 테이블의 날짜값을 모두 하루 뺀 날짜를 기준으로 테이블을 JOIN한다.

yesterday ( = today - 1 day )	today
2022-10-11	2022-10-12
2022-10-12	2022-10-13
2022-10-13	2022-10-14
2022-10-14	2022-10-15

• 색칠한 부분에 대한 temperature 값을 비교하게 되는 것이다 !
  • 이전 날짜보다 지금 날짜가 온도가 더 높으면 현재 날짜 today의 ID를 조회한다.

[MySQL]

# Write your MySQL query statement below
SELECT today.id
FROM Weather AS today 
    INNER JOIN Weather AS yesterday 
    ON yesterday.recordDate = DATE_SUB(today.recordDate, interval 1 DAY)

WHERE today.temperature > yesterday.temperature

 

MYSQL 시간 더하기, 빼기 함수

DATE_ADD(기준날짜, INTERVAL)
SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND)
SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE)
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR)
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH)
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)
SELECT DATE_ADD(NOW(), INTERVAL -1 SECOND)

DATE_SUB(기준날짜, INTERVAL)
SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND)