[SQL] Weather Observation Station 20

✅ Weather Observation Station 20

---

📝 문제

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.
Input Format

The STATION table is described as follows:

---

💻 풀이

❗ 주의
아래 코드는 홀수인 경우에만 오류 없이 출력됨

SELECT ROUND(AVG(LAT_N), 4)
FROM
(SELECT LAT_N,
 PERCENT_RANK() OVER(ORDER BY LAT_N) AS result FROM STATION) AS sub
WHERE result = 0.5;

💻 정답

SET @indexnum = -1;

SELECT ROUND(AVG(LAT_N), 4) AS Median
FROM(
    SELECT
        @indexnum := @indexnum + 1 AS row_num,
        LAT_N
    FROM STATION
    ORDER BY LAT_N ASC) AS sub
WHERE row_num IN (FLOOR(@indexnum / 2), CEIL(@indexnum / 2));

---

📌 정리

RANK() OVER(ORDER BY weight) AS result

ex) 90, 90, 85일 경우 1등, 1등, 3등으로 나타남 (공동 등수만큼 건너 뜀)

DENSE_RANK() OVER(ORDER BY weight) AS result

ex) 90, 90, 85일 경우 1등, 1등, 2등으로 나타남