중앙값 계산하기 [SQL 공통]
중앙값이란 데이터를 크기 순으로 정렬했을 때 중앙에 있는 값입니다. 데이터 갯수가 홀수일 때에는 중앙에 있는 값 1개가, 데이터 갯수가 짝수개일 때에는 중앙에 있는 값 2개의 평균이 중앙값입니다.
Untitled
Untitled
중앙값은 산술평균의 문제점을 보완하기 위해서 꼭 봐야하는 대표값 중 하나입니다. 산술평균은 이상치(Outlier)의 유무에 따라 값이 요동치지만, 중앙값은 이상치의 유무와 상관 없이 표본을 대표할 수 있는 값을 잘 나타내기 때문입니다.
산술평균과 중앙값의 차이
중앙값은 실무에서도 자주 사용되는데, SQLite, MySQL에는 중앙값을 연산하는 함수가 없어 직접 계산을 해주어야 합니다. 이 방법은 PostgreSQL 등 윈도우 함수를 지원하는 다른 DBMS에서도 공통적으로 사용할 수 있습니다. 
하지만, PostgreSQL은 중앙값을 계산하는 빌트인 함수를 이용하는 것이 성능 면에서 좋습니다. PostgreSQL에서 중앙값을 빠르게 계산하는 방법은 다음 글에서 얘기해보겠습니다.
데이터가 홀수개 일 때
2022년 2월 16일부터 20일까지 5일간 미세먼지(pm10) 농도의 중앙값을 찾으려면 약간의 아이디어가 필요합니다. pm10을 기준으로 오름차순 순위, 내림차순 순위를 계산하고 그 순위가 동일한 행이 중앙값이 됩니다. 아래 예시에서는 오름차순 순위와 내림차순 순위가 동시에 3위인 행이 중앙에 위치한 행입니다.
SELECT pm10 AS median
FROM (
  SELECT pm10
       , ROW_NUMBER() OVER (ORDER BY pm10, measured_at) AS row_asc -- 오름차순 순위
       , ROW_NUMBER() OVER (ORDER BY pm10 DESC, measured_at DESC) AS row_desc -- 내림차순 순위
  FROM measurements
  WHERE measured_at BETWEEN '2022-02-16' AND '2022-02-20'
) t
WHERE row_asc = row_desc -- 오름차순 순위와 내림차순 순위가 일치하는 행
solvesql의 플레이그라운드에서 SQLite 코드를 실행해 볼 수 있습니다. 데이터베이스로 ‘서울숲 일별 평균 대기오염도’를 선택하고 코드를 실행해 보세요. 데이터리안의 SQL 캠프 입문반과 실전반 수강생은 MySQL을, SQL 캠프 마스터반 수강생은 PostgreSQL을 추가로 사용할 수 있습니다.
데이터가 짝수개 일때
2022년 2월 15일부터 20일까지 6일간 pm10의 중앙값은 어떻게 찾을 수 있을까요? 
위 사례와 비슷하게 pm10을 기준으로 오름차순 순위, 내림차순 순위를 계산합니다. 그리고 오름차순 순위가 내림차순 순위의 +1, -1이 되는 행을 찾습니다. 이에 해당하는 pm10 값을 평균하면 중앙값입니다. 아래 예시에서는 21과 24를 평균한 22.5가 중앙값이 됩니다.
SELECT AVG(pm10) AS median
FROM (
  SELECT pm10
       , ROW_NUMBER() OVER (ORDER BY pm10, measured_at) AS row_asc
       , ROW_NUMBER() OVER (ORDER BY pm10 DESC, measured_at DESC) AS row_desc
  FROM measurements
  WHERE measured_at BETWEEN '2022-02-15' AND '2022-02-20'
) t
WHERE row_asc IN (row_desc - 1, row_desc + 1)
종합하여 중앙값 계산하기
종합하여 데이터가 홀수개일때, 짝수개일때 공통적으로 사용할 수 있는 SQL 문은 다음과 같이 쓸 수 있습니다.
SELECT AVG(pm10) AS median
FROM (
  SELECT pm10
       , ROW_NUMBER() OVER (ORDER BY pm10, measured_at) AS row_asc
       , ROW_NUMBER() OVER (ORDER BY pm10 DESC, measured_at DESC) AS row_desc
  FROM measurements
  WHERE measured_at BETWEEN '2022-02-15' AND '2022-02-20'
) t
WHERE row_asc IN (row_desc, row_desc - 1, row_desc + 1)
만약 DBMS가 중앙값을 계산하는 빌트인 함수를 제공한다면, 이 방법보다는 빌트인 함수를 사용하는 것이 더 효율적입니다. 코드를 적게 쓸 수 있을 뿐만 아니라, 성능적으로도 더 낫습니다. 다음 글에서는 PostgreSQL에서 빌트인 함수를 이용해 중앙값을 계산하는 방법에 대해 알아보겠습니다.
연습 문제
미세먼지 수치의 계절간 차이 문제를 풀고 배운 내용을 복습해보세요! Advent of SQL에서 더 많은 SQL 문제를 풀어볼 수 있습니다.


conversion_type

시리즈 내 순서

conversion_text

멤버 전용

대표 이미지

공개 가능

작성일

예상 읽기 시간

전환 텍스트

글쓴이

시리즈

상단 노출

전환 유형

요약

제목

DBMS마다 천차만별인 중앙값 계산하기, 한 번에 정리해서 배워봅시다

중앙값 계산하기 [SQL 공통]

중앙값 계산하기 [SQL 공통]

Contents

함께 읽어보면 좋은 글

알아두면 쓸데있는 데이터 모델링 (3) ER 모델의 구성 요소 - 엔티티, 속성, 관계

[선배 데이터 분석가의 SQL 코딩테스트 노하우 공개] 세미나 다시보기

인덱스 타입: B-Tree, Hash

데이터 분석 소식을
한 달에 한 번 받아보세요

중앙값 계산하기 [SQL 공통]

Contents

함께 읽어보면 좋은 글

알아두면 쓸데있는 데이터 모델링 (3) ER 모델의 구성 요소 - 엔티티, 속성, 관계

[선배 데이터 분석가의 SQL 코딩테스트 노하우 공개] 세미나 다시보기

인덱스 타입: B-Tree, Hash

데이터 분석 소식을 한 달에 한 번 받아보세요

데이터 분석 소식을
한 달에 한 번 받아보세요