[HackerRank] MySQL에서 median 중앙값 구하기 (Weather Observation Station 20)

https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true 

Weather Observation Station 20 | HackerRank

 

 

SELECT ROUND(LAT_N, 4)
FROM (
    SELECT LAT_N, RANK() OVER (ORDER BY LAT_N) AS RANKING
    FROM STATION
    ) A
WHERE RANKING = (
    SELECT FLOOR(COUNT(LAT_N)/2) + 1
    FROM STATION
    )

 

 

 

 

 

 

 

 

 

방법2) idx 열 사용

set @idx = -1;

select round(avg(LAT_N), 4)
from (select @idx := @idx + 1 as ROW_NUM, LAT_N
	from STATION
    order by LAT_N) as Sub
where Sub.ROW_NUM in (floor(@idx / 2), ceil(@idx / 2));

1. set @idx 변수 입력, -1로 저장

2. @idx는 행마다 +1씩 더해져 값이 출력 됨.

  0, 1 , 2, ...

3. @idx는 행의 가장 마지막 값이 출력됨

   ex. n행 있으면 @idx는 n-1 출력됨

 

 

 

 

방법3) percent_rank() 사용

select round(LAT_N, 4)
from (select LAT_N, percent_rank() over (order by LAT_N) as PERCENT
	from STATION) as Sub
where Sub.percent = 0.5