1. ROWNUM 유사열 (ROWNUM PSEUDO COLUMNS)

ROWNUM 사용용도

• 쿼리를 튜닝한다.
• 쿼리를 통해 페이지를 분할한다.
• 상위 N개의 처리를 수행한다.
• 참고

2. 스칼라 하위쿼리

2.1 외부 조인의 필요성 제거

2.2 단일 쿼리를 이용하여 다수의 테이블로부터 정보집계

2.3 단일 쿼리를 이용하여 다른 테이블/행으로부터 선택적으로 선택

• 행을 테이블 집합에 조인하기
• 조회하기 (SQLLDR과 함께 데이터 조회를 수행함)

3. 해석함수

• 집합지향 SQL에게 결과집합에 대해 어느 정도까지 배열의미론을 사용하게 해준다.
• 행을 그룹으로 묶지 않고도 같은 작업을 수행하게 해준다.

예제)

select deptno,
       ename,
       sal,
       sum(sal) over (partition by deptno order by sal) CumDeptTot,
       sum(sal) over (partition by deptno) SalByDept,
       sum(sal) over (order by deptno, sal) CumTot,
       sum(sal) over () TotSal
  from emp
order by deptno,sal;

• sum(sal) over (partition by deptno order by sal) : 부서별로 낮은 급여 순으로 정렬
• sum(sal) over (partition by deptno) : 부서별 급여합계
• sum(sal) over (order by deptno, sal) : 부서별, 급여별 조회 하면서 급여의 누적
• SUM(SAL) OVER () : 총계

3.1 파티션에서 특정 행 찾기

특정 컬럼의 최신 행을 뽑아라.

-- 일반적 쿼리
select * 
  from big_table t1
 where last_ddl_time = ( select max(last_ddl_time) 
                           from big_table t2
		          where t2.owner = t1.owner )

• => 대량의 데이터일 경우 상호연관된 하위쿼리의 반복으로 인하여 비효율 발생.

-- 조금 나아진 쿼리
select * 
  from big_table t1, 
     ( select owner, max(last_ddl_time) max_time 
         from big_table 
	group by owner ) t2 
 where t1.owner = t2.owner
   and t1.last_ddl_time = t2.max_time

• => 조금 나아지긴 했으나 t1테이블에 대해서 두번 전체스캔을 수행하여 비효율 발생

-- 해석함수를 사용
select owner, last_ddl_time, object_name, object_type
  from 
       ( select t1.*,
                max(last_ddl_time) over (partition by owner) max_time
           from big_table t1 
       )
 where last_ddl_time = max_time;

• => t1테이블을 한번만 전체 스캔하면서 최대last_ddl_time을 계산함.

3.2 그룹의 상위 N

• ROW_NUMBER : 정렬 후에 파티션의 모든 행에 고유한 일련의 수를 할당.
• RANK : 파티션의 모든 행에 순위를 부여. 같은 값을 가진 행에는 동일한 순위가 부여됨. 순위사이에는 공백이 있을 수 있음.
• DENSE_RANK : RANK와 같이 파티션의 모든 행에 순위를 부여. 순위는 연속적이어서 순위사이에 공백이 있을 수 없음.

set linesize 2000
break on deptno skip 1
select deptno,
        ename,
	sal,
	row_number() over (partition by deptno order by sal desc) rn,
	rank() over (partition by deptno order by sal desc) rank,
	dense_rank() over (partition by deptno order by sal desc) dense_rank
   from emp
  order by deptno, sal desc;

3.3 전치(피보팅)

부서별로 상위 세개의 봉급을 가로로 나열하라

• DEPTNO, SAL1, SAL2, SAL3

1단계)

select deptno,
	sal,
	dense_rank() over (partition by deptno order by sal desc) dense_rank
   from emp;

2단계)

-- 인라인뷰를 사용한 피벗적용
select deptno, 
       decode( dense_rank,1, sal ) sal1,
       decode( dense_rank,2, sal ) sal2,
       decode( dense_rank,3, sal ) sal3
  from 
       ( select deptno,
		sal,
		dense_rank() over (partition by deptno order by sal desc) dense_rank
	   from emp
       )
 where dense_rank < 3;

3단계)

-- NULL 제거
select deptno, 
       max(decode( dense_rank,1, sal )) sal1,
       max(decode( dense_rank,2, sal )) sal2,
       max(decode( dense_rank,3, sal )) sal3
  from 
       ( select deptno,
		sal,
		dense_rank() over (partition by deptno order by sal desc) dense_rank
	   from emp
       )
 where dense_rank < 3
 group by deptno;

3.4 이전 행/다음 행

• LAG : 결과집합의 앞을 본다.
• LEAD : 결과집합의 뒤를 본다.

예제)다음 테이블에서 FLAG의 날짜 별로 가장 최근 이전/이후 일자를 구하여라.

• FLAG, DT, BF_DT, AF_DT