오라클 실습 3. 서브쿼리와 ROWNUM을 이용한 그룹화 되지 않는 함수에 그룹함수 적용하기.

그룹함수를 이용하기 위해서는 GROUP BY를 이용해 그룹화를 해야한다.

만약 이런 데이터가 있다고 가정하자.

이 데이터에서 만약 1~8까지의 사람 중 가장 높은 값을 기록한 사람과 나머지 사람중에서 가장 높은 값을 기록한 사람을 테이블로 나타내자고 한다. 이들은 서로 공통점이 없다. 어떻게 그룹화를 할 수 있을까? 이를 할 수 있게 하는 방법이 바로 ROWNUM이다. ROWNUM은 하드디스크나 메모리에서 데이터를 끌어올린 순서가 표시된다.

먼저 생각해낼 수 있는 방법은 WHERE절로 0~8까지, 그리고 9~20까지를 떨군 다음 따로따로 구해서 UNION ALL 로 합치는 것이다. 이 때, ROWNUM은 문제점이 생긴다.

ROWNUM은 1부터 세지 않으면 중간부터 값을 찾을 수 없다. 그렇기 때문에 서브쿼리를 이용해서 전체 ROWNUM을 만드는 가상 테이블을 생성하고, 그 테이블에서 찾아서 사용하는 방법을 이용한다.

select MAX(POINT) 
from GOGAK
where ROWNUM > 0 and ROWNUM <9
UNION ALL
select MAX(POINT) 
from (
select GNO, POINT, ROWNUM "NO" From GOGAK
)
where NO > 8 and NO <21;

그러나 이 방법은 지나치게 비효율적이다. 

자세히 보면 ROWNUM에 조건식이 걸려있다. 조건식을 이용할 수 있도록 하는 구문은 CASE문이다. 따라서 CASE문을 이용해보자.

select MAX(point), G
from(
    select point, case when ROWNUM < 9 then 1 else 2 end "G"
    FROM gogak
)
GROUP BY G;

서브쿼리 내에 CASE문을 통해 ROWNUM이 9 아래면 1, 이상이면 2로 설정함으로써 1과 2라는 공통점이 있는 새 컬럼을 생성한 후, 이 컬럼을 그룹화 한다.

따라서,

다음과 같은 묶음을 만들 수 있다.