Oracle Advanced SQL 강좌
계층구조 쿼리(Hierarchical Queries)란? 45 24 99,999+

by 구루비 START WITH CONNECT BY PRIOR LEVEL 계층구조쿼리 상관관계 계층구조 NOCYCLE ORDER SIBLINGS BY [2002.04.21]


계층구조 쿼리란?

오라클 데이터베이스 scott 유저의 emp 테이블을 보면 empno와 mgr컬럼이 있으며, mgr 컬럼 데이터는 해당 사원의 관리자의 empno를 의미 한다.

예를 들어서 아래의 데이터를 보면

 EMPNO   ENAME    SAL    MGR
 ------ ------- ------ ------
   7369  SMITH     800   7902
   7902  FORD     3000   7566
  • - empno 7369사원의 관리자는 7902의 empno를 가진 사원이며
  • - empno 7902사원의 관리자는 7566의 empno를 가진 사원이다.

이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITHCONNECT BY를 이용해서 쉽게 조회 할 수 있다.

계층구조 쿼리 Synctax

START WITH
  • - 계층 질의의 루트(부모행)로 사용될 행을 지정 한다.
  • - 서브쿼리를 사용할 수도 있다.

CONNECT BY
  • - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 할 수 있다.
  • - PRIOR 연산자와 함께 사용하여 계층구조로 표현할 수 있다.
  • - CONNECT BY PRIOR 자식컬럼 = 부모컬럼 : 부모에서 자식으로 트리구성 (Top Down)
  • - CONNECT BY PRIOR 부모컬럼 = 자식컬럼 : 자식에서 부모로 트리 구성 (Bottom Up)
  • - CONNECT BY NOCYCLE PRIOR : NOCYCLE 파라미터를 이용하여 무한루프 방지
  • - 서브쿼리를 사용할 수 없다.

LEVEL Pseudocolumn
  • - LEVEL은 계층구조 쿼리에서 수행결과의 Depth를 표현하는 의사컬럼이다.

ORDER SIBLINGS BY
  • - ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

CONNECT BY의 실행순서는 다음과 같다.
  • - 첫째 START WITH
  • - 둘째 CONNECT BY
  • - 세째 WHERE 절 순서로 풀리게 되어있다.

계층구조 쿼리 예제

간단예제

아래는 직업이 PRESIDENT을 기준으로 계층 구조로 조회하는 예이다.

-- LEVEL컬럼으로 depth를 알수 있다. 
-- JONES의 관리자는 KING 이며, SCOTT의 관리자는 JONES 이다. 
-- 상/하의 계층 구조를 쉽게 조회 할 수 있다. 
SELECT LEVEL, empno, ename, mgr
  FROM emp
 START WITH job = 'PRESIDENT' 
CONNECT BY PRIOR  empno = mgr;


LEVEL      EMPNO  ENAME        MGR
------ -------- --------    -------
     1       7839   KING
     2       7566   JONES      7839
     3       7788   SCOTT      7566 
     4       7876   ADAMS      7788 
     3       7902   FORD       7566
     4       7369   SMITH      7902
...
PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서 상하위간의 관계를 기술할때 사용.

LEVEL의 활용

LEVEL Pseudocolumn을 이용하면 계층구조 쿼리를 좀 더 다양하게 활용 할 수 있다.

아래는 LEVEL의 배율만큼 공백을 왼쪽에 추가하여 계층구조를 한눈에 볼 수 있게 표현한 예이다.

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL ename FORMAT A20;


-- 왼쪽에 LEVEL만큼 공백을 추가하여 계층구조로 조회하는 예제
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job 
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr; 


LEVEL ENAME                     EMPNO    MGR    JOB
------ --------------------    -------  -----  --------
     1 KING                       7839         PRESIDEN
     2     JONES                  7566   7839  MANAGER
     3         SCOTT              7788   7566  ANALYST
     4             ADAMS          7876   7788  CLERK
     3         FORD               7902   7566  ANALYST
     4             SMITH          7369   7902  CLERK
     2     BLAKE                  7698   7839  MANAGER
...

아래는 LEVEL별로 급여 합계와 사원수를 조회하는 예제이다.

-- LEVEL별로 급여 합계와 사원수를 조회하는 예제
SELECT LEVEL, AVG(sal) total, COUNT(empno) cnt
  FROM emp
 START WITH job='PRESIDENT' 
CONNECT BY PRIOR empno=mgr      
 GROUP BY LEVEL 
 ORDER BY LEVEL;

LEVEL      TOTAL        CNT
-------- ---------- ----------
       1       5000          1
       2       8275          3
       3      13850          8
       4       1900          2

PRIOR의 활용

PRIOR연산자를 SELECT 절에서 사용해보자.

아래는 사원의 관리자를 PRIOR연산자를 이용해서 조회하는 예제이다.

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL mgrname FORMAT A10;


-- SELECT절에 "PRIOR ename mgrname"을 확인해 보자
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, 
       PRIOR ename mgrname,
       empno, mgr, job 
  FROM emp
 START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr; 


  LEVEL ENAME                MGRNAME         EMPNO        MGR JOB
------- -------------------- ---------- ---------- ---------- ---------
      1 KING                                  7839            PRESIDENT
      2     JONES            KING             7566       7839 MANAGER
      3         SCOTT        JONES            7788       7566 ANALYST
      4             ADAMS    SCOTT            7876       7788 CLERK
      3         FORD         JONES            7902       7566 ANALYST
      2     BLAKE            KING             7698       7839 MANAGER
      3         MARTIN       BLAKE            7654       7698 SALESMAN
      3         TURNER       BLAKE            7844       7698 SALESMAN
      3         JAMES        BLAKE            7900       7698 CLERK
      2     CLARK            KING             7782       7839 MANAGER
      3         MILLER       CLARK            7934       7782 CLERK

