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).
방법 1. ID 를 기준으로 조인 함수 사용하기
SELECT TODAY.ID FROM Weather AS TODAY
inner join Weather as YESTERDAY on YESTERDAY.ID+1 =TODAY.ID
WHERE YESTERDAY.temperature<TODAY.temperature
*SELF JOIN 을 사용해야하는데 스스로 생각하기 어려웠던 부분은
"YESTERDAY.ID+1 =TODAY.ID"를 생각해 내기 였다.
INPUT:["id", "recordDate", "temperature"]}"
[[1, "2015-01-01", 10],
[2, "2015-01-02", 25],
[3, "2015-01-03", 20],
[4, "2015-01-04", 30]]}}
OUTPUT:
"headers": ["ID"], "values": [[2], [4]]}
방법 2 날짜 부분 기준으로 함수 작성하기
*알아야 하는 함수 개념
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 DAY)
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH)
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR)
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR)
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR)[정답]
SELECT TODAY.ID FROM Weather AS TODAY
inner join Weather as YESTERDAY on DATE_ADD(YESTERDAY.recordDate,INTERVAL 1 DAY) =TODAY.recordDate
WHERE YESTERDAY.temperature<TODAY.temperature
#데이터 리안 SQL 부트캠프를 수강하며 작성한 게시글 입니다
