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
groupby 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
groupby deptno;
3.4 이전 행/다음 행
LAG : 결과집합의 앞을 본다.
LEAD : 결과집합의 뒤를 본다.
예제)다음 테이블에서 FLAG의 날짜 별로 가장 최근 이전/이후 일자를 구하여라.
FLAG, DT, BF_DT, AF_DT
FLAG
DATE
FLAG1
20091114
FLAG1
20091116
FLAG1
20091122
FLAG1
20091124
FLAG1
20091128
FLAG1
20091202
FLAG2
20091117
FLAG2
20091119
FLAG2
20091123
FLAG2
20091125
FLAG2
20091127
FLAG2
20091201
FLAG2
20091203
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;
