[Hacker Rank] Weather Observation Station 20 : MySQL

📢 본 포스팅에 활용되는 기본 문제 및 자료 출처는 HackerRank 임을 밝힙니다.

https://www.hackerrank.com

---

■ 문제

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

Weather Observation Station 20 | HackerRank

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to  decimal places.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

---

 

■ 풀이

SELECT ROUND(LAT_N, 4)
 FROM (SELECT LAT_N, PERCENT_RANK() OVER (ORDER BY LAT_N) AS PR
       FROM STATION) T1
 WHERE T1.PR = 0.5;

 

PERCENT_RANK() 

: 파티션 값보다 작은 파티션 값의 백분율을 반환한다.

 

문제에서는 중앙값을 추출하라고 했으므로 조건절에 PERCENT_RANK()= 0.5 .

 

 

 

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_percent-rank

MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions