SQL 테이블의 중앙값을 찾는법

테이블의 중앙값 구하기

student_id(학생 ID)	weight(체중 kg)
A100	50
A101	55
A124	55
B343	60
B346	72
B378	72
C563	72
C345	72

1. 집합 지향적 방법

테이블을 상위 집합과 하위 집합으로 분할하고 그 공통부분을 검색하는 방법

SELECT AVG(weight)
    FROM (SELECT W1.weight
               FROM Weights W1, Weights W2
           GROUP BY W1.weight
        HAVING SUM(CASE WHEN W2.weight >= W1.weight THEN 1 ELSE 0 END)
            >= COUNT (*) / 2
        AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE 0 END)
            >= COUNT (*) TMP
       )

• CASE 식에 표현한 두 개의 함수 (포함된다면 1 아니면 0)로 상위 집합과 하위 집합을 분할.
• SQL 스러운 방법
• 단점 1 : 코드가 복잡해서 이해하기 힘들다
• 단점 2 : 성능이 나쁘다. 셀프 조인이라서

개선 - 절차 지향적 방법 1

SELECT AVG(weight) as median
    FROM (SELECT weight, 
                       ROW_NUMBER() OVER (ORDER BY weight ASC, student_id ASC) AS hi,
                    ROW_NUMBER () OVER (ORDER BY weight DESC, student_id DESC) AS lo
        FROM weight) TMP
  WHERE hi IN (lo, lo + 1, lo -1)

• 양쪽 끝에서 레코드 하나씩 세어 중간을 찾는다.

오름차순, 내림차순한 뒤 같은 속도로 움직인다.
그때 lo와 hi가 똑같은 시점이 중앙값이다.
그런데 왜 lo+1, lo-1 도 있을까? -> 짝수일 경우 2개의 평균으로 구해야 하기 때문.
그래서 AVG도 있는 것이다. 홀수와 짝수의 조건 분기를 IN으로 한꺼번에 수행

그러나 정렬 2회, 테이블 스캔 1회로 성능적으로 더 개선할 수 있다.

개선 2. 2빼기 1은 1

SELECT AVG(weight)
    FROM (SELECT weight,
                2 * ROW_NUMBER () OVER(ORDER BY weight)
                - COUNT(*) OVER() AS diff
          FROM Weights) TMP
 WHERE diff BETWEEN 0 AND 2;

순번을 2배 해 COUNT(*)를 빼주면 홀수개일 경우 1이 중앙값, 짝수개일 경우 0과 2가 중앙 값으로 AVG를 구해준다.

이 쿼리의 실행 계획은 스캔 1회, 정렬 1회로 SQL 표준으로 중앙값을 구하는 가장 빠른 쿼리이다.

참조

• SQL 레벨업