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

🔊 본 포스팅에서 사용되는 테이블의 자료와 출처는 HackerRank 임을 밝힙니다. 더 다양한 SQL 문제를 풀어보시려면HackerRank 사이트를 방문해 보세요!

 

HackerRank 사이트

 

이번 포스팅에서는 MySQL을 활용해 Median값을 출력해보는 문제를 풀어보려 한다. Median이란, 잘 알다시피 중앙값을 의미하며 보통 데이터 분포가 치우쳐져 있을 때 데이터의 대푯값으로 자주 설정하는 척도이다. 이런 중앙값을 SQL로 계산하는 방법에 대해 알아보자. 문제의 원본은 여기를 참조하자. 문제의 요구사항은 다음과 같다.

 

A median is defined as a number separating the higher half of a data set from the lower half. Query themedianof theNorthern Latitudes(LAT_N) fromSTATIONand round your answer to 4 decimal places.
(STATION이라는 테이블의 LAT_N 칼럼의 중앙값을 출력하라. 이 때 출력값을 소수점 5번째 자리에서 반올림하여 소수 4번재 자리까지 출력시켜라.)

테이블 형태는 다음과 같다.

 

주어진 테이블 STATION의 스키마

 

SQL에서 기본적으로 제공하는 집계함수로는 MAX(최댓값), MIN(최솟값), AVG(평균값) 등이 있다. 그래서 문제를 처음 풀 당시 MEDIAN() 이라는 메소드를 제공하지 않을까? 싶어서 구글링을 해보니 역시나 없었다..

 

그렇다면 다른 방법으로 중앙값을 출력하도록 해주어야 하는데 문제 해결 아이디어를 떠올릴 때 저번에 풀어보았던 문제에서 힌트를 얻었다. 바로 SET이라는 쿼리를 이용해 새로운 변수를 정의해주고 이를 이용해 테이블 행(Row)의 index를 나타내는 파생 칼럼을 만들어야 한다는 것! 우선 정답 SQL 구문을 살펴보자.

 

SET @rowIndex=-1;
SELECT ROUND(AVG(lat_n), 4) AS Median
FROM (SELECT @rowIndex:=@rowIndex+1 AS RowNumber,
                lat_n
      FROM station
      ORDER BY lat_n) sub
WHERE RowNumber IN (FLOOR(@rowIndex / 2), CEIL(@rowIndex / 2))

 

자, 이제 정답 SQL 구문을 하나씩 뜯어보자. 우선, 중앙값을 설정하는 것은 데이터를 정렬했을 때, 가운데 순번에 있는 숫자를 중앙값이라고 한다.(이 때, 정렬기준은 오름차순이던 내림차순이던 상관없음) 그렇기 때문에 SET을 활용해 테이블 행의 숫자를 세주자. 이 때 -1이라고 설정하는 이유는 이후에 중앙값을 찾을 때 데이터 개수가 홀수/짝수에 따라 설정 방법이 달라지기 때문이다.(이 부분은 하단에서 살펴보자.)

 

SET @rowIndex=-1;
SELECT @rowIndex:=@rowIndex+1,
       lat_n
FROM station
ORDER BY lat_n

 

위 SQL구문으로 우선 LAT_N기준으로 정렬했을 때 테이블의 행 숫자를 매기고 LAT_N 값을 출력하면 다음과 같아진다.

 

결과화면

 

이제 위 결과에서 중앙값을 찾아주면 되는데, 정답 SQL 구문의 WHERE 절이 한 번에 이해가 확 와닿지 않을 것이다. 따라서 예시를 한 번들어보자. 행의 첫 번째 Index는 0으로 설정했을 때이다. 우선 데이터 개수가 짝수개일 때이다.

 

RowNumber	Value
0	100
1	200
2	300
3	400

 

위에서 중앙값은 RowNumber가 1과 2일 때의 Value를 평균낸 값인 250이 된다. 따라서 마지막 RowNumber인 3을 2로 나누게 되면 1.5가 되고 이를 FLOOR(내림 처리)하면 1, CEIL(올림 처리)하면 2가 되게 된다. 따라서 올림, 내림처리한 각각의 RowNumber에 해당하는 Value 2개를 평균값을 내게 되면 중앙값이 된다.

 

그렇다면 반대로 데이터 개수가 홀수일 때를 살펴보자.

 

RowNumber	Value
0	100
1	200
2	300
3	400
4	500

 

위에서 중앙값은 300이 된다. 마지막 RowNumber인 4를 2로 나누게 되면 2로 나누어떨어진다. 그런데 어차피 정수이기 때문에 이를 FLOOR, CEIL 처리 하여도 동일하게 정수인 2가 나오게 된다. 또한 RowNumber = 2인 Value인 300 2개의 값을 평균값을 내어도 동일하게 300이 된다.

 

따라서 데이터 개수에 상관없이 첫 데이터 행의 Index가 0으로 시작할 때, 마지막 행 Index를 가지고 중앙값을 계산해낼 수 있다. 만약 필자의 설명으로도 이해가 잘 가지 않는다면 꼭 노트에 필기해서라도 이해하자! 머릿속으로 생각만 하는 것과 손으로 써보아서 시각으로 확인하는 것은 분명 다르다!

 

결국 위와 같은 과정들을 거쳐 이전에 보여주었던 정답 SQL 구문이 나오게 된다. 마지막으로 주의해야 할 점은 WHERE 구문에 마지막 행 Index를 정의해줄 때 RowNumber이 아닌 @rowIndex인 이유는 서브쿼리 구문에서 @rowIndex := @rowIndex+1 로 계속적으로 더해주었고 결국 마지막에 최종적으로 할당된 @rowIndex 변수에는 마지막 행의 Index가 담겨있기 때문이다.

 

SET @rowIndex=-1;
SELECT ROUND(AVG(lat_n), 4) AS Median
FROM (SELECT @rowIndex:=@rowIndex+1 AS RowNumber,
                lat_n
      FROM station
      ORDER BY lat_n) sub
WHERE RowNumber IN (FLOOR(@rowIndex / 2), CEIL(@rowIndex / 2))

 

[참조]

GeeksforGeeks - Calculate Median in MySQL