[MySQL] 날짜 연산 DATE_ADD, DATE_SUB, DATEDIFF (+ LEAD, LAG)

리트 코드 문제를 풀면서 어려웠던 문제의 여러 가지 풀이법을 함께 공유하려고 한다.

 

지시 사항

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.

>> 다음 날의 기온이 이전 날짜의 기온보다 높은 경우의 Id를 모두 구하라는 지시 사항이다.

 

정답코드

WITH PIVOT AS (SELECT *, DATE_ADD(recordDate, INTERVAL 1 DAY)
FROM Weather)

SELECT W.id
FROM PIVOT P
LEFT JOIN Weather W
ON P.nextDay = W.recordDate
WHERE P.temperature < W.temperature;

 

해설

---

처음엔 LAG와 LEAD를 이용해서 풀어볼 수 있지 않을까 생각을 했다. 개념 먼저 잡고 가자.

 

LAG : 뒤처지다.

LEAD : 이끌다. 

이전 행의 값 또는 다음 행의 값을 알고 싶을 때 사용한다. 

 

문제를 통해 예시를 확인해 보자.

 

 

LAG / LEAD 기본 형태

LAG(칼럼, 몇 칸 옮길 것인지) OVER (ORDER BY 칼럼) 
LEAD(칼럼, 몇 칸 옮길 것인지) OVER (ORDER BY 칼럼)

 

 

LAG 활용 예시

SELECT recordDate,
LAG(recordDate, 1) OVER (ORDER BY recordDate) AS "nextDay"
FROM Weather;

LAG는 한 칸씩 뒤로 보낸다고 생각하면 된다.

 

 

LEAD 활용 예시

SELECT recordDate, LEAD(recordDate, 1) OVER (ORDER BY recordDate) AS "theDayBefore"
FROM Weather;

LEAD는 한 칸씩 앞으로 보낸다고 생각하면 된다.

 

 

 

 

이것을 이용해서 처음에 어떻게 풀었냐 하면,

WITH PIVOT AS (SELECT *,
     LAG (temperature, 1) OVER (ORDER BY recordDate) AS theDayBefore
FROM Weather)

SELECT *
FROM PIVOT P
WHERE P.temperature > P.theDayBefore;

LAG를 이용해서 temperature 칼럼을 한 칸 뒤로 미뤄서 새로운 칼럼 'theDayBefore'이라는 이전 날짜의 온도를 만들었고,  다음 날짜(temperature)의 온도와 이전 날짜(theDayBefore)의 온도를 비교해서 id값을 반환하려 했다.

 

 

<결과>

이런 식으로 결과가 나와서 temperature과 theDayBefore의 온도를 비교해서 다음날인 temperature의 온도가 높은 결과만 뽑은 것이다. 사실 이 결과만 보고 submit을 했는데 다른 케이스에서 걸려버렸다.

 

 

| id | recordDate | temperature |
| -- | ---------- | ----------- |
| 1  | 2000-12-14 | 3           |
| 2  | 2000-12-16 | 5           |

위의 케이스의 경우에 12/14일 자와 12/16를 비교하는 꼴이 된다. 이것이 아니라 우리는 바로 그다음 날의 온도와 비교해야 한다. 

 

 

그래서 다른 시도는 LAG를 쓰지 않고 하는 방법으로 시도했다.

 

 

 

다른 시도

WITH PIVOT AS (SELECT *, DATE(recordDate+1) AS nextDay
FROM Weather)

SELECT W.id
FROM PIVOT P
LEFT JOIN Weather W
ON P.nextDay = W.recordDate
WHERE P.temperature < W.temperature;

이것은 WITH절을 사용해서 다음 날에 대한 테이블을 모두 만든 후에 join 하여 where 조건으로 비교했다.

피봇 테이블을 만들면 아래와 같이 다음 날짜를 옆에다 붙일 수 있다.

그러고 나서 nextDay를 기준으로 left join을 한다. (left join의 이유는 마지막 날짜의 경우 데이터 값이 없을 거니까 null값을 넣어주기 위함)

join을 하고 나면 이런 형태로 테이블이 붙게 된다.

여기서 볼 것은 nextDay는 이음새 역할만 한 거라고 생각하면 되고, 보기 쉽게 다시 테이블을 정리하면

