[SQL]전날보다 더 더웠던 날 찾기: Self Join vs WITH(CTE)로 깔끔 정복

오늘 문제 풀다가 두 가지를 새로 알았다.

1. 테이블을 자기 자신과 조인(Self Join)할 수 있다
2. WITH 문(CTE)로 임시 테이블을 만들어 단계별로 쿼리를 짤 수 있다

이 두 가지를 “전날 온도와 비교해서 더 따뜻했던 날짜의 id를 구하는 문제”로 정리해본다.

---

문제 요약

Weather(id, recordDate, temperature) 테이블이 있다.

각 날짜의 온도가 전날보다 높았으면 그 날짜의 id를 반환하라.

• recordDate는 날짜,
• 날짜는 중복 없음(= 하루에 한 행)

---

 

스키마 (예시)

ID	recordDate	temperature
1	2021-01-01	10
2	2021-01-02	25
3	2021-01-03	20
4	2021-01-04	30

정답은 2, 4 (각각 전날보다 따뜻함)

---

방법 1) Self Join (자기 자신과 조인)

MySQL 버전 (DATEDIFF 이용)

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE w1.temperature > w2.temperature;

핵심 아이디어

• 같은 테이블을 두 번 부른다: 오늘(w1) vs 어제(w2)
• DATEDIFF(today, yesterday) = 1이면 날짜가 정확히 하루 차이
• 온도 비교해서 오늘이 더 크면 w1.id 반환

다른 DB 호환 팁

• MySQL(대안): w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
• PostgreSQL: w1.recordDate = w2.recordDate + INTERVAL '1 day'
• Oracle: w1.recordDate = w2.recordDate + 1 (날짜 + 정수 = 일수 더하기)
• SQL Server: w1.recordDate = DATEADD(day, 1, w2.recordDate)

---

 

방법 2) WITH 문(CTE)로 “복사” 후 조인

CTE(공통 테이블 표현식)는 쿼리 안에서만 쓰는 임시 테이블이라고 보면 편하다.

WITH w AS (
  SELECT id, recordDate, temperature
  FROM Weather
)
SELECT a.id
FROM w a
JOIN w b
  ON DATEDIFF(a.recordDate, b.recordDate) = 1
WHERE a.temperature > b.temperature;

느낌

• Weather를 w라는 이름으로 임시 저장
• 읽기 쉬움(가독성 👍), 복잡한 쿼리를 단계로 쪼갤 때 유용

✅ CTE 지원 여부

• RDB 대부분 지원(O): Oracle(11g+), MySQL(8.0+), MariaDB(10.2+), PostgreSQL, SQL Server, SQLite
• NoSQL(Mongo 등)은 SQL이 아니라서 CTE 자체는 없음(대신 Aggregation Pipeline 사용)

---

보너스) 윈도우 함수로 한 방에 (MySQL 8.0+, PostgreSQL, Oracle 등)

LAG로 “이전 행의 값”을 끌어와 비교한다.

SELECT id
FROM (
  SELECT id,
         recordDate,
         temperature,
         LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp
  FROM Weather
) t
WHERE temperature > prev_temp;

장점

• 조인 없이도 “전날 값”을 바로 비교 (깔끔함)
• 정렬 기준만 정확하면 구현이 매우 간단

---

내가 몰랐던 포인트 (정리)

1. Self Join 가능조인 조건만 잘 걸어주면 된다.
2. 같은 테이블을 두 번 가져와서 “오늘 vs 어제”처럼 서로 비교할 수 있다.
3. WITH(CTE)
   
   • 긴 서브쿼리를 깔끔하게 이름 붙여서 재사용
   • 단계별로 쿼리를 나눌 때 가독성/유지보수성 압승
   • MySQL 8.0 이상부터 사용 가능(5.x는 불가!)
4. 쿼리 안에서만 쓰는 임시 테이블.
5. 윈도우 함수(LAG)다만 DB 버전/엔진에 따라 지원 여부가 다르니 확인 필요.
6. “이전 행” 비교는 사실 조인 없이도 된다.

---

실무 주의사항

• 날짜 구멍(Gap): 전날 데이터가 아예 없으면(예: 주말 제외 데이터) self join/LAG 모두 비교 불가 → 해당 행은 결과에서 빠지는 게 정상
• 인덱스: recordDate에 인덱스를 주면 조인/정렬 성능이 좋아짐
• 타임존/타입: recordDate가 DATE인지 DATETIME인지에 따라 비교 함수 주의

---

TL;DR

• 전날 비교 문제는 Self Join(가장 범용적) 또는 LAG(가독성 최고)로 해결
• **WITH(CTE)**로 쿼리를 단계적으로 써주면 읽기 좋고 유지보수 편함
• DB/버전에 따라 함수가 살짝 다를 수 있으니 문법만 맞춰주면 끝!