SQL 작성시 계층형 데이터 표현

계층형 데이터 표현이란 ?

간단하게 설명하자면 상위 구조가 있고 하위구조가 존재할때에 그 구조를 표현하는것

이게 더 어려울수 있으니 예를 들자면

회사로 따지면 사장이 존재하고 부사장이 존재하고 각 부서의 짱들이 존재할텐데 그걸 나타내보면

사장

      부사장

                  총무부짱

                                총무부차장

                                                 총무부과장

                                                                  총무부대리

                                                                                   사원1

                                                                                    사원2 

뭐 이런식으로 쿼리 결과를 표현하는것을 계층형 데이터 표현이라고 말할수 있다.

우리가 사용하는 간단한 emp table을 조사해 보면 . . .

empno 컬럼이 PK로 설정이 되어있고 mgr 컬럼은 FK컬럼으로 설정이 되면서 empno컬럼의 값을 참조하게 된다.

이런 계층형 구조를 표현하고자 할때 사용하는것이 level 이라는 의사열을 사용한다.

계층이 하나씩 들어갈때마다 level 값이 0 부터 하나씩 증가를 한다.

그럼 이런 계층구조를 나타내기 위해서 어떻게 해야 하나 ?

Connect by [PRIOR]  컬럼1 = [PRIOR]  컬럼2 

START WITH  조건 

이 부분을 추가한다 . 

PRIOR라는 키워드가 컬럼 1 쪽에 붙으면 전위형( root - left - right ) 형태가 된다.

즉 PRIOR를 왼쪽에 붙이면서 컬럼1 값에 PK컬럼값을 주면된다 .(부모에서 자식으로 검색)

PRIOR라는 키워드가 컬럼2 쪽에 붙으면 후위형( left - right - root )형대가 된다.

이때는 자식에서 부모로 값이 출력되는 형태가 되는것이다.

그 다음 START WITH 조건 부분은 LEVEL을 어디부터 적용할것인지를 나타내게 된다.

간단하게 예를 들어보면

SELECT ( '  ' , LEVEL * 5 ) || ename

FROM     emp

CONNECT  BY   PRIOR  empno = mgr

START  WITH mgr  IS NULL ;

위에껄 풀어보자면 empno 와 mgr이 같은것을 찾아서 뿌려주되 시작 점은 mgr 이 NULL이 되는것부터 LEVEL을 적용시키라 하는뜻이다 . 

즉 mgr 이 NULL이 되는것이 Root가 되는것이고  ( '  ' , LEVEL * 5 ) || ename 이라는것은 레벨에 맞게끔 앞에 공백을 다섯개씩 붙인후에 이름을 출력하자는것이다.

=========   결과를 보면  ============

RPAD('',LEVEL*5)||ENAME

--------------------------------------------------------------------------------

     KING

              JONES

                        SCOTT

                                    ADAMS

                         FORD

                                    SMITH

               BLAKE

                         ALLEN

                         WARD

                         MARTIN

                         TURNER

                         JAMES

                CLARK

                         MILLER

14 개의 행이 선택되었습니다.

요런식의 결과가 나온다 .

그럼 PRIOR를 오른쪽에 붙여 본다면 . . .

SELECT   RPAD( ' ', LEVEL * 5 ) || ename

FROM      emp

CONNECT BY  empno =  PRIOR mgr

START WITH ename =  'ADAMS' ;

즉 시작위치가 자식부터 검색해 나가는것이니깐 . . .

아담스라는 젤 하위 레벨부터 주면 그 레벨에 해당하는 상의값을 얻을수 있다.

============  결과를 보면 =============

RPAD('',LEVEL*5)||ENAME

-------------------------------------

     ADAMS

                  SCOTT

                              JONES

                                          KING

이런식의 결과를 얻을수가 있게 된다 .

- 응용

SCOTT가 관리하는 사원을 트리 구조로 보고자 한다면 어떻게 하면 될까 ?

위에서 아래로 검색하는것이므로 . . .

PRIOR가 왼쪽 즉 PK컬럼에 붙으면 되는것이고 젤 위 레벨을 SCOTT로 주면 되겠져 ?

SELECT  RPAD( ' ', LEVEL * 5 ) || ename

FROM      emp

CONNECT BY  PRIOR empno =   mgr

START WITH ename =  'SCOTT' ;

======== 결과 =========

RPAD('',LEVEL*5)||ENAME

--------------------------------

     SCOTT

                  ADAMS

자 그럼 각 레벨별로 사람이 몇명인지 알아보고자 한다면 ?

SELECT  Level  , count (*)

FROM     emp

CONNECT  BY   PRIOR  empno = mgr

START  WITH   mgr IS NULL 

GROUP BY  level ;

===========  결과를보면 ============

     LEVEL   COUNT(*)

---------- ----------

           1          1

           2          3

           3          8

           4          2

각 레벨별로 몇명이 존재하는지 알 수 있 다.