[SQL] MEDIAN() 없이 중앙값 구하기

중앙값의 경우 DBMS에 내장되어 있는 함수가 있다면 쉽게 바로 구할 수 있다. 그러나 중앙값을 구하는 내장 함수가 없는 경우, 직접 계산하는 쿼리를 작성해야 한다. (BigQuery, Oracle 은 중앙값을 구하는 내장 함수 있음)
 
 

중앙값이란?

• 자료를 크기순으로 나열했을 때 제일 가운데에 위치하는 값
• 아래 또는 위로 세어봐도 같은 중앙 위치
• 중앙값 = 중간값 = 중위수 = 50 백분위수 = 2 사분위수
 
 

중앙값 구하는 쿼리 작성하기

STEP 1️⃣ 정렬하여 순서매기는 컬럼 & 전체 행 개수도 표시하는 컬럼 만들기

SELECT math,
       ROW_NUMBER() OVER (ORDER BY math) AS rn,
       COUNT(*) OVER () AS total_rows
FROM a_class;

 

 예시 
(1) 데이터 개수가 홀수인 경우

math	rn	total_rows
10	1	5
20	2	5
30	3	5
40	4	5
50	5	5

 
 
(2) 데이터 개수가 짝수인 경우

math	rn	total_rows
10	1	6
20	2	6
30	3	6
40	4	6
50	5	6
60	6	6

 
 
 

STEP 2️⃣ 순서에서 중앙에 위치한 값 찾은 후, 평균 계산해주기

WITH tmp AS (
    SELECT math,
           ROW_NUMBER() OVER (ORDER BY math) AS rn,
           COUNT(*) OVER () AS total_rows
    FROM a_class
)
SELECT AVG(math) AS median
FROM tmp
WHERE rn IN (FLOOR((total_rows + 1) / 2), CEIL((total_rows + 1) / 2));

 

 예시 
(1) 데이터 개수가 홀수인 경우
=> 가운데에 위치한 순서 번호를 찾아야함(예시: 3번)
 
총 행 갯수에 1을 더한 후 2로 나눠주면 딱 가운데 위치한 순서 번호가 나온다. (예시: (5 + 1) / 2 => 3)

데이터의 갯수가 홀수인 경우, 위와 같이 계산하면 순서 번호가 정수로 딱 떨어지게 나오기 때문에 FLOOR(), CEIL() 을 씌워도 값이 그대로다. 가운데 순서 번호에 해당하는 값이 중앙값이다.
 

 
 
(2) 데이터 개수가 짝수인 경우
=> 가운데에 위치한 순서 번호를 찾아야함(예시: 3.5번은 없으니, 3번과 4번을 찾아야 함)
 
총 행 갯수에 1을 더한 후 2로 나눠주면 딱 가운데 위치한 순서 번호가 나온다. (예시: (6 + 1) / 2 => 3.5)

단 데이터의 갯수가 짝수인 경우, 위와 같이 계산하면 순서 번호가 정수가 아니다. 이 순서 번호에 FLOOR(), CEIL() 을 씌우면 소수점 이하를 버림한 정수와 올림한 정수 즉 2개의 값을 구할 수 있다. 두 순서 번호가 가리키는 값의 평균이 중앙값이 된다. 
 

 
 
 
[복습 노트]
https://limhm4907.tistory.com/109

기초통계 / 대표값 (평균, 중앙값, 절사평균, 최빈값)

 
https://limhm4907.tistory.com/259

DAY 8 / [solvesql] Advent of SQL 2024 2문제 (전국 카페 주소 데이터 정제하기, 미세먼지 수치의 계절간 차