[Hacker Rank] SQL Intermediate 'Weather Observation station 20' (Medium)

 

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

Weather Observation Station 20 | HackerRank

SELECT ROUND(AVG(T1.LAT_N), 4)
FROM (
    SELECT LAT_N,
           ROW_NUMBER() OVER (ORDER BY LAT_N ASC) AS row_asc,
           ROW_NUMBER() OVER (ORDER BY LAT_N DESC) AS row_desc
    FROM STATION
) AS T1
WHERE T1.row_asc IN (T1.row_desc, T1.row_desc - 1);

 

🔸MEDIAN 함수를 대체하기 위해 윈도우 함수인 ROW_NUMBER() 사용

MySQL에서는 MEDIAN 함수를 직접 지원하지 않습니다~!

핵심은 ROW_NUMBER() OVER (ORDER BY...)

1)  위도(LAT_N) 를 작은 값부터 순서대로 번호를 매김합니다

 ROW_NUMBER() OVER (ORDER BY LAT_N ASC) AS row_asc

2) 이번에는 큰값부터 번호 매김합니다.

 ROW_NUMBER() OVER (ORDER BY LAT_N DESC) AS row_desc

 

그렇게 만들어진 T1 table은 아래와 같습니다

SELECT LAT_N,
           ROW_NUMBER() OVER (ORDER BY LAT_N ASC) AS row_asc,
           ROW_NUMBER() OVER (ORDER BY LAT_N DESC) AS row_desc
    FROM STATION

예시 입니다.

LAT_N	row_asc	row_desc
10	1	5
20	2	4
30	3	3
40	4	2
50	5	1

 

이렇게 asc와 desc를 동시에 쓰는 이유는

데이터개수를 몰라도 중앙에 위치한 값(짝수일때 둘 , 홀수 일때는 하나)을 자동으로 찾기 위해서 입니다.

 

🔸왜 이렇게 파생 테이블을 만드는가?

1. row_asc / row_desc 번호를 미리 붙여놓고
   → “이 값이 앞에서 몇 번째인지, 뒤에서 몇 번째인지”를 같이 볼 수 있게 함.
2. 그다음 WHERE 조건으로
   → 중앙값에 해당하는 행만 필터링.
3. 마지막 SELECT에서 AVG()로 중앙값 산출.

3) WHERE절에서 중앙값에 해당하는 행만 필터링

오름차순으로 센 순서(row_asc)와 내림차순으로 센 순서(row_desc)

이 두 순서가 중앙에서 만나거나, 거의 만나서 ±1 차이 나는 경우만 잡아내는 겁니다.

WHERE T1.row_asc IN (T1.row_desc, T1.row_desc - 1)

그럼 데이터 개수가

1. 홀수 개일 때
   • 가운데 딱 1개의 값이 나오고, 그 값 하나만 선택됨 (row_asc = row_desc).
   • AVG(중앙값 하나) = 중앙값 그대로.
2. 짝수 개일 때
   • 가운데 2개의 값이 선택됨 (row_asc = row_desc - 1 관계).
   • AVG(두 값) = 중앙값.

즉, AVG() 함수가 알아서 1개든 2개든 처리해 줍니다.

---

🔸ROW_NUMBER() 윈도우 함수

: 결과 집합 내에서 각 행에 순번을 매기는 함수입니다.

 

기본 문법

ROW_NUMBER() OVER ( [PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC )

 

• ROW_NUMBER() → 순번을 만들어주는 함수
• OVER() → “어떤 기준으로 순번을 매길지”를 지정
• PARTITION BY (선택) → 그룹별로 순번을 새로 매김
• ORDER BY → 순번을 매길 때 기준이 되는 컬럼

이번에 문제를 풀때는 PARTITION BY는 필요하지 않았는데요

어떻게 사용되는지 확인해볼게요

그룹별 순번 (PARTITION BY)

SELECT NAME, DEPT, SALARY,
       ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY SALARY DESC) AS rn
FROM EMPLOYEE;

 

결과)

 

• HR/IT 그룹별로 순번을 다시 매김
• PARTITION BY → 그룹 안에서 순번 재시작

 

NAME	DEPT	SALARY	rn
B	HR	4000	1
A	HR	3000	2
D	IT	4500	1
C	IT	3500	2

특징)

1. 중복 순번 없음 → 항상 1,2,3,…
2. 순서를 기준으로 순번을 매길 때 유용
3. 집계와 조합 가능 → 예: 중앙값, 상위 N개 추출, 순위 기반 필터링