Postgres 실습 온도 상승 문제 해결 방법 쉽게 배우기

Tables

The Problem

예시

해결책

The trick lies in the self-join, which allows us to compare values within the same table.

Imagine that ww.recordDate represents today's date, and we want to find a date which is one day before. This means that ww.recordDate is today, and w.recordDate is yesterday. With this presumption, we expect that ww.temperature (today) is higher than w.temperature (yesterday).

SELECT ww.id
FROM Weather w
JOIN Weather ww on w.recordDate = (ww.recordDate - 1)
WHERE ww.temperature > w.temperature;

Another way to solve this is by using the LAG window function. We will prepare all the necessary data in one result set. In the second step, we compare the temperatures and dates. It's a similar approach to a self-join but uses the LAG function.

WITH PreviousWeatherData AS
(
    SELECT 
        id,
        recordDate,
        temperature, 
        LAG(temperature, 1) OVER (ORDER BY recordDate) AS PreviousTemperature,
        LAG(recordDate, 1) OVER (ORDER BY recordDate) AS PreviousRecordDate
    FROM 
        Weather
)
SELECT 
    id 
FROM 
    PreviousWeatherData
WHERE 
    temperature > PreviousTemperature
AND 
    recordDate = PreviousRecordDate + 1;

데이터

만약 직접 시도해보고 싶다면 데이터를 생성하고 삽입하는 쿼리를 추가할게요.

CREATE TABLE Weather (
    id INT,
    recordDate DATE,
    temperature INT,
    PRIMARY KEY (id)
);

INSERT INTO Weather (id, recordDate, temperature) VALUES
(1, '2015-01-01', 10),
(2, '2015-01-02', 25),
(3, '2015-01-03', 20),
(4, '2015-01-04', 30);

Leetcode challenge — https://leetcode.com/problems/rising-temperature/description/