Rising Temperature

문제

Table: Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the column with unique values for this table.
There are no different rows with the same recordDate.
This table contains information about the temperature on a certain day.

 

Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The 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 Depth

1. 테이블 셀프 조인 수행하여 weather 테이블 자체의 데카르트 곱 생성하여 일쌍 만듦
FROM weather w1
JOIN weather w2

2. 그 다음 DATEDIFF 함수를 사용하여 이러한 쌍을 연속된 일(day)만 포함하도록 제한
DATEDIFF(w1.recordDate, w2.recordDate) = 1

3. 마지막으로 이러한 연속된 일(day) 쌍을 추가로 필터링하여 두 번쨰 날의 기온이 더 높은 쌍만
포함한다.
where w1.temperature > w2.temperature

4. 결과 ID는 기온이 전날보다 더 높았던 날을 나타낸다.
SELECT w1.id

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

> 데카르트 방법은 권하지 않음.


방법2

1단계. Lag 함수를 사용해 공통 테이블 표현식(CTE) 만들기
 ㄴ 오프셋 1로 함수를 사용하여 얻은 전날의 온도 정렬
 ㄴ 이전 날의 기록날짜를 LAG() 오프셋 1로 함수를 사용하여 얻은 값으로 정렬

with Pre_WeatherDate AS
(
    SELECT
        id,
        recordDate,
        temperature,
        LAG(temperature, 1) OVER (ORDER BY recordDate) AS Pre_temperature,
        LAG(recordDate, 1) OVER (ORDER BY recordDate) AS pre_record
    FROM 
        weather
)
SELECT id
FROM Pre_WeatherDate
WHERE temperature > Pre_temperature
AND recordDate = DATE_ADD(pre_record, INTERVAL 1 DAY)

2단계 온도 및 날짜에 대한 조건이 있는 ID선택
 ㄴ WHERE 절에 조건 실행 temperature > Pre_temperature - 전날의 기온보다 높은 날짜 필터링
 ㄴ recordDate = DATE_ADD(pre_recordDate, INTERVAL 1 DAY) 로 연속된 날짜 비교. DATE_ADD함수를 사용해 1일 간격을 추가하고 pre_recordDate와 recordDate 가 같은지 확인함.
 ㄴ 위의 두 조건을 모두 충족해야하는 AND와 결합하고 ID만 선택하기. 
> 이는 연속된 날짜 비교가 있음을 보장함.

# 2 Depth
1. 데카르트 곱
데카르트 곱(Cartesian Product)이란 두 개 이상의 테이블을 조인할 때 ON 조건 없이 조인하여 모든 행의 가능한 모든 조합을 생성하는 것.
즉, 각 행이 다른 테이블의 모든 행과 결합되어 엄청난 수의 행이 생성됨.

| id | recordDate | temperature | id | recordDate | temperature |
| -- | ---------- | ----------- | -- | ---------- | ----------- |
| 4  | 2015-01-04 | 30          | 1  | 2015-01-01 | 10          |
| 3  | 2015-01-03 | 20          | 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          | 1  | 2015-01-01 | 10          |
| 1  | 2015-01-01 | 10          | 1  | 2015-01-01 | 10          |
| 4  | 2015-01-04 | 30          | 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          | 2  | 2015-01-02 | 25          |
| 2  | 2015-01-02 | 25          | 2  | 2015-01-02 | 25          |
| 1  | 2015-01-01 | 10          | 2  | 2015-01-02 | 25          |
......
앞의 id~temperature까지가 w1
뒤의 id~temperature까지가 w2

2. DATEDIFF()

WHAT? : 두 날짜 간의 차이를 '일(DAY)' 단위로 변환하는 함수

WHY?  :  두 날짜를 비교해서 며칠 차이가 나는지 계산할 때 유용함. 

HOW?  :  INPUT > DATEDIFF('2024-03-10', '2024-03-01')  OUTPUT > 9 

