30. 계층형 질의, 그룹 함수

[ 계층형 질의 ]  
- 한 테이블 내 각 행끼리 상하 관계를 갖는 경우 그 depth(level)를 표현하는 방법
 ex) 부서에 대한 상하관계 존재할 경우 부서명 들여쓰기

** 문법
select 
  from 
 where
 start with 시작조건
connect by 연결조건;


예제) 사장실부터 시작해서 하위부서 연결
정상)
select d.*, level 
  from dept2 d
 start with PDEPT is null
connect by pdept = prior dcode;

 

잘못된 쿼리)
select d.*
  from dept2 d
 start with PDEPT is null
connect by  prior pdept =dcode; 

 
예제) department 테이블 내 있는 학과끼리의 상하관계를 표현
단, 공과대학과 인문대학이 root node가 되도록
select d.*, level,
       lpad(' ',(level-1)*4,' ')||dname
  from department d
 start with part is null
connect by prior deptno = part;

 

 

 

 

예제) dept2 테이블을 사용하여 H/W지원 팀으로부터의 상위부서를 출력
단, H/W지원팀이 root node가 되도록
select d.*, level,
       lpad(' ',(level-1)*4,' ')||DNAME as 부서연결
  from dept2 d
 start with PDEPT is null
connect by prior DCODE = PDEPT;

select d.*, level
  from dept2 d
 start with dcode = '1004'
connect by dcode = prior pdept;

** 중요 : where / connect by 차이점
파싱순서 : start with -> connect by -> where

문제) 다음 SQL 실행 결과로 가장 적절한 것은?
 select d.*, level
   from dept2 d
  where area = '서울지사'  
  start with pdept is null
connect by pdept = prior dcode;

문제) 다음 SQL 실행 결과로 가장 적절한 것은?
 select d.*, level
   from dept2 d
  start with pdept is null
connect by pdept = prior dcode and area = '서울지사';

 

 

위의 두 문제의 차이를 잘 구별할 줄 알아야 한다.

** 계층형 질의 추가 함수 / 가상컬럼
1) 가상 컬럼
- level : 트리구조로 표현했을 경우의 각 단계 표현한 숫자(depth)
- connect_by_isleaf : leaf node 여부
- connect_by_iscycle : 순환구조 시작 여부(순환구조 시작지점이 1, 나머지 0)

select dname, 
       lpad(' ',(level-1)*2,' ')||dname as "부서명---------",
       level,
       connect_by_isleaf
  from dept2 d
 start with pdept is null
connect by prior dcode = pdept;

2) 가상 함수
- connect_by_root 컬럼명 : root node의 특정 컬럼값 출력
- sys_connect_by_path(컬럼, 구분자) : root node부터 현재 node 까지의 모든 연결과정 출력
- order siblings by 컬럼

select dname, 
       lpad(' ',(level-1)*2,' ')||dname as "부서명---------",
       connect_by_root dname,
       sys_connect_by_path(dname,'-')
  from dept2 d
 start with pdept is null
connect by prior dcode = pdept
  order siblings by dname desc;

** nocycle 옵션
- connect by에 사용하는 옵션
- 순환구조를 깨는 옵션

예제) 순환구조를 갖는 경우의 계층형 질의 출력 결과
create table dept3
as
select * from dept2 where dname = '사장실';

insert into dept3 values('0002','경영지원부','0001','서울');
update dept3 set pdept = '0002' where dcode = '0001';
commit;

 

select *
  from dept3
 start with dcode = '0001'
connect by pdept = prior dcode;   -- 에러발생(connect by의 루프가 발생되었습니다)

 

select d.*, connect_by_iscycle
  from dept3 d
 start with dcode = '0001'
connect by nocycle pdept = prior dcode;   -- 정상수행

[ 그룹 함수 ]
- 소계 출력 함수(집합을 정의하는 함수)
- group by절에 전달

1. grouping sets(A,B) : A, B
 - 나열 순서 중요 X
 - 전체 소계 출력 X

2. rollup(A,B) : A, (A,B), () 
 - 나열 순서 중요
 - 전체 소계 출력

3. cube sets(A,B) : A, B, (A,B), ()   
 - 나열 순서 중요 X
 - 전체 소계 출력


예) group by grouping sets(deptno, job) -> deptno별 그룹연산 결과 + job별 그룹연산 결과 
예) group by rollup(deptno, job) -> deptno별 결과 + deptno + job별 그룹연산 결과 + 전체 소계


예제) 부서별 급여 총합 + 업무별 급여 총합
union all)
select deptno, null as job, sum(sal) as 급여총합
  from emp
 group by deptno
 union all 
select null, job, sum(sal)
  from emp
 group by job;  

 

 

grouping sets)
select deptno, job, sum(sal) as 급여총합
  from emp
 group by grouping sets(deptno, job);
 

 

 

아래 grouping sets으로 푸는 방식이 쿼리가 훨씬 간단하게 만들어진다.
 
예제) 부서별 급여 총합 + 업무별 급여 총합 + 전체 급여 총합
sol1 - union all)
select deptno, null as job, sum(sal) as 급여총합
  from emp
 group by deptno
 union all 
select null, job, sum(sal)
  from emp
 group by job
 union all 
select null, null, sum(sal)
  from emp;  

sol2 - grouping sets)
select deptno, job, sum(sal) as 급여총합
  from emp
 group by grouping sets(deptno, job, ());

select deptno, job, sum(sal) as 급여총합
  from emp
 group by grouping sets(deptno, job, null);

   
예제) emp에서 deptno별, deptno + job별, 전체 급여 총합 출력

sol1) union all 이용
select deptno, null as job, sum(sal) as sumsal
  from emp
 group by deptno
 union all
select deptno, job, sum(sal)
  from emp
 group by deptno, job
 union all
select null, null, sum(sal)
  from emp;

sol2) rollup 이용
select deptno, job, sum(sal) as sumsal
  from emp
 group by rollup(deptno,job);

 

예제) deptno별, job별, deptno+job별, 전체 급여 총 합
union all)
select deptno, null as job, sum(sal) as sumsal
  from emp
 group by deptno
 union all
select null, job, sum(sal) as sumsal
  from emp
 group by job
 union all
select deptno, job, sum(sal)
  from emp
 group by deptno, job
 union all
select null, null, sum(sal)
  from emp;
  
cube)
select deptno, job, sum(sal) as sumsal
  from emp
 group by cube(deptno,job);


grouping sets)
select deptno, job, sum(sal) as sumsal
  from emp
 group by grouping sets(deptno,job,(deptno,job),()); 
 

 세 방식으로 해도 같은 결과를 얻을 수 있다. cube가 가장 간단하게 표현할 수 있다.