[PostgreSQL] solvesql LV.3 미세먼지 수치의 계절간 차이 (with 중앙값 구하는 방법)

https://solvesql.com/problems/finedust-seasonal-summary/

https://solvesql.com/problems/finedust-seasonal-summary/

🔍 문제

대기 오염 측정 데이터(measurements 테이블)를 활용하여

계절별 미세먼지(PM10) 농도 변화를 분석하고자 합니다.

 

measured_at 컬럼에는 측정 일자가, pm10 컬럼에는 미세먼지 농도가 저장되어 있습니다.

각 측정값을 계절 기준으로 구분한 뒤,

각 계절의 중앙값(median)과 평균(average)을 구하는 것이 목표입니다.

---

🎯정답

select
  CASE
    WHEN to_char(measured_at, 'MM-DD') between '03-01' and '05-31' THEN 'spring'
    WHEN to_char(measured_at, 'MM-DD') between '06-01' and '08-31' THEN 'summer'
    WHEN to_char(measured_at, 'MM-DD') between '09-01' and '11-30' THEN 'autumn'
    else 'winter'
  END as season,
percentile_disc(0.5) within group (order by pm10) as pm10_median,
round(avg(pm10), 2) as pm10_average
from measurements
group by 1;

❓풀이

• case ~ when 구문을 사용해서 select절에 봄/여름/가을/겨울을 표현해줬다.
• ✳️ 핵심 : 중앙값을 구하는 방법
  • postgresql 에서 중앙값 구하기 : percentile_disc를 이용하여 중앙값을 구할 수 있다.

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY 값컬럼) AS median
FROM 테이블명;

• 데이터 개수가 홀수이면 `percentile_disc`을 이용해서 정확한 중앙값을 찾을 수 있다.
• 만약 데이터 수가 짝수이면 `percentile_cont`을 이용해야 한다.
  • `percentile_cont`는 데이터 수가 짝수일 때 중앙에 있는 값을 데이터 내에서 가져올 수 없으므로 "선형보간"을 이용해서 중앙값을 구한다.