IT/모바일
「한빛 네트워크 기사 공모전」 입선작: 서민구
TOP N Query소개
Top N Query에 대해서 들어보았는지 모르겠다. Top N Query는 데이터베이스 상에 있는 자료 중 최상위 몇 개의 자료를 보기 위해 사용하는 자료이다. 예를 들어 학교에서 중간고사를 보았고(독자에게 중간고사의 악몽을 떠올리게 했다면 죄송^^), 이때 총점이 높은 순서대로 10명의 학생을 보기 위해 사용하는 쿼리가 바로 Top N Query의 예가 되겠다.
본격적으로 들억가기에 앞서 약간 몸부터 풀고 시작하자.
TOP N Query의 본질과도 연관성이 있는 문제이므로 잘 풀어보도록 하자
중복된 row를 제거하라
먼저 다음 코드를 실행하여 샘플 테이블을 만들도록 한다.
독자들이 잠시 동안 시도해보도록 하고 계속 읽어주기 바란다.
다음과 같은 쿼리는 어떨까?
실전! TOP N Query!
N 번째 까지 구하기
자 그러면 TOP N을 구해야 할 대상인 샘플을 보도록 하겠다. 언제나 유명한 우리의 EMP테이블이 대상이 될 것이다.
N번째까지가 아니라 N등까지를 구하라
앞에서의 TOP N 구하는 방식은 SAL순서대로 소팅한 뒤에 제일 먼저 나오는 10개의 row를 뽑은 것이다. 이번에는 N등까지를 구해보도록 하자. 자, 일단 독자 스스로 구현해보도록 하자. 못하면 못하는 대로 한시간은 고민하기 바란다.
부서별 N등까지를 구하라
EMP테이블과 자주 같이 사용되는 테이블은 DEPT테이블로서 EMP에 나타난 DEPTNO와 조인하면 부서명을 얻어올 수 있다.
OLAP를 위한 글도 analytic function을 위한 글도 아니므로 간단히만 짚어보도록 하자.
실제로 analytic function은 매우 강력한 오라클의 분석 기능이지만, ANSI SQL에 위배되고 또한 RDBMS의 근본적인 관계형 맵핑이라는 기본원칙을 무시한다는 비판도 있는 것은 사실이지만, 이러한 쿼리를 역시 100% 완벽한 관계형 데이터베이스 개념에 입각한 쿼리로 바꾸는 것은 거의 불가능할 뿐만 아니라 anlytic function에 비해서 낮은 성능을 보인다.
이러한 내용에 대한 논쟁에 관심이 있다면 http://www.oracle.com/의 오라클 매거진이나 오라클 매거진 내의 ask Tom 컬럼을 참고하기 바란다.
참고자료
TOP N Query소개
Top N Query에 대해서 들어보았는지 모르겠다. Top N Query는 데이터베이스 상에 있는 자료 중 최상위 몇 개의 자료를 보기 위해 사용하는 자료이다. 예를 들어 학교에서 중간고사를 보았고(독자에게 중간고사의 악몽을 떠올리게 했다면 죄송^^), 이때 총점이 높은 순서대로 10명의 학생을 보기 위해 사용하는 쿼리가 바로 Top N Query의 예가 되겠다.
본격적으로 들억가기에 앞서 약간 몸부터 풀고 시작하자.
TOP N Query의 본질과도 연관성이 있는 문제이므로 잘 풀어보도록 하자
중복된 row를 제거하라
먼저 다음 코드를 실행하여 샘플 테이블을 만들도록 한다.
create table temp (id number primary key, num number not null) / insert into temp values(1,1) / insert into temp values(2,2) / insert into temp values(3,3) / insert into temp values(4,4) / insert into temp values(5,1) / insert into temp values(6,1) / commit / select * from temp /실행화면은 다음과 같다.
테이블이 생성되었습니다.
1 개의 행이 만들어졌습니다.
1 개의 행이 만들어졌습니다.
1 개의 행이 만들어졌습니다.
1 개의 행이 만들어졌습니다.
1 개의 행이 만들어졌습니다.
1 개의 행이 만들어졌습니다.
커밋이 완료되었습니다.
ID NUM
---------- ----------
1 1
2 2
3 3
4 4
5 1
6 1
6 개의 행이 선택되었습니다.
위에서 보다시피 테이블의 ID컬럼은 primary key가, NUM컬럼은 데이터가 들어있는 형태이다. 그런데 이 테이블에는 NUM컬럼의 데이터가 중복되어 들어가 있다. 즉, ID=1일 때 NUM=1이며, ID=5일 때와 ID=6일 때도 NUM값은 1이다. 이제 NUM필드에 중복된 값을 모두 삭제해버리도록 해보자. 즉, NUM값이 1인 row를 어느것이든 한 개만 남기도록 하는 것이다.
독자들이 잠시 동안 시도해보도록 하고 계속 읽어주기 바란다.
다음과 같은 쿼리는 어떨까?
SQL> r
1 select * from temp t1, temp t2
2* where t1.rowid > t2.rowid and t1.num = t2.num
ID NUM ID NUM
---------- ---------- ---------- ----------
5 1 1 1
6 1 1 1
6 1 5 1
SQL>
이 쿼리는 중복된 row를 찾아준다. 따라서 이 쿼리를 사용하면 간단하게 중복된 데이터를 삭제할 수 있을 것이다. 그러나 위의 쿼리는 중복된 데이터를 중복되게 찾아준다. 즉, 중복된 값을 가지고 있는 row가 5,6,6으로 6이 2회 나타난다. 결과가 이렇게 나오는게 아니라 정확히 중복된 row의 ID들이 나타나게 하려면 어떻게 해야 할까?
select * from temp t1
where t1.rowid > (select min(rowid) from temp where t1.num = num)
/
ID NUM
---------- ----------
5 1
6 1
자 이제 원하는 데이터를 찾았으니 삭제하는 것은 매우 간단하다.
1 delete from temp
2 where id in
3 (
4 select id from temp t1
5 where t1.rowid >
6 (
7 select min(rowid) from temp
8 where t1.num = num
9 )
10* )
11 /
2 행이 삭제되었습니다.
SQL> select * from temp;
ID NUM
---------- ----------
1 1
2 2
3 3
4 4
SQL>
필자의 SQL스타일이 보기 싫다고 탓하지는 않기 바란다. 내용전달에 힘을 쏟느라 거기까지는 여력이 못미쳤으니… 자, 이 문제를 처음 접했고 훌륭히 풀어냈다면 당신은 뛰어난 sql 구사자이다. 이 문제가 복잡한 이유는 바로 셀프조인을 활용해야 하기 때문이다. 그리고 이러한 셀프조인의 아이디어를 생각해내고 활용하는 것이 바로 OLAP에서 나타나는 다차원 쿼리가 될 것이다. 이에 대한 깊은 내용은 생략하고(사실 알지도 못한다 -_-; 참 유연히 넘어가지 않는가? 약간 뻔뻔하다.. ^^), 우리의 본격적인 작업으로 넘어가 보겠다.
실전! TOP N Query!
N 번째 까지 구하기
자 그러면 TOP N을 구해야 할 대상인 샘플을 보도록 하겠다. 언제나 유명한 우리의 EMP테이블이 대상이 될 것이다.
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- -------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL>
멋지다! 자 이제 문제는 SAL이 가장 높은 순으로 10명을 뽑으라는 것이다. 일단 간단히 ORDER BY로 SAL을 정렬하면 될 것이다.
SQL> SELECT * FROM EMP
2 ORDER BY SAL DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- -------
7839 KING PRESIDENT 81/11/17 5000 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7369 SMITH CLERK 7902 80/12/17 800 20
14 개의 행이 선택되었습니다.
SQL>
자 이 쿼리를 한 번 더 둘러싸주면 간단히 10명을 뽑을 수 있다.
SQL> R
1 SELECT E.* FROM
2 (
3 SELECT * FROM EMP
4 ORDER BY SAL DESC
5 ) E
6* WHERE ROWNUM <= 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- -------
7839 KING PRESIDENT 81/11/17 5000 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
10 개의 행이 선택되었습니다.
SQL>
쿼리가 약간 이상해보이는가? E.* 부분이 그럴지도 모르겠다. 저러한 표현이 가능하다는 것 정도는 알아두자. 고백할 것이 있다. 이 쿼리는 오라클 8i 이상에서만 작동하며 오라클 7이나 오라클 8에서는 동작하지 않는다. 왜냐하면 이들 오라클 7이나 8버전에서는 서브쿼리내에 order by를 사용할 수 없기 때문이다. 이러한 경우에는 약간의 편법을 사용해야 한다.
SQL> GET B
1 SELECT EMPNO, ENAME, ABS(SAL) FROM
2 (
3 SELECT E.EMPNO, -E.SAL SAL, MAX(E.ENAME) ENAME FROM EMP E
4 GROUP BY -E.SAL, E.EMPNO
5 )
6* WHERE ROWNUM <= 10
SQL> /
EMPNO ENAME ABS(SAL)
---------- -------------------- ----------
7839 KING 5000
7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
10 개의 행이 선택되었습니다.
SQL>
이번엔 쿼리가 약간 복잡하다. 세세히 뜯어보도록 하자.
3 SELECT E.EMPNO, -E.SAL SAL, MAX(E.ENAME) ENAME FROM EMP E 4 GROUP BY -E.SAL, E.EMPNOgroup by를 사용해 e.sal으로 묶어준다. 그러나 이렇게만 묶어버리면 sal이 중복되는 row들이 사라져버린다. 따라서 여기서는 EMPNO(EMP테이블의 primary key이다)까지 묶어서 group by를 해주었다. group by 는 desc구문이 존재하지 않으므로 sal이 큰 순으로 나오려면 -E.SAL 형태로 group by를 해주어야 한다. 그리고 group by에 나타나지 않은 컬럼들인 E.ENAME과 은 단순히 MAX()함수를 취해주었다. 이것은 항상 그룹이 단일 row단위로 들어오므로 언제나 단순히 E.ENAME을 돌려준다.
1 SELECT EMPNO, ENAME, ABS(SAL) FROM 2 ( 3 SELECT E.EMPNO, -E.SAL SAL, MAX(E.ENAME) ENAME FROM EMP E 4 GROUP BY -E.SAL, E.EMPNO 5 ) 6* WHERE ROWNUM <= 10이제 전체적인 데이터가 도달한 상태에서 10개의 row를 뽑아주기만 하면 된다. 단, SAL 값은 -에 의해 음수로 변해서 왔으므로 이는 다시 ABS함수를 쓰던가 다시 -를 해주어 원래 양수값으로 바꿔줘야 한다. 여기서 제시한 오라클 7,8에서의 서브쿼리에서의 소팅 방법은 group by를 사용해 서브쿼리에서 소트를 할 수 있다는 것과, 소트할 컬럼을 group by의 제일 앞에 명시하고, 다음에 중복된 row가 사라지는 것을 막기위해 pk를 group by에 명시하며 마지막으로, group by 에 명시되지 않았으면서 select 절에 나타난 컬럼들에 max()를 취해준다는 것이다. 그러나 다음과 같이 해볼 수도 있겠다.
SQL> get b
1 SELECT EMPNO, ENAME, ABS(SAL) FROM
2 (
3 SELECT E.EMPNO, -E.SAL SAL, E.ENAME ENAME FROM EMP E
4 GROUP BY -E.SAL, E.EMPNO, E.ENAME
5 )
6* WHERE ROWNUM <= 10
SQL> /
EMPNO ENAME ABS(SAL)
---------- -------------------- ----------
7839 KING 5000
7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
10 개의 행이 선택되었습니다.
SQL>
여기서는 모든 컬럼을 group by에 명시하되 sal컬럼만 앞에 가게 해준다는 것이다. 어느 방법이든 맘에 드는 것을 골라 쓰면 되겠다.
N번째까지가 아니라 N등까지를 구하라
앞에서의 TOP N 구하는 방식은 SAL순서대로 소팅한 뒤에 제일 먼저 나오는 10개의 row를 뽑은 것이다. 이번에는 N등까지를 구해보도록 하자. 자, 일단 독자 스스로 구현해보도록 하자. 못하면 못하는 대로 한시간은 고민하기 바란다.
1 SELECT EMPNO, ENAME, SAL FROM EMP E1
2 WHERE (
3 SELECT COUNT(1) FROM EMP E2
4 WHERE E1.SAL < E2.SAL
5 ) <= 10
6* ORDER BY SAL DESC
SQL> /
EMPNO ENAME SAL
---------- -------------------- ----------
7839 KING 5000
7788 SCOTT 3000
7902 FORD 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
7654 MARTIN 1250
11 개의 행이 선택되었습니다.
SQL>
ORDER BY SAL DESC가 존재하지만 이 ORDER BY 문장은 10개의 데이터에 대해서만 SORT를 하므로 SORT비용이 매우 낮음에 주목하라. 그러나 이에 비해 앞서 했던 10번째까지의 사람 구하기는 GROUP BY나 ORDER BY에 의해서 사용되었으므로 SORT비용이 매우 컸다.
부서별 N등까지를 구하라
EMP테이블과 자주 같이 사용되는 테이블은 DEPT테이블로서 EMP에 나타난 DEPTNO와 조인하면 부서명을 얻어올 수 있다.
SQL> SELECT EMPNO, ENAME, SAL, DNAME
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO
4 /
EMPNO ENAME SAL DNAME
---------- -------------------- ---------- ----------------------------
7369 SMITH 800 RESEARCH
7499 ALLEN 1600 SALES
7521 WARD 1250 SALES
7566 JONES 2975 RESEARCH
7654 MARTIN 1250 SALES
7698 BLAKE 2850 SALES
7782 CLARK 2450 ACCOUNTING
7788 SCOTT 3000 RESEARCH
7839 KING 5000 ACCOUNTING
7844 TURNER 1500 SALES
7876 ADAMS 1100 RESEARCH
7900 JAMES 950 SALES
7902 FORD 3000 RESEARCH
7934 MILLER 1300 ACCOUNTING
14 개의 행이 선택되었습니다.
SQL>
우리가 하고자 하는 것은 각 부서별로 2등까지(두 번째 까지가 아니다)의 사람을 구하는 것이다. 원하는 데이터 형태는 아래와 같이 나타난다.
DNAME RANK EMPNO ENAME
부서A 1 XXXX 홍길동
2 YYYY 김길동
부서B 1 NNNN 박길동 <- 부서 B에는 한명의 사원만 존재하는 경우
부서C 1 XXXX 서길동
2 ZZZZ 황길동
이것은 쉽지 않을 것이다. 머리 싸매고 연구해보자.
1 SELECT * FROM
2 (
3 SELECT DNAME, EMPNO, ENAME, SAL, RANK() OVER ( PARTITION BY E.DEPTNO ORDER BY SAL DESC ) RNK
4 FROM DEPT D, EMP E
5 WHERE D.DEPTNO = E.DEPTNO
6 )
7* WHERE RNK <= 2
SQL> /
DNAME EMPNO ENAME SAL RNK
---------------------------- ---------- -------------------- ---------- ----------
ACCOUNTING 7839 KING 5000 1
7782 CLARK 2450 2
RESEARCH 7788 SCOTT 3000 1
7902 FORD 3000 1
SALES 7698 BLAKE 2850 1
7499 ALLEN 1600 2
6 개의 행이 선택되었습니다.
SQL>
답을 보고 의아하게 생각하는 사람들이 있을지 모르겠다. 그러나 사실 ANSI SQL가지고만 풀기에 이 문제는 난이도가 높다. 위에 등장한 SQL코드에는 8i부터 등장한 analytic function이 사용되었다. 이들을 사용해 이 문제와 같은 매우 복잡한 SQL문장을 단 한번에 한 큐에 날려보낼 수 있으며, 더군다나 이들은 OLAP의 다차원 쿼리에 활용될 수 있는 강력한 무기이다.
OLAP를 위한 글도 analytic function을 위한 글도 아니므로 간단히만 짚어보도록 하자.
3 SELECT DNAME, EMPNO, ENAME, SAL, RANK() OVER ( PARTITION BY E.DEPTNO ORDER BY SAL DESC ) RNK 4 FROM DEPT D, EMP E 5 WHERE D.DEPTNO = E.DEPTNO바로 이 부분이 핵심인데 그 중에서도 핵심은 RANK() OVER ( PARTITION BY E.DEPTNO ORDER BY SAL )의 의미를 파악하면 이 쿼리를 완전히 아는 것이다. 먼저 RANK() 함수는 순위를 구하는 함수이며 PARTITION 은 부분을 나누는 구문이다. 그리고 ORDER BY는 각 PARTITION(부분) 내에서의 정렬을 위한 컬럼을 명시하는데 쓰인다. 즉, 위의 코드는 E.DEPTNO 를 기준으로 파티션들을 분할하고 그들 각각을 SAL에 의해서 역순으로 정렬한 순위를 얻어오라는 것이다. 이 코드만 실행하면 아래와 같은 결과를 얻는다.
SQL> GET B
1 SELECT DNAME, EMPNO, ENAME, SAL, RANK() OVER ( PARTITION BY E.DEPTNO ORDER BY SAL DESC ) RNK
2 FROM DEPT D, EMP E
3* WHERE D.DEPTNO = E.DEPTNO
SQL> /
DNAME EMPNO ENAME SAL RNK
---------------------------- ---------- -------------------- ---------- ----------
ACCOUNTING 7839 KING 5000 1
7782 CLARK 2450 2
7934 MILLER 1300 3
RESEARCH 7788 SCOTT 3000 1
7902 FORD 3000 1
7566 JONES 2975 3
7876 ADAMS 1100 4
7369 SMITH 800 5
SALES 7698 BLAKE 2850 1
7499 ALLEN 1600 2
7844 TURNER 1500 3
7521 WARD 1250 4
7654 MARTIN 1250 4
7900 JAMES 950 6
14 개의 행이 선택되었습니다.
SQL>
보다시피 RANK()가 각 파티션 내의 순위를 구해주고 있다.
1 SELECT * FROM 2 ( 3 SELECT DNAME, EMPNO, ENAME, SAL, RANK() OVER ( PARTITION BY E.DEPTNO ORDER BY SAL DESC ) RNK 4 FROM DEPT D, EMP E 5 WHERE D.DEPTNO = E.DEPTNO 6 ) 7* WHERE RNK <= 2그리고 위에서처럼 SELECT문으로 한번 더 묶고 RNK로 FILTERING을 해주어 원하는 결과를 획득한다.
실제로 analytic function은 매우 강력한 오라클의 분석 기능이지만, ANSI SQL에 위배되고 또한 RDBMS의 근본적인 관계형 맵핑이라는 기본원칙을 무시한다는 비판도 있는 것은 사실이지만, 이러한 쿼리를 역시 100% 완벽한 관계형 데이터베이스 개념에 입각한 쿼리로 바꾸는 것은 거의 불가능할 뿐만 아니라 anlytic function에 비해서 낮은 성능을 보인다.
이러한 내용에 대한 논쟁에 관심이 있다면 http://www.oracle.com/의 오라클 매거진이나 오라클 매거진 내의 ask Tom 컬럼을 참고하기 바란다.
참고자료
- 오라클 SQL 튜닝 실무사례(대청)
- expert one on one Oracle(wrox)
- Oracle Magazine
TAG :
이전 글 : 이해하기 어려운 C ?
다음 글 : 이아스님이 제공하는 자바 헤드라인
최신 콘텐츠