on
[Oracle DB] SQL 기본 - 계층형 질의
[Oracle DB] SQL 기본 - 계층형 질의
계층형 질의
테이블에 계층형 데이터 가 존재하는 경우, 데이터를 조회하기 위해 계층형 질의(Hierarchical Query)를 사용한다.
여기서의 계층형 데이터란, 동일 테이블에 계층적으로 상위 및 하위로 구분되는 데이터를 의미한다.
오라클의 계층형 SQL
START WITH (조건) 계층구조 전개의 시작위치를 지정 하는 구문.
즉, 루트데이터를 지정 한다. CONNECT BY [NO CYCLE][PRIOR] A AND B CONNECT BY 절은 다음에 전개될 자식데이터를 지정하는 구문이다.
1. PRIOR 자식=부모 형태를 사용하면, 계층구조에서 순방향 전개 (자식→부모) 를 한다.
2. PRIOR 부모=자식 형태를 사용하면, 계층구조에서 역방향 전개 (부모→자식) 를 한다.
NO CYCLE을 추가하면, 사이클이 발생한 이후의 데이터는 전개하지 않는다. ORDER SIBLINGS BY (칼럼) 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
(동일 레벨 간 소팅 적용)
계층형 질의의 가상 칼럼
LEVEL 루트 데이터라면 1, 하위데이터가 존재할 때마다 1씩 증가한다. CONNECT_BY_ISLEAF 전개과정에서 해당 데이터가 리프데이터면 1, 그렇지 않으면 0 CONNECT_BY_ISCYCLE 전개과정에서 자식을 갖는데 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0
⭐ 계층형 질의의 완벽한 이해를 위한 간단한 실습
가장 먼저, 다음과 같은 테이블이 있다고 생각해보자.
소대원 테이블
그리고, 이러한 소대 테이블에 다음과 같은 소대원들의 데이터가 저장되어있다.
소대원들의 정보(군번은 실제 군번이 아닌 1부터 9까지의 정수로 지정)
데이터를 확인해보면, 현재 해당 소대는 직속상급자군번이 NULL인 소대장이 계층의 가장 상위에 위치해 있고, 그 밑으로는 분대장 1명과 분대원 다수로 구성된 분대 2개가 있음을 알 수 있다.
즉, 위 소대는 다음과 같은 계층 조직을 가진다.
소대원 테이블에 저장된 데이터의 계층도
이제 계층형 쿼리를 사용 하여 이러한 계층형 데이터를 확인해본다.
아래의 내용은 계층형 쿼리와 해당 쿼리에 대한 결과이다.
계층형 쿼리 계층형 쿼리에 대한 결과
이제부터 쿼리를 하나하나 자세히 살펴보고 이해해보자.
먼저 SELECT 절에서는 LEVEL 칼럼, 소대 계층을 보여주는 칼럼, 그리고 CONNECT_BY_ISLEAF 칼럼이 존재한다.
이 중 가상 칼럼들을 살펴보면, 먼저 LEVEL 칼럼에서 데이터의 레벨을 보여준다.
루트 데이터는 LEVEL 값이 1인데, 이는 쿼리 뒷부분에서 루트데이터를 소대장으로 지정 했기 때문이다. (잠시 뒤에 살펴본다)
이후 하위 데이터가 존재할 때마다 LEVEL 값은 1씩 증가한다.
소대장 하위의 분대장의 LEVEL이 2, 분대장 하위의 분대원의 LEVEL이 3인 것을 확인할 수 있다.
그 다음은 CONNECT_BY_ISLEAF 칼럼이다.
CONNECT_BY_ISLEAF 칼럼은 전개과정에서 해당 데이터가 리프데이터면 1, 그렇지 않으면 0을 반환한다.
마찬가지로, 최하위의 계층에 있는 분대원들의 CONNECT_BY_ISLEAF 칼럼값이 1임을 확인할 수 있다.
다음에 살펴볼 것은 START WITH 절이다.
START WITH 절은 계층구조 전개의 시작위치를 지정한다. 즉, 루트데이터(LEVEL 1)를 지정하는 것이다.
START WITH 절은 뒤에 조건이 나와야하는데, 쿼리를 다시금 살펴보면 직속상급자군번의 값이 NULL인 행 즉, 소대장을 루트데이터로 지정 하고 있다.
START WITH 절로 루트데이터를 지정했다면, 이제부턴 이러한 계층을 연결 해야한다.
계층구조를 보면, 각 계층(레벨)에서의 직속상관군번은 상위 레벨의 군번이다.
다시 말해, 상위 계층의 군번은 현재 계층의 직속상관군번인 것이다.
이런 방식으로 거슬러 올라가면, 최상위 계층에 속해있는 소대장의 상위 계층 군번은 존재하지 않게된다.
이를 나타낸 것이 START WITH 절 다음에 위치한 CONNECT 절이다.
CONNECT BY PRIOR 군번 = 직속상관군번
from http://neolbeum.tistory.com/67 by ccl(A) rewrite - 2021-08-28 20:26:19