MySQL 중앙값 계산하기

중앙값이란?

데이터를 크기 순으로 정렬했을 때 중앙에 있는 값

• 데이터 개수가 홀수일 때는 중앙에 있는 값 1개
• 데이터 개수가 짝수일 때는 중앙에 있는 값 2개의 평균

중앙값이 중요한 이유?

산술평균은 이상치(Outlier)가 존재하면 영향을 많이 받습니다.
하지만 중앙값은 이상치의 유무와 상관없이 표본을 대표할 수 있습니다.

SQLite, MySQL에서는 중앙값을 연산하는 함수가 없어 윈도우 함수를 이용해 직접 계산해야 한다.
PostgreSQL은 중앙값을 계산하는 함수 존재

데이터가 홀수 개일 때 중앙값 찾기

2023년 3월 10일부터 3월 14일까지 5일간 서울의 일별 평균기온 데이터가 있다고 가정하겠습니다.

데이터가 홀수일 경우, 오름차순과 내림차순으로 각각 순위를 매겨, 두 순위가 일치하는 행이 바로 중앙값입니다.

측정일	평균기온(℃)	오름차순 순위	내림차순 순위
2023-03-10	5	1	5
2023-03-11	8	2	4
2023-03-12	12	3	3
2023-03-13	15	4	2
2023-03-14	18	5	1

이 경우 오름차순과 내림차순 모두 순위가 3인 12℃가 중앙값입니다.

SELECT temperature AS median_temperature
FROM (
    SELECT temperature,
           ROW_NUMBER() OVER (ORDER BY temperature, date) AS row_asc,
           ROW_NUMBER() OVER (ORDER BY temperature DESC, date DESC) AS row_desc
    FROM daily_temperatures
    WHERE date BETWEEN '2023-03-10' AND '2023-03-14'
) AS ranks
WHERE row_asc = row_desc;

데이터가 짝수 개일 때 중앙값 찾기

이번에는 2023년 3월 9일부터 3월 14일까지 총 6일간의 데이터를 예로 들겠습니다.

짝수 개일 때는 오름차순 순위가 내림차순 순위와 1씩 차이 나는 두 행의 평균이 중앙값이 됩니다.

측정일	평균기온(℃)	오름차순 순위	내림차순 순위
2023-03-09	3	1	6
2023-03-10	5	2	5
2023-03-11	8	3	4
2023-03-12	12	4	3
2023-03-13	15	5	2
2023-03-14	18	6	1

여기서 오름차순 순위와 내림차순 순위가 서로 1 차이 나는 두 값 8℃와 12℃의 평균(10℃) 이 중앙값입니다.

SELECT AVG(temperature) AS median_temperature
FROM (
    SELECT temperature,
           ROW_NUMBER() OVER (ORDER BY temperature, date) AS row_asc,
           ROW_NUMBER() OVER (ORDER BY temperature DESC, date DESC) AS row_desc
    FROM daily_temperatures
    WHERE date BETWEEN '2023-03-09' AND '2023-03-14'
) AS ranks
WHERE row_asc IN (row_desc - 1, row_desc + 1);

짝수와 홀수를 모두 포함하는 일반적인 방법

짝수와 홀수의 데이터를 구분하지 않고 중앙값을 찾는 일반적인 쿼리문은 다음과 같이 작성할 수 있습니다.

SELECT AVG(temperature) AS median_temperature
FROM (
    SELECT temperature,
           ROW_NUMBER() OVER (ORDER BY temperature, date) AS row_asc,
           ROW_NUMBER() OVER (ORDER BY temperature DESC, date DESC) AS row_desc
    FROM daily_temperatures
    WHERE date BETWEEN '2023-03-09' AND '2023-03-14'
) AS ranks
WHERE row_asc IN (row_desc, row_desc - 1, row_desc + 1);

• 이 방법은 데이터가 홀수 개일 때와 짝수 개일 때 모두 적용 가능한 방법입니다.
• DBMS에 기본 제공되는 중앙값 함수가 있다면 성능과 편의성을 위해 내장 함수를 사용하는 것이 더 좋습니다.