SQL 문제풀이 3)

안녕하세요. 최근에는 하루 2~3시간 정도 SQL 문제풀이에 집중하고 있습니다. Medium 수준인데 풀이를 안보면 풀 수가 없네요. 대신 풀이는 확실하게 분석해서 풀어봅니다.

 

문제의 요구사항은 다음과 같습니다.

1. 테이블의 Lat_n 값의 median 값을 구해라

2. 소수점 4번째자리에서 반올림 해라 

 

median 값이 무엇인지 wiki를 찾아보니 다음과 같았습니다.

1. 행의 개수가 홀수이면 중간값을 구하며, 짝수이면 중간에 있는 값 2개의 평균을 구해라

 

예를들면 개수가 홀수인 리스트 1, 2, 3, 4, 5가 주어졌을 때 median 값은 3입니다.

개수가 짝수인 리스트 1, 2, 3, 4, 5, 6이 주어졌을 때 median 값은 3.5입니다.

 

해당 풀이에는 2가지가 있었는데요.

 

첫번째 풀이부터 알아보겠습니다.

 

SELECT ROUND(Lat_n) FROM STATION x, STATION y
Group by x.Lat_n 
Having sum(sign(1 - sign(y.Lat_n - x.Lat_n)))/count(*) > 0.5 LIMIT 1

 

크로스 조인하여 테이블 내 두 개의 Lat_n값을 비교하는 방식입니다.

즉, x 값이 1일때 y 값이 1, 2, 3, 4, 5이며 x 값이 2일때 y 값이 1, 2, 3, 4, 5 이렇게 나올 수 있는거죠

 

x가 1일 때 

y가 1일 때 sum(sign(1-sign(y - x)))/count(*) = 1/count(*)

y가 2일 때 sum(sign(1-sign(y - x)))/count(*) = 0/count(*)

y가 3일 때 sum(sign(1-sign(y - x)))/count(*) = 0/count(*)



x가 2일 때

y가 1일 때 sum(sign(1-sign(y - x)))/count(*) = 1/count(*)

y가 2일 때 sum(sign(1-sign(y - x)))/count(*) = 1/count(*)

y가 3일 때 sum(sign(1-sign(y - x)))/count(*) = 0/count(*)

 

구한값들을 모두 SUM 하여 1/count 개수가 50%을 처음으로 넘었을 때 중간값이므로 중간값을 가져올 수 있습니다.

 

 

 

두번째 풀이입니다.

Set @row_index = -1;

SELECT ROUND(avg(lat_n), 4) FROM 
(SELECT @row_index:= @row_index +1 as row_index, lat_n FROM STATION ORDER BY lat_n) as l
Where l.row_index IN (Floor(@row_index/2), Ceil(@row_index/2))

 

lat_n을 오름차순으로 정렬 후 각 행에 대한 row_index를 붙입니다.

row_index 값이 중간 사이일 경우를 조건으로 하여 값이 2개일 경우 평균을 가져와 중간값을 구합니다.

 

 

첫번째 풀이방식의 경우 크로스조인으로 행이 많을수록 속도가 저하되니 두번째 풀이방식을 선호하시면 될 것같습니다.

 

 

위의 풀이말고 더 좋은 방안이 2가지가 더 있습니다. 이에 대해서 덧붙여서 알아보도록 하겠습니다.

 

SELECT ROUND(AVG(LAT_N),4) FROM (
SELECT LAT_N, ROW_NUMBER() OVER(ORDER BY LAT_N) as lo,
ROW_NUMBER() OVER(ORDER BY LAT_N DESC) as hi FROM STATION
) Works
Where Works.hi IN (lo, lo+1, lo-1)

바로 window 함수를 이용하여 median 값을 구하는 방식입니다.

LAT_N을 기준으로 오름차순, 내림차순으로 순번을 정한다음 레코드의 개수가 홀수일 때 hi = lo

레코드의 개수가 짝수일 때 hi = lo+1, lo-1로 평균값을 구합니다.

해당 방법은 윈도우 함수 내 정렬을 2번하며, 서브쿼리를 사용하는 방법으로 앞서 설명해드렸던 방식보다 성능이 훨씬 좋습니다.