Weather Observation Station 20 / HackerRank, SQL, MySQL

문제

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 4 decimal places.

제출답안(MySQL)

SELECT ROUND(lat_n, 4)
FROM (SELECT lat_n, PERCENT_RANK() OVER (ORDER BY lat_n) AS pct FROM station) tb_pct
WHERE pct = 0.5

 

 

 

풀이(MySQL)

오라클과 달리 MySQL은 MEDIAN 함수를 지원하지 않음

>> 따라서 직접 구해야함

Median == 50th Percentile을 의미함

>> Percent Rank 활용

 

 

---

 

제출답안(MySQL) - 행 번호 이용해서 풀기

SET @cnt_lat := (SELECT COUNT(lat_n) FROM station);
SET @loc_median := CEIL(@cnt_lat/2);
-- In case that the number of rows is even, the median is (x[loc_median-1] + x[loc_median])/2
                        
SELECT 
    CASE WHEN @cnt_lat % 2 = 0 THEN ROUND(AVG(lat_n), 4)
    ELSE ROUND(MAX(lat_n), 4) END
FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY lat_n) AS rn, lat_n 
     FROM station) tb_rm
WHERE rn = @loc_median-1 OR rn = @loc_median

 

풀이(MySQL)

아래 코드를 이용해서 lat_n 오름차순 별로 row_number를 붙일 수 있음

(SELECT ROW_NUMBER() OVER (ORDER BY lat_n) AS rn, lat_n 
FROM station) tb_rm

>> 해당 서브쿼리에서 데이터를 추출할 예정

 

median의 row 번호를 사용자 변수로 지정

SET @cnt_lat := (SELECT COUNT(lat_n) FROM station);
SET @loc_median := CEIL(@cnt_lat/2);
-- In case that the number of rows is even, the median is (x[loc_median-1] + x[loc_median])/2

>> @loc_median은 데이터의 수가 홀수일 때 바로 적용할 수 있다

>> 데이터의 수가 짝수일 때 median 값은 열번호가 @loc_median -1, @loc_median 에 해당하는 값의 평균

(아래 median 공식 참고)

 

Source: https://en.wikipedia.org/wiki/Median

Row 넘버가 @loc_median-1, @loc_median인 값을 불러옴

FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY lat_n) AS rn, lat_n 
     FROM station) tb_rm
WHERE rn = @loc_median-1 OR rn = @loc_median

>> 2개의 데이터가 조회됨

데이터셋의 수가 짝수일 경우 AVG, 홀수일 경우 MAX를 적용

SELECT 
    CASE WHEN @cnt_lat % 2 = 0 THEN ROUND(AVG(lat_n), 4)
    ELSE ROUND(MAX(lat_n), 4) END

문제 바로가기(MySQL)