[solvesql_Lv3] 미세먼지 수치의 계절간 차이(중앙값구하기)

1. 문제 설명

데이터를 활용해 봄, 여름, 가을, 겨울 계절 별로 미세먼지의 농도에 차이가 있는지 알아보고싶다

 

조건 1)  제시된 시기별로 계절의 명칭을 붙이기

조건 2)  계절별 미세먼지 농도의 중앙값과 평균을 구하기

조건 3)  PM10 농도의 평균값은 소숫점 셋째 자리에서 반올림 해 소수점 둘째자리까지 표시

2. 풀이

WITH base AS (
  SELECT
    pm10,
    CASE
      WHEN DATE(measured_at) BETWEEN '2022-03-01' AND '2022-05-31' THEN 'spring'
      WHEN DATE(measured_at) BETWEEN '2022-06-01' AND '2022-08-31' THEN 'summer'
      WHEN DATE(measured_at) BETWEEN '2022-09-01' AND '2022-11-30' THEN 'autumn'
      ELSE 'winter'
    END AS season
  FROM measurements
),
ranked AS (
  SELECT
    season, pm10,
    ROW_NUMBER() OVER (PARTITION BY season ORDER BY pm10) AS rn,
    COUNT(*)    OVER (PARTITION BY season)                AS cnt
  FROM base
)
SELECT
  season,
  ROUND(AVG(pm10),2) AS pm10_average,
  AVG(CASE WHEN rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2)) THEN pm10 END) AS pm10_median
FROM ranked
GROUP BY season
ORDER BY season;

3. 중앙값 구하기

이 문제는 전체 구조는 단순한데 중앙값을 구하는 로직이 핵심 포인트였습니다.

MySQL에 내장 함수가 없어서 직접 구현해야하기때문이죠!

 

1) BASE 테이블에 계절 컬럼 붙이기 : 분류 라벨링 완료

2) ROW_NUMBER() 함수로 계절별 정렬 순번 부여

3)  COUNT(*)    OVER (PARTITION BY season) : 같은 계정 그룹의 총개수를 모든 행에 붙임.

4) 본격 중앙값 구하기

 AVG(CASE WHEN rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2)) THEN pm10 END)

• rn = 정렬된 순서 번호
• cnt = 그 계절에 속한 전체 데이터 개수

상황	선택되는 rn	이유	평균 결과
홀수 개수	딱 1개 (가운데 값)	(cnt+1)/2의 floor와 ceil이 같아서	그 값 자체가 중앙값
짝수 개수	2개 (가운데 두 값)	floor와 ceil이 서로 달라서	두 값의 평균이 중앙값

예시로 확실하게 이해하기 

+ ) rn IN (...)을 쓴 이유 
-> 짝수 개수일 때 가운데 두 값(rn 두 개) 을 둘 다 포함하기 위해서!

상황	FLOOR((cnt+1)/2)	CEIL((cnt+1)/2)	rn IN (...) 결과
홀수 (cnt=5)	3	3	rn IN (3,3) → 결국 rn=3 하나만 선택
짝수 (cnt=6)	3	4	rn IN (3,4) → rn=3과 rn=4 두 행 선택

즉,

• IN은 다중 값을 비교하는 연산자라
  • 홀수일 때는 하나만 선택
  • 짝수일 때는 두 개를 모두 선택
• AVG()가 그 두 값을 자동으로 평균 내니까 중앙값이 완성

---

4.  집계 함수도 윈도우 함수로 사용 가능

MySQL 8.0 이후부터는 집계 함수 대부분이 윈도우 함수로도 사용 가능합니다.

SUM(), AVG(), COUNT(), MIN(), MAX() 같은 집계 함수를

OVER() 구문과 함께 쓰면 → 윈도우 함수로 변신합니다.

 

즉 👇

• COUNT() → 전체나 파티션 내 행 개수 세기
• SUM() → 누적합
• AVG() → 누적평균
• MIN() / MAX() → 구간별 최솟값·최댓값

예시

SELECT
  department,
  employee,
  salary,
  COUNT(*) OVER (PARTITION BY department) AS cnt_by_dept,
  AVG(salary) OVER (PARTITION BY department) AS avg_by_dept
FROM employees;

 

department	employee	salary	cnt_by_dept	avg_by_dept
HR	Alice	4000	2	4500
HR	Bob	5000	2	4500
IT	Carol	7000	3	7333
IT	Dan	8000	3	7333
IT	Emma	7000	3	7333

➡️ 여기서 COUNT()는 GROUP BY처럼 한 줄로 요약하지 않고,
각 행마다 “해당 그룹 안의 개수”를 표시합니다. 이게 바로 윈도우 함수의 장점이죠.