단, 결과는 항상 '일(DAY) 단위로 반환함.

* 시간 차이를 구할 떄는 TIMESTAMPDIFF() 를 사용한다.

 

3.LAG()

WHAT? : 이전 행(Row)의 값을 가져오는 윈도우 함수(Window Function). 이전 행과 비교할때 사용 됨.순차적인 데이터(시간, 날짜, ID 등)에서 이전 값을 가져올 때 유용하기 때문에 사용

WHY? :이전 행과 현재 행을 비교할 때 필요하며 JOIN, SELF JOIN 없이 간단하게 이전 값 참조 가능

HOW ? :

그룹 컬럼 구별없을 시 - LAG(컬럼명, 이동할 행 수, 기본값) OVER (ORDER BY 정렬컬럼)

LAG(temperature, 1) OVER (ORDER BY recordDate) AS PreviousTemperature

 어떻게 동작하는가? 

1) OVER (ORDER BY recordDate) 로 데이터 정렬

2) 정렬된 데이터  기준으로 LAG()가 실행 됨.

3) AS 새로운컬럼명 입력으로 새로운 컬럼으로 반환

 

그룹 컬럼 구별시 - LAG(컬럼명, 이동할 행 수, 기본값) OVER (PARTITION BY 그룹컬럼 ORDER BY 정렬컬럼)

 어떻게 동작하는가? 
1) PARTITION BY 그룹변수 실행하여 각 그룹변수 별로 데이터를 나눔.

2) 각 그룹 내에서  OVER (ORDER BY recordDate) 로 데이터 정렬

3) 정렬된 데이터  기준으로 LAG()가 실행 됨.

4) AS 새로운컬럼명 입력으로 새로운 컬럼으로 반환

 

3-1. LEAD()

 

WHAT/WHY ? : 이후 행의 값을 가져오는 윈도우 함수. 이후 행과 비교할 때 사용됨. (LAG()는 이전행을, LEAD()행은 뒤의 행을 가져오는 것만 다르고 문법은 같음.)

HOW ? LAG()와 동일

WITH문 vs 서브쿼리문
WHERE절에서는 WITH(CTE)가 성능 최적화에 유리한 경우가 많다.
SELECT절에서는 서브쿼리가 더 직관적이고 빠른 경우가 많다.
FROM절에서는 WITH(CTE)를 사용하면 가독성이 좋아진다.

 

주의사항

 - ORDER BY는 반드시 지정해야 함. 

 - PARTITION BY 없이 사용시 전체 데이터 기준으로 적용

 - 첫번째 행 또는 마지막 값은 NULL이 나올수 있음.

 - LAG()와 LEAD()는 Window function 이므로 where절에는 직접 사용할 수 없음.

> with 이나 subquery를 사용해야 함.

 - OFFSET 값(N)을 크게하면 NULL값이 많이 나올 수 있음. LAG(temperature, 10) > 데이터 10개 미만일 경우 대부분 NULL.

 

 

4. DATA_ADD

WHAT ? : 날짜에 특정 시간(일, 월, 년 등)을 더하는 함수

WHY? : 날짜연산, 미래일정생성, 시간 간격 계산(ex. 가입일 + 30일 뒤 만료)

HOW ?

DATE_ADD('2024-03-01', INTERVAL 7 DAY)

1) 기준날짜를 '2024-03-01'로설정

2) INTERVAL 추가할 값 단위를 이용해 원하는 만큼 날짜 증가

3) 결과값으로 새로운 날짜 반환 > 2024-03-08

주의사항

 - NULL 값 주의 : NULL만날시 결과는 NULL임

 - DATE_ADD()에서 음수 값 사용가능 > 이때 DATE_SUB()과 결과가 동일하게 나올 수 있음.

 단, DATE_SUB()에서는 음수 값 사용 불가능.

 

다음 Depth 

 

1. Window 함수란?