요즘 solve sql로 2025년 어드벤트를 풀고 있다..!
문제
서울숲 일별 평균 대기오염도 데이터베이스는 2022년 서울숲 대기 측정소에서 매일 기록한 다양한 대기오염 정보를 담고 있습니다.
미세먼지 수치가 지속적으로 나빠지는 경우를 추출해 해당 일자 부근의 서울숲 대기를 상세하게 분석해보려고 합니다. 이를 위해 2022년 중 이틀 연속 미세먼지 수치가 나빠져 30㎍/㎥ 이상이 된 날을 추출하는 쿼리를 작성해주세요. 예를 들어, 1월 3일의 미세먼지 수치가 28㎍/㎥ 이고, 1월 4일의 미세먼지 수치가 37㎍/㎥, 1월 5일의 미세먼지 수치가 52㎍/㎥ 이라면 1월 5일이 이틀 연속 미세먼지 수치가 나빠져 30㎍/㎥ 이상이 된 날 입니다.
쿼리 결과에는 아래 컬럼이 있어야 하고, 날짜 기준 오름차순 정렬되어 있어야 합니다.
date_alert: 이틀 연속 미세먼지 수치가 나빠져 30㎍/㎥ 이상이 된 날
조건이 너무 복잡해서 어떻게 해야 하나 멍해졌다..
그러다 생각난 sql window function 중 하나인 LEAD와 LAG함수
유레카..!
LAG함수를 써서 이전 값을 가져오는 컬럼을 새로 생성하고 그걸 토대로 조건문을 만들어보자!
LAG: 해당 컬럼 이전 행을 가져온다
LAG(접근할 컬럼, 접근할 이전 행 N, NULL 대체값) OVER (ORDER BY 정렬컬럼)
LEAD: 해당 컬럼 이후 행을 가져온다
LEAD(접근할 컬럼, 접근할 이후 행 N, NULL 대체값) OVER (ORDER BY 정렬컬럼)
- N과 NULL 대체값은 생략 가능합니다. (N 디폴트값은 1)
- 함수 결과값이 없을 경우 NULL을 반환합니다. (NULL 대체값이 있을 경우 대체값 반환)
SELECT *,
LAG(pm10) OVER (order by measured_at) LAG_1day,
LAG(pm10, 2) OVER (order by measured_at) LAG_2day
FROM measurements
WHERE pm10 >= 30
AND pm10 > LAG(pm10) OVER (order by measured_at)
AND LAG(pm10) OVER (order by measured_at) > LAG(pm10, 2) OVER (order by measured_at)이렇게 쓰면 WHERE절의 LAG에서 에러가 나길래 서브쿼리로 묶어서 일단 컬럼을 만들고 조건문을 걸었다
SELECT measured_at AS date_alert
FROM
(
SELECT *,
LAG(pm10) OVER (order by measured_at) LAG_1day,
LAG(pm10, 2) OVER (order by measured_at) LAG_2day
FROM measurements
) a
WHERE pm10 >= 30
AND pm10 > LAG_1day
AND LAG_1day > LAG_2day
ORDER BY measured_at
결과는 성공..! 헤헤
