[LeetCode] 197. Rising Temperature (JOIN)

< 문제 >

[SQL50] 197. Rising Temperature (JOIN)

---

---

< 풀이 >

SELECT A.id
FROM Weather A LEFT JOIN 
    (SELECT id, 
            LAG(recordDate, 1) OVER(ORDER BY recordDate) AS yesterday, 
            LAG(temperature, 1) OVER(ORDER BY recordDate) AS prev_temp 
     FROM Weather) B ON A.id = B.id
WHERE temperature > B.prev_temp AND B.yesterday = recordDate - 1;

---

LAG

이전 행의 값을 가져올 수 있다. 

SELECT t, val, LAG(val) AS 'lag'
FROM series
ORDER BY t;

 

실행 결과 >

t	val	lag
12:00:00	100	NULL
13:00:00	125	100
14:00:00	132	125
15:00:00	145	132
16:00:00	140	145
17:00:00	150	150
18:00:00	200	200

---

LAG(val, m, n)

- m번째 이전 값을 가져오되 이 값이 없으면 n으로 출력한다.

SELECT t, val, LAG(val, 1, 0) AS 'lag'
FROM series
ORDER BY t;

 

실행 결과 >

t	val	lag
12:00:00	100	0
13:00:00	125	100
14:00:00	132	125
15:00:00	145	132
16:00:00	140	145
17:00:00	150	150
18:00:00	200	200

 

 

---

LEAD

이후 행의 값을 가져올 수 있다. 

SELECT t, val, LEAD(val) AS 'lead'
FROM series
ORDER BY t;

실행 결과 >

t	val	lead
12:00:00	100	125
13:00:00	125	132
14:00:00	132	145
15:00:00	145	140
16:00:00	140	150
17:00:00	150	200
18:00:00	200	NULL

 

---

LEAD(val, m, n)

- m번째 이후 값을 가져오되 이 값이 없으면 n으로 출력한다.

SELECT t, val, LEAD(val, 1, 0) AS 'lead'
FROM series
ORDER BY t;

 

실행 결과 >

t	val	lead
12:00:00	100	125
13:00:00	125	132
14:00:00	132	145
15:00:00	145	140
16:00:00	140	150
17:00:00	150	200
18:00:00	200	0

 

 

 

 

참고

lag_lead