HackerRank - Weather Observation Station 20_MySQL

안녕하세요!

 

해커랭크에 있는 Weather Observation Station 20 문제입니다.

SQL에서는 중앙값을 직접 구할 수가 없어서

중앙값 개념을 잘 이해해야 응용해서 해결 할 수 있습니다.

 

---

중앙값은 말 그대로 크기 순서대로 전체 데이터를 보았을 떄 한 가운데 위치하고 있는 값을 의미합니다.

데이터의 개수가 홀수라면 한 가운데 있는 값을 중심으로

좌우에 짝수 개의 데이터들이 분포하고 있겠죠.

하지만,

데이터의 개수가 짝수라면 한 가운데 위치하고 있는 값을 찾을 수 없습니다.

이 때는 가운데 위치하고 있는 두 개의 숫자를 더하고 평균을 구한 값을 이용합니다.

 

Wikipedia

---

개념 이해를 바탕으로 문제를 해결해 보겠습니다.

 

- 문제

STATION 테이블에서 Northern Latitudes(LAT_N) 의 중앙값을 소수 넷째자리까지 구하라.

 

- 테이블

---

- 문제 해결

1) 우선 ROW_NUMBER() 함수를 사용하여 데이터마다 순서를 매긴 테이블을 임시로 생성합니다.

WITH median as 
    (SELECT lat_n
          , ROW_NUMBER() OVER(ORDER BY lat_n) rn
     FROM station)

2) 데이터의 개수를 모르는 상태이지만 중앙값의 개념을 응용하여 WHERE 절에

    ROW_NUMBER()의 평균(순서의 평균)을 CEIL(올림)한 값 또는 FLOOR(내림)한 값이라는 조건을 줍니다.

    홀수 개인 경우 올림이든 내림이든 같기에 하나의 값이 선택될 것이고,

    짝수 개인 경우 올림한 값과 내림한 값의 평균이 구해질 것입니다.

    요구 사항인 소수 넷째자리 출력을 위해 ROUND(AVG(lat_n), 4)를 입력합니다.

SELECT ROUND(AVG(lat_n), 4)
FROM median
WHERE rn IN (SELECT CEIL(AVG(rn)) FROM median)
   OR rn IN (SELECT FLOOR(AVG(rn)) FROM median)

 

---

- 쿼리 결과

WITH median as 
    (SELECT lat_n
          , ROW_NUMBER() OVER(ORDER BY lat_n) rn
     FROM station)

SELECT ROUND(AVG(lat_n), 4)
FROM median
WHERE rn IN (SELECT CEIL(AVG(rn)) FROM median)
   OR rn IN (SELECT FLOOR(AVG(rn)) FROM median)

---

- 다른 방법

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))

 

논리는 순서의 최종 값을 반으로 나누어 해결하는 것입니다.

이를 위해 인덱스를 0에서 시작해야 합니다.

SET을 통해 rowIndex 라는 변수를 생성하는데 값은 -1 로 정합니다.

변수는 각 행마다 1씩 증가하게 합니다.

이렇게 되면 변수 rowIndex 는 0으로 시작합니다.

그리고 lat_n 값도 같이 출력되게하는 서브쿼리를 사용하여 테이블을 생성합니다.

이 테이블로부터 lat_n의 평균을 소수 넷째자리까지 불러옵니다.

WHERE 절은 위에서 설명한 논리대로 순서의 최종값(@rowIndex)에서

반으로 나누어 올림 또는 내림한 값이라는 조건을 달아줍니다.

 

아래는 0부터 시작하는 이유입니다.

---

홀수 개의 데이터를 예로 들겠습니다.

이미지1. 순서의 최종값인 4를 반으로 나누면 2가 됩니다.

이미지2. 순서의 최종값인 5를 반으로 나누면 2.5가 됩니다.

이미지1에서 볼 수 있듯 0에서 시작하여 순서의 최종값인 4를 반으로 나누면 중앙값인 2를 알 수 있습니다.

하지만,

이미지2처럼 1로 시작하면 순서의 최종값인 5를 반으로 나누면 2.5가 되어 실제 중앙값인 3과 다릅니다.

 

짝수 개의 데이터를 예로 들겠습니다.

 

이미지3. 순서의 최종값인 3을 반으로 나누면 1.5가 됩니다.

이미지4. 순서의 최종값인 4를 반으로 나누면 2가 됩니다.

이미지3에서 볼 수 있듯 0에서 시작하여 순서의 최종값인 3을 반으로 나누면 1.5가 계산됩니다.

짝수 개의 데이터는 중앙값을 바로 찾을 수 없기에 가운데 2개의 수의 평균을 내어 사용합니다.

이미지3에서 평균 1.5의 올림은 2, 내림은 1이므로 1과 2의 평균값이 중앙값이 됩니다.

하지만,

이미지4처럼 1로 시작하면 순서의 최종값인 4를 반으로 나누었을 경우 2가 되기에 중앙값을

찾지 못하게 됩니다.

---

 

 

 

- 출처

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

Weather Observation Station 20 | HackerRank