[SQL] 13. SQL 레코드 순서 응용2 (예제 - 레코드 순번 활용 : 중앙값 구하기)

[SQL 레코드 순서 응용2]

- 레코드 안의 순번을 활용하는 예제

 앞 포스팅에서는 레코드 안의 순번을 매기는 예제를 설명하였다. 이번 포스팅에서는 테이블에 만들어진 순번을 활용하는 다양한 예제를 실습해볼것이다.

 

 

 

1. 중앙값(Median) 구하기

 

< 중앙값(Median)이란? >

숫자를 크기에 따라 정렬하고 양끝에서부터 수를 세는 경우 중앙에 오는 값을 뜻한다. 여기서 중요한 점은 평균(Mean/Average)과의 차이인데, 평균은 이상점(Outlier)에 영향을 받기 때문에 이상한(너무 크거나 작은) 데이터가 들어오면 대표성을 잃게된다. 하지만 중앙값은 그 영향을 받지 않아 데이터들의 대표적인 지표로서 훌륭한 역할을 해낸다고 볼 수 있다.

 

실은, Oracle10g 버전 이후부터 아래처럼 중앙값을 쉽게 구할수 있는 집계 함수 Median( )를 지원한다. 하지만 학습을 위해 직접 중앙값을 구하는 쿼리를 작성해 볼것이다. 

 

- Oracle의 중앙값을 구하는 MEDIAN( ) 함수 -

 

 

 

 

- 집합 지향적 방법

효율적인 쿼리는 아니지만, 테이블을 상위 집합과 하위 집합으로 분할한 뒤 공통 부분을 검색하는 방법이다. 과정은 아래와 같다.

 

1. HAVING 구 안에서 CASE 식을 통해 표현한 하위집합(아래 쿼리의 6,7행)과 상위집합(아래 쿼리의 8,9행)을 분할한다.

2. 하위집합의 가장 큰 값과 상위집합의 가장 작은 값을 구한다.

3. 홀수의 경우엔 2번에서 구한 값이 같지만, 짝수의 경우엔 2번에서 구한 값이 다르기 때문에 AVG( ) 함수를 사용하여 두 값의 평균을 구해준다.

 

- 집합 지향적으로 중앙값을 구하는 방법 -

 

 

 

 

- 절차 지향적 방법 1

레코드를 양쪽 끝에서부터 숫자를 세며 중앙값을 구하는 방법도 있다. 쿼리는 다음과 같다.

 

- 절차 지향적으로 중앙값을 구하는 방법 1 -

 

 

 

weight의 오름차순으로 순번을 매긴 'hi' 레코드와 내림차순으로 순번을 매긴 'lo' 레코드를 비교하여 중앙값을 계산하는 로직으로 이루어져있다. 위의 쿼리는 WHERE절의 역할이 이해하기 어려운데, 무엇보다 책에서 설명하는 그림이 이해에 도움이 빠를 것 같아 가져와 보았다.

 

- 절차 지향형 방법 설명을 위한 그림 -

 

 

 

 

- 절차 지향적 방법 2

절차 지향적 방법 1에서는 ORDER BY 정렬이 두번 사용되면서 쿼리의 성능을 저하시키는 것을 알 수 있다. 위에서 사용했던 ROW_NUMBER( )을 사용하여 성능을 높이는 절차 지향적 쿼리를 만들어 보려한다. 성능이 개선된 쿼리는 아래와 같다.

 

- 절차 지향적으로 중앙값을 구하는 방법 2 -

 

 

위의 쿼리에서 서브쿼리 내부에서 생성되는 테이블은 다음과 같다. diff 필드는 값이 앞의 두 필드의 차를 계산한 것이고 이중 0~2인 값을 찾아내어 평균을 구하는 로직이므로 그 결과는 60이 된다.

 

위의 쿼리는 정렬을 한 번만 사용하므로, 성능이 개선된 쿼리라 볼 수 있겠다.

 

weight	ROW_NUMBER( )	2 * ROW_NUMBER( )	COUNT(*)	diff
50	1	2	7	-5
55	2	4	7	-3
55	3	6	7	-1
60	4	8	7	1
72	5	10	7	6
72	6	12	7	5
72	7	14	7	7

- 내부 서브쿼리로 부터 만들어진 테이블 -

 

 

 

 

 

다음 포스팅에서는 순번을 사용한 테이블을 분할하는 예시를 설명하고자 한다.