SQL 중앙값 구하기

예시 : Hackerank : weather observation station 20
https://www.hackerrank.com/challenges/weather-observation-station-20/problem

중복을 피해야 하므로 rank가 아닌 row_number 를 쓰는 것 유념

select round(
    avg(
        case when (select count(*)+1 from station) % 2 = 1 then		-- n+1이 핵심 (데이터 수가 짝수면,)
                    case when abs(rnk - (select count(*)+1 from station)/2)=1/2 then lat_n else null end	--중간 양쪽
             else case when rnk = (select count(*)+1 from station)/2 then lat_n else null end	-- 홀수면 중간 (n+1)/2
        end
    ),4)
from (
    select lat_n, row_number() over(order by lat_n asc) as rnk from station
    ) T

«SQL 레벨업, 미크, 윤인성 역, 2016, 한빛미디어»

큰 순서대로(lo), 작은순서대로(hi) 출발해서
데이터 수가 홀수이면 중앙값에서 만나고, 짝수이면 hi = lo +- 1 일 때 만남

select avg(lat_n)
from (select lat_n, 
      		row_number() over (order by lat_n asc) as hi
      		,row_number() over (order by lat_n desc) as lo
      from station ) T
where hi in (lo, lo+1, lo-1)

row_number()에서의 order by 정렬 한번만 사용하므로(count에 order by가 없음) 성능이 더 좋음

$ 0 \leq 2*r - N \leq 2 \Leftrightarrow \frac{2}{N} \leq r \leq 1+ \frac{2}{N} $

select avg(lat_n)
from (select lat_n, 2 * row_number() over(order by lat_n asc) - count(*) over() as diff
      from station ) T
where diff between 0 and 2