PRIOR 연산자 : 상위행의 컬럼임을 나타낸다. CONNECT BY 절에서만 사용할 수 있는 것은 아니다.

Bottom Up 조회 예제

위 간단 예제를 역순(자식에서 부모로 트리 구성, Bottom Up)으로 조회 해 보자

-- SQL*Plus에서만 깔끔하게 보기위해서 
COL ename FORMAT A20;


-- ename을 기준으로 Bottom Up으로 조회하는 예제이다.
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job 
  FROM emp
 START WITH ename='SMITH'  -- 최 하위 노드 값이 와야 한다. 
CONNECT BY PRIOR mgr = empno; 


LEVEL ENAME                EMPNO      MGR    JOB
------ ---------------    -------- -------- ---------
     1 SMITH                 7369     7902   CLERK
     2     FORD              7902     7566   ANALYST
     3         JONES         7566     7839   MANAGER
     4             KING      7839            PRESIDENT

PRIOR 컬럼에 따라(상위 or 하위) 계층전개 방향이 달라진다.
  • - 순방향(Top Down-상위~하위) : PRIOR 하위 = 상위
  • - 역방향(Bottom Up-하위~상위) : PRIOR 상위 = 하위

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

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

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

by saki [2005.03.14 13:51:20]
정말 도움 많이 되겠습니다.. 제 블로그로 퍼가도 될지 모르겠습니다.
http://blog.naver.com/wdwcjswo.do
좋은하루 보내세요 ^^

by 히보기 [2005.04.05 22:40:20]
덕분에 메뉴작업이 한결쉬워졌습니다. ^^

by kwaknu [2005.10.24 17:52:33]
정말 도움이 많이 되었습니다. connect by 를 잘 몰랐는데 이 글로 많이 배웠습니다.
퍼가도 되겠습니까??

http://blog.naver.com/kwaknu.do

by xoqkf [2006.03.29 18:05:52]
감솨~~~^^

by ima [2007.01.25 14:56:37]
감사합니다. 퍼갈께요
http://blog.naver.com/dlak2582.do

by BULGO [2007.08.04 21:06:48]
감사합니다.퍼갈게요...blog.naver.com/bulgo77

by streem [2007.08.27 15:42:03]
좋은정보 감사합니다. 계층 게시판 만들때 복잡스러운 쿼리문이 한결 간단해 지겠군요. 퍼갈께요. blog.naver.com/streem7

by md02 [2007.10.27 12:51:27]
감사합니다

by 곽태영 [2008.03.13 15:15:01]
이런식으로 깔끔하게도 사용할 수 있죠.

select substr(max(sys_connect_by_path( category_name, ' > ' )), +4)
from
(
select manage_number, category_name, rownum rnum
from temp_table2
order by manage_number
)
start with rnum = 1
connect by prior rnum = rnum-1

by 지나가다 [2008.09.09 10:43:50]
잘 정리되었네요 ^^ 잘 보고 갑니다.

by karl [2009.09.29 20:58:58]
^^ 너무 잘 읽었습니다. 감사해요.

by 안경곰 [2010.08.12 13:19:08]
도움 많이 됬습니다. ^^ 퍼갈게요 ~

by 정존 [2010.12.15 12:21:12]
좋은 자료 감사합니다. ㅅㅅ

by 이광택 [2011.10.17 13:45:56]
계층구조에서 막혀 있었는데 감사합니다

by 손님 [2013.05.23 10:20:37]
안녕하세요 
이제 갓 프로그래머로써 첫 발을 내딛었네요 
계층구조에 대한 개념이 잘 안 섰는데 이 글 읽고 많이 도움이 되었습니다.
제 블로그에 퍼갈게요^^

by 정존 [2013.07.12 13:23:54]
이 부분은 공부할게 많네요 ㅎㅎ

by 박민철 [2014.08.28 17:29:51]

유용한 정보를 깔끔하게 정리해주셨네요^^

감사 감사

 


by 정진우 [2014.10.27 10:15:04]

정말 감사합니다 ... 매번 ..^^


by 비비엘소프트 [2016.03.30 09:31:44]

<오탈자 정정건의>

계층구조 쿼리 Synctax -> 계층구조 쿼리 Syntax


by 므흣 [2016.05.02 22:39:20]

공부할게 많네요~~~


by 데빌디벨롭퍼 [2016.11.17 15:15:04]

감사합니다! 퍼갈게요~


by 초코초코 [2018.10.04 17:44:34]

2002년 게시물이 여전히 유용하네요! 출처 밝히고 퍼갈게요~


by 홍길덩이 [2019.01.14 17:14:29]

많은 도움이 됩니다!

급여의 합계라면은...

아래의 함수 AVG -> SUM 으로 바꿔야 되지 않을까 생각됩니다..

 

 

-- LEVEL별로 급여 합계와 사원수를 조회하는 예제

SELECT LEVEL, AVG(sal) total, COUNT(empno) cnt

  FROM emp

 START WITH job='PRESIDENT'

CONNECT BY PRIOR empno=mgr     

 GROUP BY LEVEL

 ORDER BY LEVEL;


by 김태민 [2019.08.09 16:43:50]

감사합니다 ! 

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