[SQL] LAG, LEAD 윈도우 함수 / HAVING 절은 GROUP BY와 사용 가능 / WINDOW 함수는 서브 쿼리 안에서

문제

문제 링크 : https://solvesql.com/problems/bad-finedust-measure/

 

서울숲 일별 평균 대기오염도 데이터셋은 2022년 서울숲 대기오염도 측정소에서 매일 기록한 대기오염 정보를 담고 있습니다.

measurements 테이블의 pm10 컬럼에는 다양한 대기오염도 측정 기준 중에서도 미세먼지(PM10) 농도가 기록되어 있습니다. 이 데이터를 이용하여 당일의 미세먼지 농도보다 바로 다음날의 미세먼지 농도가 더 안좋은 날을 찾아주세요. 결과는 아래 컬럼들을 포함해야 합니다.

• today: 당일 (YYYY-MM-DD)
• next_day: 다음날 (YYYY-MM-DD)
• pm10: 당일의 미세먼지 농도
• next_pm10: 다음날의 미세먼지 농도

 

해답

핵심 개념 ) 바로 전 행과 다음 행 출력하기 (LAG, LEAD)

데이터의 바로 전 행과 바로 다음 행을 출력하기 위해서 LAG와 LEAD함수를 사용합니다. 

 

LAG 함수는 바로 전 행의 데이터를 출력하는 함수입니다.

숫자 1을 사용하면 바로 전 행, 숫자 2를 사용하면 전 전 행이 출력됩니다.

 

LEAD 함수는 바로 다음 행의 데이터를 출력하는 함수입니다.

숫자 1을 사용하면 바로 다음 행, 숫자 2를 사용하면 다음 다음 행이 출력됩니다.

LEAD (1 간격) 으로 파생 컬럼 생성한 결과

 

오류 1) HAVING 절을 비집계 쿼리에 사용하면 SQL 오류가 발생

HAVING 절은 일반적으로 집계 함수와 함께 사용되며, GROUP BY 절로 그룹화된 결과를 필터링하는 데 사용됩니다. HAVING 절을 비집계 쿼리에 사용하면 SQL 오류가 발생합니다.

HAVING 절을 사용하지 않고 동일한 결과를 얻으려면 WHERE 절을 사용해야 합니다.

 

오류 코드 1) 

SELECT measured_at AS today
      ,LEAD(measured_at, 1) OVER(ORDER BY measured_at ASC) AS next_day
      ,pm10
      ,LEAD(pm10, 1) OVER(ORDER BY measured_at ASC) AS next_pm10
  FROM measurements
HAVING pm10 < next_pm10;

 

 

오류 2) LEAD 함수와 같은 윈도우 함수를 WHERE 절에 직접 사용할 수 없음

WHERE 절에서는 윈도우 함수를 사용할 수 없습니다.

윈도우 함수는 논리적 실행 순서에서 SELECT 절 이후에 실행되기 때문입니다.

WHERE 절은 SELECT 절 이전에 적용되므로, WHERE 절에서는 윈도우 함수의 결과를 사용할 수 없습니다.

 

오류 코드 2)

SELECT measured_at AS today
      ,LEAD(measured_at, 1) OVER(ORDER BY measured_at ASC) AS next_day
      ,pm10
      ,LEAD(pm10, 1) OVER(ORDER BY measured_at ASC) AS next_pm10
  FROM measurements
 WHERE pm10 < LEAD(pm10, 1) OVER(ORDER BY measured_at ASC);

 

정답 쿼리

SELECT *
FROM (SELECT measured_at AS today
            ,LEAD(measured_at, 1) OVER(ORDER BY measured_at ASC) AS next_day
            ,pm10
            ,LEAD(pm10, 1) OVER(ORDER BY measured_at ASC) AS next_pm10
      FROM measurements) A 
 WHERE pm10 < next_pm10;