이펙티브 오라클 (2009년)
SQL 바로 알기 0 0 51,357

by 구루비스터디 ROWNUM 해석함수 [2018.05.26]


  1. 1. ROWNUM 유사열 (ROWNUM PSEUDO COLUMNS)
    1. ROWNUM 사용용도
  2. 2. 스칼라 하위쿼리
    1. 2.1 외부 조인의 필요성 제거
    2. 2.2 단일 쿼리를 이용하여 다수의 테이블로부터 정보집계
    3. 2.3 단일 쿼리를 이용하여 다른 테이블/행으로부터 선택적으로 선택
  3. 3. 해석함수
    1. 예제)
    2. 3.1 파티션에서 특정 행 찾기
    3. 3.2 그룹의 상위 N
    4. 3.3 전치(피보팅)
    5. 3.4 이전 행/다음 행


1. ROWNUM 유사열 (ROWNUM PSEUDO COLUMNS)

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


2. 스칼라 하위쿼리

2.1 외부 조인의 필요성 제거

하나 이상의 열을 필요로 할 경우

  • 외부조인으로 돌아간다.
  • 하나 이상의 스칼라하위쿼리를 사용한다.
  • 단일 스칼라 하위쿼리를 이용한 트릭을 쓴다.('||' 사용 등)
  • 객체유형(type)을 사용한다. (사용안해봄)


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

인라인 뷰 구현

INLINE_VIEW.LOG


스칼라 서브 쿼리 구현

Scalar_SUB.LOG


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;



  • ROW_NUMBER 및 RANK, DENSE_RANK 는 그룹을 파티션으로 나누고, 해당 파티션에서 상위N개를 얻고 싶을 때에만 사용한다.
  • 전체 집합에서 상위 N개를 구하고자 할때는 ROWNUM을 사용한다( ROWNUM의 최적화, p664 ROWNUM을 이용한 상위 N개의 쿼리 처리 참조 )


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
FLAGDATE
FLAG120091114
FLAG120091116
FLAG120091122
FLAG120091124
FLAG120091128
FLAG120091202
FLAG220091117
FLAG220091119
FLAG220091123
FLAG220091125
FLAG220091127
FLAG220091201
FLAG220091203



SELECT FLAG,
DT,
LAG(DT) OVER (PARTITION BY FLAG ORDER BY DT) BF_DT,
LEAD(DT) OVER (PARTITION BY FLAG ORDER BY DT) AF_DT
FROM STUDY_TEST;

"구루비 데이터베이스 스터디모임" 에서 2009년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3496

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입