[해커랭크] Weather Observation Station 20 - 사용자 정의 변수 선언, 중앙값(Median) 구하는 방법

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

Weather Observation Station 20 | HackerRank

중앙값은 숫자의 배열을 오름차순 혹은 내림차순으로 배열하여 가운데에 위치한 값을 의미한다. 숫자의 개수가 홀수일 때에는 정중앙에 해당하는 값을 중앙값으로 하고, 숫자의 개수가 짝수일 때에는 정중앙에 해당하는 2개의 값의 평균을 중앙값으로 한다.

MySQL에서 중앙값을 구하는 함수가 존재하지 않는다. 때문에 행에 인덱스를 직접 부여하여, 이를 기준으로 중앙값을 직접 구하여야 한다.
이를 위해서는 SET을 이용하여 먼저 인덱스를 생성하여 각 행에 인덱스를 부여한 후, 행의 개수가 홀수, 짝수인 경우를 모두 고려하여 중앙값을 구하는 쿼리를 만들어야 한다.

SET @변수명 = 대입값; 또는 SET @변수명 := 대입값;
SELECT @변수명 := 대입값;

 

SET 이외의 명령문에서 = 는 비교연산자로 취급되므로 SELECT로 값을 대입할 때에는 := 를 사용하여야 한다.

 

SET @ROWINDEX = -1;

SELECT @ROWINDEX := @ROWINDEX +1 AS ROWINDEX
      , LAT_N
FROM STATION
ORDER BY LAT_N

 

행의 인덱스를 ROWINDEX라는 변수로 선언하고 -1을 대입한다. 그리고 SELECT 절에 @ROWINDEX := @ROWINDEX +1 을 통해 0,1,2,3,4,... 의 ROWINDEX 컬럼을 생성해주었다.

 

SET @ROWINDEX = -1;

SELECT ROUND(AVG(LAT_N),4) AS MEDIAN
FROM(SELECT @ROWINDEX := @ROWINDEX +1 AS ROWINDEX
          , LAT_N
     FROM STATION
     ORDER BY LAT_N) AS SUB
WHERE ROWINDEX IN (CEIL(@ROWINDEX/2),FLOOR(@ROWINDEX/2))

 

그리고, 위에 작성한 쿼리를 서브쿼리로 하여 WHERE 절에 CEIL, FLOOR 함수를 이용하여 ROWINDEX의 중앙값 혹은 정중앙에 해당하는 2개의 값을 추출한다. 그리고 그 두개의 값의 평균을 구하여 MEDIAN 컬럼으로 생성해주었다.

위에서 @ROWINDEX = -1; 을 선언해주었던 이유는 행의 인덱스를 1,2,3,4,... 가 아닌 0,1,2,3,... 으로 생성하기 위함이다.

ROWINDEX	VALUE
0	3
1	7
2	10
3	11

쿼리에 사용된 방식으로 중앙값을 구할 때, 위 테이블의 경우 마지막 행 인덱스 3을 2로 나눈 1.5 위 아래의 값을 평균하여 8.5를 중앙값으로 하는 반면에, 아래 테이블의 경우 마지막 행 인덱스 4를 2로 나눈 2에 해당하는 값인 7을 중앙값이 된다. 위의 테이블을 이용해 구한 이 중앙값의 정의에 해당하는 정확한 중앙값이다.

ROWINDEX	VALUE
1	3
2	7
3	10
4	11

 

 

<참고>

https://three-pleasure.tistory.com/256

[삼쾌한 IT강의] MySQL 변수 종류 및 사용법

https://techblog-history-younghunjo1.tistory.com/146

[SQL] MySQL - SET 과 서브쿼리 사용하기

https://techblog-history-younghunjo1.tistory.com/160

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