[SQL/solvesql LV2] 이틀 연속 미세먼지가 나빠진 날

https://solvesql.com/problems/bad-finddust-days-in-a-row/

https://solvesql.com/problems/bad-finddust-days-in-a-row/

 
서울숲 일별 평균 대기오염도 데이터베이스에는 2022년 동안 매일 기록된 미세먼지정보가 저장되어 있습니다.

우리가 궁금한 건 단순히 미세먼지 농도가 높은 날이 아니라, 이틀 연속 미세먼지 수치가 나빠져서, 세 번째 날에 30㎍/㎥ 이상이 된 날을 찾아내는 것입니다.
 
예를 들어,

• 1월 3일: 28㎍/㎥
• 1월 4일: 37㎍/㎥
• 1월 5일: 52㎍/㎥

이라면,

3→4, 4→5 이틀 연속 미세먼지가 상승했고, 1월 5일 수치가 30 이상이므로 1월 5일이 우리가 찾는 경고 일자(date_alert)가 됩니다.
 

🎯 문제 정리

테이블: measurements

주요 컬럼:

• measured_at : 날짜 (일자)
• pm10 : 해당 날짜의 일 평균 미세먼지 농도(㎍/㎥)

찾고 싶은 값:

• date_alert :
• 이틀 연속 미세먼지 수치가 나빠져 세 번째 날(pm10)이 30㎍/㎥ 이상이 된 날짜

 

🎯 문제 해결 흐름

어떤 날짜를 D라고 할 때,

• D-2일, D-1일, D일 이렇게 3일을 한 세트로 보고,
• 조건은 아래와 같습니다.

1. 연속 3일 동안 미세먼지가 계속 증가
   • pm10(D-2) < pm10(D-1) < pm10(D)
2. 마지막 날(D)의 미세먼지가 30 이상
   • pm10(D) >= 30

이 조건을 만족하는 날 D를 date_alert로 뽑으면 됩니다.

이걸 SQL로 구현하는 가장 자연스러운 방법이 바로 윈도우 함수 LAG를 이용해서 전날·이틀 전 값을 같이 조회하는 것입니다.
 

🎯쿼리

WITH pm AS (
    SELECT
        measured_at,
        pm10,
        LAG(pm10, 1) OVER (ORDER BY measured_at) AS pm_d1,   -- 전날
        LAG(pm10, 2) OVER (ORDER BY measured_at) AS pm_d2    -- 이틀 전
    FROM measurements
)
SELECT
    measured_at AS date_alert
FROM pm
WHERE pm10 >= 30           -- 오늘 수치 30 이상
  AND pm_d2 < pm_d1        -- 이틀 전 < 전날
  AND pm_d1 < pm10         -- 전날 < 오늘 → 3일 연속 상승
ORDER BY date_alert

 

🎯핵심 문법 설명

1. LAG 함수 — 이전 행의 값을 가져오는 윈도 함수

LAG(컬럼명, 몇칸_전) OVER (ORDER BY 정렬기준)

 
예를 들어 LAG(pm10, 1) OVER (ORDER BY measured_at) 라는 뜻은 measured_at 날짜 순서대로 정렬해서, 한 칸 앞(전날)의 pm10 값을 가져오는 것입니다.

 

LAG(pm10, 1) OVER (ORDER BY measured_at) AS pm_d1

LAG(pm10, 2) OVER (ORDER BY measured_at) AS pm_d2

• LAG(column, offset)는 현재 행 기준 이전 행의 값을 가져오는 함수입니다.
• offset = 1이면 전날, offset = 2이면 이틀 전 데이터를 읽어올 수 있습니다.
• OVER (ORDER BY measured_at)
  → 날짜순으로 나열된 상태에서 이전 값을 가져오라는 의미입니다.

이 문제의 핵심은 3일 연속 미세먼지 증가 여부를 판단하는 것입니다. 따라서 이전 날짜의 pm10 값을 같은 행에서 비교하기 위해 LAG 함수가 사용됩니다.