이 상태에서 temperature끼리 비교하면 되는 것이다. (이미 where절에 다음 날짜의 온도가 높은 경우만 조회하라고 넣어놔서 2개만 나오는 것)

 

 

이때 SELECT 절에서 P.id를 선택하는 것이 아닌 W.id를 선택해야 하는 이유는?

어제보다 더 높은 temperature를 갖고 있으면 ‘그’ id를 가져와야 한다. 그렇기 때문에 W.id를 가져와야한다.

 

<정답 결과>

 

 

한 가지 주목해야 할 점은 여기서 야매로(?) 넣은 것이 보이는데 날짜의 다음날을 어떻게 구해야 될지 몰라서 무작정 DATE로 감싸서 비슷한 포맷으로 만들었다.

 

그런데 이 코드 역시 케이스 테스트에서 막혔다... 정확한 문제가 뭔지는 모르겠지만 순서만 다르게 나온 것 같은데 문제의 원인을 찾기가 힘들었다.

 

그래서 찾다 보니 DATE를 바로 감싸는 게 아니고 날짜를 더하는 함수가 따로 있었다...!

 

---

 

 

 

날짜 더하기 빼기

1. DATEDIFF

2. DATE_ADD

3. DATE_SUB

 

 

1. DATEDIFF

날짜의 차이를 구해주는 함수이다.

하나의 예시를 보자면,

 

DATEDIFF(A, B) = 1

A - B를 했을 때 1이 나오는 경우라고 생각하면 된다.

 

중요한 것은 양수가 나오려면 A가 "나중 날짜"가 되어야 한다.

SELECT nextDay.id AS 'Id'
FROM weather nextDay
JOIN weather theDayBefore 
ON DATEDIFF(nextDay.recordDate, theDayBefore.recordDate) = 1
AND nextDay.Temperature > theDayBefore.Temperature;

이번 문제의 경우 '다음 날짜'가 '이전 날짜'보다 온도가 올라간 경우를 구하는 문제이기 때문에

Join을 할 때 on 조건으로 '다음 날짜'와 '이전 날짜'의 차이가 '하루' 차이인 경우 join을 진행해 주고

조건을 추가(AND)해서 다음날(nextDay)의 온도가 이전(theDayBefore)의 온도보다 높은 경우에 다음날(nextDay)의 id를 조회해 준다.

 

 

 

2.DATE_ADD 

날짜를 더하는 함수(DATE_ADD)

 

DATE_ADD(기준 날짜, INTERVAL 숫자 DAY)
# DATE_ADD('2022-05-04', INTERVAL 1 DAY)

DATE_ADD의 경우 며칠을 더 할지 ‘INTERVAL 숫자’로 표현한다.

하루 단위로만 더할 수 있는 것이 아닌 초, 분, 시간 단위로도 더할 수 있다.

 

 

1초 더하기

DATE_ADD(기준 날짜, INTERVAL 숫자 SECOND)

1분 더하기

DATE_ADD(기준 날짜, INTERVAL 숫자 MINUTE)

1시간 더하기

DATE_ADD(기준 날짜, INTERVAL 숫자 HOUR)

 

 

 

 

3. DATE_SUB

DATE_ADD와 비슷하게 사용할 수 있다.

DATE_SUB의 경우엔 기준 날짜로부터 몇 일 빼주는 것이다. 이것 역시 초, 분, 시간 단위로도 사용이 가능하다.

DATE_SUB(기준 날짜, INTERVAL 숫자 DAY)

 

 

결론

DATE로 무작정 감싸서 하루를 더해줬는데 'DATE_ADD'를 사용해서 사용하니 잘 해결된다. 

WITH PIVOT AS (SELECT *, DATE_ADD(recordDate, INTERVAL 1 DAY)
FROM Weather)

SELECT W.id
FROM PIVOT P
LEFT JOIN Weather W
ON P.nextDay = W.recordDate
WHERE P.temperature < W.temperature;

 

 

 

 

REF

---

[쉽게 쓴 SQL] LAG, LEAD 함수

[MySQL] 시간 더하기, 빼기 (DATE_ADD, DATE_SUB 함수)