SQL
[HackerRank] Aggregation > Weather Observation Station 20
halloweenie
2022. 12. 30. 23:34
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.
[MySQL Solution 1]
SELECT ROUND(AVG(S.LAT_N), 4)
FROM STATION S
WHERE ABS((SELECT COUNT(*) FROM STATION WHERE LAT_N < S.LAT_N) -
(SELECT COUNT(*) FROM STATION WHERE LAT_N > S.LAT_N)) <= 1;
[MySQL Solution 2]
SET @N := 0;
SELECT COUNT(*) FROM STATION INTO @TOTAL;
SELECT ROUND(AVG(A.LAT_N), 4)
FROM (SELECT @N := @N+1 AS ROW_ID, LAT_N FROM STATION ORDER BY LAT_N) A
WHERE
CASE WHEN MOD(@TOTAL, 2) = 0
THEN A.ROW_ID IN (@TOTAL/2, (@TOTAL/2+1))
ELSE A.ROW_ID = (@TOTAL+1)/2
END;
문제 링크: