[Mysql] SELF JOIN

(해당 포스트는 Inflearn - [백문이불여일타] 데이터 분석을 위한 중급 SQL 강의록입니다.)

SELF JOIN

실전 문제

[Leet Code] 197. Rising Temperature

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

Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday). Return the result table in any order.

The query result format is in the following example:

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+
In 2015-01-02, temperature was higher than the previous day (10 -> 25).
In 2015-01-04, temperature was higher than the previous day (30 -> 20).

결국 전날보다 높은 기온을 보이는 날짜의 id를 출력하라는 문제. 정답 출력 시 Result set의 순서는 상관 없음.

나의 풀이

생각보다 어렵지 않다는 생각으로 적어나간 나의 풀이는 아래와 같았다.

select w.Id 
from Weather as w
join Weather as p_w
on w.recordDate = p_w.recordDate+1
where p_w.Temperature < w.Temperature

테이블 하나로 셀프 조인을 해야하므로 alias 설정에 신경을 썼다.
w 라고 alias를 준 테이블은 오늘의 날씨 (today's weather)로,
p_w라고 alias를 준 테이블은 어제의 날씨 (previous day's weather)로 정했다.

이대로 run code를 해보니 곧잘 Accepted 되었고, 문제가 없을 것으로 판단하여 submit하였다.
하지만 submit을 해보니, expected 답안과 내가 제출한 답안에는 차이가 있어 결국 Wrong Answer가 되고 말았다.

해설 강의

내가 너무 쉽게 생각한 부분은 Date+1 부분이었다.
파이썬인지 장고인지 Date+1이 정상 작동했던 기억이 있어(기억의 조작인지는 나도 잘 모르겠으나) 이렇게 간단하게 +1만 해도 충분히 될 것이라고 판단하여 적었는데, 역시 그 부분이 문제였다.

MySQL 날짜 데이터 더하기, 빼기 함수

• 날짜 더하기: DATE_ADD (기준날짜, interval)
  1) select DATE_ADD (now(), interval 1 second)
  2) select DATE_ADD (now(), interval 1 minute)
  3) select DATE_ADD (now(), interval 1 hour)
  4) select DATE_ADD (now(), interval 1 day)
  5) select DATE_ADD (now(), interval 1 month)
  6) select DATE_ADD (now(), interval 1 year)

• 날짜 빼기: DATE_SUB (기준날짜, interval)
  ex) select DATE_SUB (now(), interval 1 second) DATE_ADD와 단위 연동

• DATE_ADD 함수에서 interval 값을 음수로만 작성해도 DATE_SUB 함수의 효과
  select DATE_ADD (now(), interval -1 second/minute/hour/day/month/year)
  ==
  select DATE_SUB (now(), interval 1 second/minute/hour/day/month/year)

위를 활용하여 다시 작성한 mysql 쿼리는 아래와 같았다.

select w.Id 
from Weather as w
join Weather as p_w
on w.recordDate = DATE_ADD(p_w.recordDate, interval 1 day)
where p_w.Temperature < w.Temperature

이번에는 submit 시 정답으로 인정 되었다.

lesson of the day
1. 셀프 조인을 사용할 때에는 내가 생각하는 용도에 따른 알리아스를 명확하게, 제대로 주자.
2. DATE_ADD 함수를 잊지 말자.