[HackerRank/SQL] Aggregation - Weather Observation Station 20

Problem. Basic Select - Weather Observation Station 20

---

Link.

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

Weather Observation Station 20 | HackerRank

---

Description.

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.

STATION 테이블

---

Key Point. 

 

SET,

 

ROUND,

 

AVG,

 

FLOOR,

 

CEIL,

---

My Answer. 

SET @rowIndex=-1;

SELECT 
    ROUND(AVG(SS.LAT_N), 4)
FROM
(
SELECT 
    @rowIndex:=@rowIndex+1 AS rowNumber,
    LAT_N
FROM STATION
ORDER BY LAT_N
) SS
WHERE SS.rowNumber IN (FLOOR(@rowIndex / 2), CEIL(@rowIndex / 2));

---

References. 

 

앎의 공간: https://techblog-history-younghunjo1.tistory.com/160

[SQL] MySQL로 중앙값(Median) 찾아내기 - (HackerRank - Weather Observation Station 20 문제)