[SQL] 📌 중앙값 구하기 | median 함수 없이 구하는 방법 | 활용 예제

중앙값 구하기

 

중앙값(median)은 데이터를 크기 순서대로 나열했을 때, 중앙에 위치한 값을 말합니다.

데이터를 대표하는 값 중 하나로, 특히 극단적인 이상치에 영향을 덜 받는 특징이 있어 평균보다 더 안정적인 지표로 사용되기도 합니다.

 

중앙값 구하는 방법:

• 홀수 개의 데이터가 있을 경우:
  → 가운데 있는 값이 중앙값입니다.
  예: 3, 5, 7 → 중앙값은 5
• 짝수 개의 데이터가 있을 경우:
  → 가운데 두 값의 평균이 중앙값입니다.
  예: 2, 4, 6, 8 → 중앙값은 (4+6)/2 = 5

이렇게 데이터의 개수가 홀/짝인지에 따라 중앙값의 위치와 구하는 방법이 달라지기 때문에

이를 반영하여 SQL 쿼리를 짜야 합니다.

MySQL은 median이라는 함수를 지원하지 않기 때문에 약간의 아이디어가 필요합니다.

 

활용 예제

 

tips라는 테이블을 활용하여 

각 요일별 성별 식사금액의 중앙값을 구해보겠습니다.

속성	설명
day	요일
sex	성별
total_bill	식사 금액

 

 

방법 1

 

첫번째 방법은, 중앙값의 순서 * 2 - 전체 레코드 수를 구해주는 것입니다.

 

아래 예제를 통해 알아보겠습니다.

 

row_num : 데이터를 순서대로 나열해서 번호를 매겨준 값입니다.

tot_cnt : 전체 레코드 수입니다.

 

▪️레코드가 홀수개

전체 레코드 수가 9개이면, 중앙값은 5번째 순서에 있는 레코드 값일 것입니다.

따라서 위의 중앙값의 순서 * 2 - 전체 레코드 수 공식을 활용하면

(5 * 2) - 9 = 1입니다.

즉, 중앙값의 순서 * 2 - 전체 레코드 수가 1인 경우를 구해주면 중앙값을 구할 수 있습니다.

 

▪️레코드가 짝수개

전체 레코드 수가 10개이면, 중앙값은 5번째, 6번째 레코드의 평균일 것입니다.

위의 공식에 적용해보면

(5 * 2) - 10 = 0 | (6 * 2) - 10 = 2입니다.

즉, 중앙값의 순서 * 2 - 전체 레코드 수가 0과 2인 레코드의 평균을 구해주면 중앙값을 구할 수 있습니다.

 

 

방법 1 : SQL 코드

with rownum_table as
(select
  day,
  sex,
  total_bill,
  row_number() over (partition by day, sex order by total_bill asc) as row_num, -- day, sex별로 레코드 순서 매겨주기
  count(*) over (partition by day, sex) as tot_cnt -- day, sex별로 전체 레코드 수 구하기
from tips)

select
  day,
  sex,
  avg(total_bill) as median_total_bill -- 짝수인 경우를 고려해서 금액의 평균을 구해줌
from rownum_table
where row_num * 2 - tot_cnt between 0 and 2 -- 홀수 : 1, 짝수 : 0,2 을 모두 만족
group by 1, 2

 

▪️ROW_NUMBER()

ROW_NUMBER() 함수는 정렬 순서에 따라 1부터 시작해 순차적으로 매겨주는 윈도우 함수(Window Function)입니다.

RANK(), DENSE_RANK()와 달리 같은 값이 여러 개 있어도 순번은 중복 없이 번호가 매겨집니다.

 

결과 테이블을 보면 요일, 성별별로 식사 금액의 중앙값이 잘 구해진 것을 확인할 수 있습니다.

 

 

방법 2

 

두번째 방법은, 중앙값의 순서와 (전체 레코드 수 + 1 / 2)를 비교해주는 것입니다.

 

 

▪️레코드가 홀수개

(전체 레코드 수 + 1 / 2)을 구해보면 (9 + 1) / 2 = 5입니다.

즉, 중앙값은 레코드의 순서와 (전체 레코드 수 + 1 / 2)가 일치하는 경우일 것입니다.

 

▪️레코드가 짝수개

(전체 레코드 수 + 1 / 2)을 구해보면 (10 + 1) / 2 = 5.5입니다.

중앙값은 (전체 레코드 수 + 1) / 2의 ⌊내림값⌋과 ⌈올림값⌉ 위치에 있는 값들의 평균입니다.

 

 

방법 2 : SQL 코드

 

with rownum_table as
(select
  day,
  sex,
  total_bill,
  row_number() over (partition by day, sex order by total_bill asc) as row_num, -- 레코드 순서 매기기
  count(*) over (partition by day, sex) as tot_cnt -- 전체 레코드 수
from tips)

select
  day,
  sex,
  avg(total_bill) as median_total_bill -- 평균 구하기
from rownum_table
where row_num between floor((tot_cnt + 1) / 2.0) and ceil((tot_cnt + 1) / 2.0) -- 내림한 값과 올림한 값 사이에 있는 값
group by 1, 2

 

▪️FLOOR & CEIL

floor : 내림 함수

ceil (또는 ceiling) : 올림 함수

 

결과는 동일하게 잘 나왔습니다!

 

 

번외 ) wide 형태의 피벗테이블 구하기

 

이렇게 long한 형태의 테이블을 wide 형태의 피벗 테이블로 변형하여 가독성을 높여보겠습니다.

 

-- 위의 long한 형태의 중앙값 테이블을 cte long_table로 정의하였습니다.

SELECT
  sex,
  max(case when day = 'Thur' then median end) as 'Thursday',
  max(case when day = 'Fri' then median end) as 'Friday',
  max(case when day = 'Sat' then median end) as 'Saturday',
  max(case when day = 'Sun' then median end) as 'Sunday'
from long_table
group by sex

 

 

결과가 wide한 형태의 테이블로 잘 출력이 된 것을 확인할 수 있습니다.

 

정리

 

오늘은 중앙값을 구하는 함수가 없을 때

직접 구하는 방법을 알아보았습니다.

추가로 롱 형태의 테이블을 와이드 형태로 피벗하는 방법도 알아보았습니다.

 

막상 직접 구현하려면 헷갈릴 수 있는 내용들이라, 정리해두면 앞으로 분석 작업에 큰 도움이 될 것 같습니다!