[TIL] Window함수로 Median 구하기 # MySQL # SQL

HackerRank Intermediate - Weather Observation Station 20
👉 문제 바로가기(HackerRank)

-- Query the median of the Northern Latitudes (LAT_N) from STATION
-- Round result to 4 decimal places

SELECT
  ROUND(t.LAT_N, 4)
FROM 
  (SELECT LAT_N, ROW_NUMBER() OVER(ORDER BY LAT_N) as rnk 
   FROM STATION) t
WHERE 
  t.rnk = ((SELECT ROW_NUMBER() OVER(ORDER BY LAT_N DESC) as rnk 
            FROM STATION 
            ORDER BY rnk DESC LIMIT 1)+1)/2

---

[ 논리 과정 ]

1. 행을 LAT_N으로 정렬한 후 인덱싱한다.
2. 인덱스의 중앙값에 해당하는 LAT_N을 출력
3. 출력할 때 반올림하여 원하는 형식으로 출력

[ 문제 상황 ]

• 인덱스 넘버 컬럼을 추가하는 방법을 알지 못한다.

[ 해결 ]

• RANK 관련된 윈도우 함수를 통해 인덱싱한다.
• 중복되는 순위 없이 모두 구별되어야하므로 ROW_NUMBER()를 사용한다.

[ 회고 ]

• MEDIAN에 해당하는 행을 판별하는 조건을 걸 때(본 쿼리의 WHERE절), SELECT COUNT(*) FROM STATION 쿼리문으로 총 행 수를 확인한 후 (총 행수 + 1)/2 로 쿼리를 작성하여 해당 테이블의 중앙값을 조회할 수 있었다.

• 하지만 다른 테이블의 MEDIAN 값을 호출할 때 쿼리 호환성이 좋지 않으므로(똑같은 쿼리로 다른 테이블의 중앙값을 바로 조회할 수 없음, 총 행 수가 짝수인지 홀수인지 모르므로), ROW_NUMBER() 원도우 함수 대신 PERCENT_RANK()함수를 사용하고 조건절에 rnk = 0.5 로 작성하면 다른 테이블과 호환되도록 쿼리를 날릴 수 있다.