오라클 계층쿼리 start with connect by로 계층 구조를 알아보자

계층 쿼리를 사용할 일이 있어서 공부해보았다.

 

계층 쿼리를 사용하면 순환 관계를 가진 데이터를 조회가 가능하다. 아래와 같은 구조로 표현 가능하다.

 

테스트 데이터는 오라클에서 제공하는 HR스키마의 EMPLOYEE테이블을 사용하였다.

 

노드는 부모와 자식으로 나눌수있고, LEVEL1노드 기준으로 부모가되고 LEVEL2노드는 자식이 된다.

또한 LEVEL3기준으로 LEVEL3은 부모가 되고 LEVEL4는 자식노드가 된다.

 

---

▶ 테이스 데이터

SELECT쿼리

SELECT B.EMPLOYEE_ID , B.MANAGER_ID
     , B.FIRST_NAME  , B.LAST_NAME, B.SALARY
  FROM EMPLOYEES B

출력결과

EMPLOYEE_ID|MANAGER_ID|FIRST_NAME |LAST_NAME  |SALARY|
-----------+----------+-----------+-----------+------+
        100|          |Steven     |King       | 24000|
        101|       100|Neena      |Kochhar    | 17000|
        102|       100|Lex        |De Haan    | 17000|
        103|       102|Alexander  |Hunold     |  9000|
        104|       103|Bruce      |Ernst      |  6000|
        105|       103|David      |Austin     |  4800|
        106|       103|Valli      |Pataballa  |  4800|
        107|       103|Diana      |Lorentz    |  4200|
        108|       101|Nancy      |Greenberg  | 12008|
생략...

건수가 많아서 생략하였다.

 

위와 같이 EMPLOYEE_ID가 PK로 존재하고, 해당EMPLOYEE_ID를 관리하는 MANAGER_ID가 존재하는 상황이다.

 

▶ 셀프조인으로 자식노드 출력

SELECT 쿼리

SELECT A.EMPLOYEE_ID "상위 관리자" , B.EMPLOYEE_ID 
     , B.FIRST_NAME , B.LAST_NAME, B.SALARY
  FROM EMPLOYEES  A
     , EMPLOYEES  B 
 WHERE A.EMPLOYEE_ID  = 101
   AND A.EMPLOYEE_ID = B.MANAGER_ID;

위와 같이 하나의 테이블로 셀프조인을 하였다. 출력결과는 아래와 같다.

 

출력결과

상위 관리자|EMPLOYEE_ID|FIRST_NAME|LAST_NAME|SALARY|
------+-----------+----------+---------+------+
   101|        108|Nancy     |Greenberg| 12008|
   101|        200|Jennifer  |Whalen   |  4400|
   101|        203|Susan     |Mavris   |  6500|
   101|        204|Hermann   |Baer     | 10000|
   101|        205|Shelley   |Higgins  | 12008|

A테이블 조건으로 EMPLOYEE_ID가 101 대상하나와 B테이블의 B.MANAGER_ID와 A.EMPLOYEE_ID가 101 인 대상을 조인처리 하였다. 그림으로는 위와 같다.

 

B테이블의 MANAGER_ID = 101 으로 조회한 결과는 위의 5건이 나왔다.

 

하지만 조금 깊은 레벨의 노드를 구하기 위해서는 셀프조인을 반복해야하는데, 오라클에서는 순환 관계를 가진 데이터를 계층 쿼리절 등 기능을 제공한다.

 

---

 

▶ 계층 쿼리 절

간단하게 알아봤다. START WITH로 시작하고 CONNECT BY로 연결처리한다.

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...

쿼리 참조

https://docs.snowflake.com/en/sql-reference/constructs/connect-by

 

직관적으로 확인 가능한 쿼리를 작성해봤다

SELECT LEVEL AS LV
     , LPAD(' ', LEVEL -1, ' ') || FIRST_NAME || LAST_NAME AS NAME
     , MANAGER_ID
     , EMPLOYEE_ID
     , PRIOR EMPLOYEE_ID AS "PRIOR EMPLOYEE_ID"
  FROM EMPLOYEES
 START WITH MANAGER_ID  IS NULL
 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID

MANAGER_ID가 NULL인 대상을 최상위 노드로 둔 후 MANAGER_ID와 EMPLOYEE_ID를 CONNECT BY처리 하였다.

PRIOR은 직전 노드의 값을 반환한다.

 

건수가 많아서 나머지는 생략하였다.

즉, PRIOR은 MANAGER_ID의 부모 노드인 EMPLOYEE(PRIOR)인 행을 읽어서 반복조회를 한다.

 

출력결과

LV|NAME               |MANAGER_ID|EMPLOYEE_ID|PRIOR EMPLOYEE_ID|
--+-------------------+----------+-----------+-----------------+
 1|StevenKing         |          |        100|                 |
 2| NeenaKochhar      |       100|        101|              100|
 3|  NancyGreenberg   |       101|        108|              101|
 4|   DanielFaviet    |       108|        109|              108|
 4|   JohnChen        |       108|        110|              108|
 4|   IsmaelSciarra   |       108|        111|              108|
 4|   Jose ManuelUrman|       108|        112|              108|
 4|   LuisPopp        |       108|        113|              108|
 3|  JenniferWhalen   |       101|        200|              101|
 3|  SusanMavris      |       101|        203|              101|
 3|  HermannBaer      |       101|        204|              101|
 3|  ShelleyHiggins   |       101|        205|              101|
 4|   WilliamGietz    |       205|        206|              205|
 2| LexDe Haan        |       100|        102|              100|
 3|  AlexanderHunold  |       102|        103|              102|
 4|   BruceErnst      |       103|        104|              103|
 4|   DavidAustin     |       103|        105|              103|

출력결과다 건수가 많아서 나머지는 생략했다.

 

쿼리 및 내용 참조 출처 : 불친절한SQL 서적