계층형 쿼리란?
상하 관계를 바탕으로 트리 형태의 구조를 구현하기 위해서 사용 되는 것으로
우리가 흔히 프로그램에서 + 버튼을 눌러서 트리 메뉴를 펼치거나 접거나 할때 사용되는 쿼리 입니다
예) 조직도, 메뉴, BOM, 카테고리 등등 ~
다양한 DB 에서 지원하고 있으며, 오라클에서는 START WITH ~ CONNECT BY~ 형태로 사용합니다.
기본 개념 (문법의 이해)
1. START WITH ~
데이터를 연결할때 가장 최상위에 들어갈 데이터를 선택 합니다.
조직도라면 경우 회사를 시작할 것이고, 메뉴의 경우에는 별도의 'ZERO' 형태로 관리 할것입니다.
2. CONNECTED BY ~
최상위 데이터는 위에서 정했고, 이제 상위(부모), 하위(자식)의 데이터를 무었을 기준으로 결합할것이 지정해야 합니다.
정리 하자면, 현제 데이터의 상위가 누구인지 지정해주면 됩니다. (데이터를 파도타기 하는 셈이죠)
요약하자면, SELF_ID(자신의 값ID), PARENT_ID(부모의 값ID), 그리고 표현할 데이터 총 3개만 있으면 계층형 쿼리는 만들수 있다.
샘플데이터
겔럭시의 부품 구성(예시)
휴대폰을 예시로 한번 작성 해보려고 합니다. 겔럭시를 구성하기 위한 임의의 부품 목록표 입니다.
겔럭시를 만들기 위해서 크게, 메인보드, 카메라, 케이스 가 필요 하고,
메인보드를 만들기 위해서 CPU, 메모리, 센서가 필요,
그리고 센서에는 온도센서와, 자이로센서가 있다고 가정 해봅시다.
| 겔럭시 |
| 메인보드 |
| CPU |
| 메모리 |
| 센서 |
| 온도센서 |
| 자이로센서 |
| 카메라 |
| 케이스 |
샘플 테이블 구성 (표현값, 본인ID, 상위ID)
WITH T AS
(
SELECT '겔럭시' NM, '100' SELF_ID, '' PARENT_ID FROM DUAL
UNION ALL SELECT '카메라', '300', '100' FROM DUAL
UNION ALL SELECT '케이스', '400', '100' FROM DUAL
UNION ALL SELECT '메인보드', '200', '100' FROM DUAL
UNION ALL SELECT 'CPU', '410', '200' FROM DUAL
UNION ALL SELECT '메모리', '420', '200' FROM DUAL
UNION ALL SELECT '센서', '430', '200' FROM DUAL
UNION ALL SELECT '온도센서', '431', '430' FROM DUAL
UNION ALL SELECT '자이로센서', '432', '430' FROM DUAL
)쿼리 사용 및 상세 설명
상기 샘플 테이블에서는 표현값(NM), 본인ID(SELF_ID), 상위ID(PARENT_ID) 으로 이름을 지정했습니다.
1. START WITH
시작해야 할 데이터는? 당연히 '겔럭시' 이겠죠. 사람은 이것을 알고 있지만 기계는 알수 없습니다.
그래서 START WITH SELF_ID = '100' 이렇게 지정해서, SELF_ID가 100인것 부터 시작하겠다라고 정의 하는것입니다.
꼭 이렇다고 정해진건 아닙니다. 가장합당한 시작 지점, 최상위 데이터를 정해주면 되는겁니다.
START WITH NM = '겔럭시' , START WITH PARENT_ID IS NULL 모두 다 가능합니다.
2. CONNECT BY ~ PRIOR
현재의 행과 다른행을 결합해야 하기에, 이것을 구분하기 위해서 PRIOR 라는 것을 함께 사용 한다.
PRIOR의 의미는 '최초의, 이전의' 라는 의미를 가지는데, 현재의 행의 값을 의미 한다고 생각하면 됩니다.
START WITH NM='겔럭시' CONNECT BY PRIOR SELF_ID = PARENT_ID 라고 한다면,
'겔럭시'가 존재하는 행에서 그 행의 SELF_ID 와 다른행의 PARENT_ID를 결합하는 방식입니다.
시작을 지정한 '겔럭시'의 SELF_ID는 '100' 이고 다른행 PARENT_ID가 100인것이 있다면 하위로 연결됩니다
추가적으로 LEVEL 이라는 값을 활용해, LPAD 함수와 함께 연동해 쿼리결과에서 계층형으로 보여 줄수 있습니다.
WITH T AS
(
SELECT '겔럭시' NM, '100' SELF_ID, '' PARENT_ID FROM DUAL
UNION ALL SELECT '카메라', '300', '100' FROM DUAL
UNION ALL SELECT '케이스', '400', '100' FROM DUAL
UNION ALL SELECT '메인보드', '200', '100' FROM DUAL
UNION ALL SELECT 'CPU', '410', '200' FROM DUAL
UNION ALL SELECT '메모리', '420', '200' FROM DUAL
UNION ALL SELECT '센서', '430', '200' FROM DUAL
UNION ALL SELECT '온도센서', '431', '430' FROM DUAL
UNION ALL SELECT '자이로센서', '432', '430' FROM DUAL
)
SELECT T.NM, T.SELF_ID, T.PARENT_ID, LPAD(' ', LEVEL*2) || T.NM AS NM2, LEVEL
FROM T
START WITH NM='겔럭시' CONNECT BY PRIOR SELF_ID = PARENT_ID
3. PRIOR의 위치가 변경 된다면?
반대로 START WITH NM='겔럭시' CONNECT BY SELF_ID = PRIOR PARENT_ID 이렇게 한다면
'겔럭시'의 PARENT_ID와 다른행의 SELF_ID를 결합합니다. '겔럭시'의 PARENT_ID 는 빈값이고,
이것과 결합할 다른행의 SELF_ID는 없겠죠? ^^;;
WITH T AS
(
SELECT '겔럭시' NM, '100' SELF_ID, '' PARENT_ID FROM DUAL
UNION ALL SELECT '카메라', '300', '100' FROM DUAL
UNION ALL SELECT '케이스', '400', '100' FROM DUAL
UNION ALL SELECT '메인보드', '200', '100' FROM DUAL
UNION ALL SELECT 'CPU', '410', '200' FROM DUAL
UNION ALL SELECT '메모리', '420', '200' FROM DUAL
UNION ALL SELECT '센서', '430', '200' FROM DUAL
UNION ALL SELECT '온도센서', '431', '430' FROM DUAL
UNION ALL SELECT '자이로센서', '432', '430' FROM DUAL
)
SELECT T.NM, T.SELF_ID, T.PARENT_ID, LPAD(' ', LEVEL*2) || T.NM AS NM2, LEVEL
FROM T
START WITH NM='겔럭시' CONNECT BY PRIOR SELF_ID = PARENT_ID
하지만 이것도 사용할 수 있는 용도가 있습니다.
START WITH NM='자이로센서' CONNECT BY SELF_ID = PRIOR PARENT_ID 라고 적는다면
'자이로센서' 현재 행의 PARENT_ID는 '430'이고 이것과 매칭하는 다른행의 SELF_ID가 '430' 인것은 '센서' 입니다.
다시 '센서의' PARENT_ID는 '200'이고 다른행중에 SELF_ID가 '200'인 것은 '메인보드' 입니다.
'메인보드'의 PARENT_ID는 '100'이고 다른행중에 SELF_ID가 '100'인 것은 '겔럭시' 입니다.
역순으로 정렬된 형태가 되는 것이죠.
'자이로센서'가 적용된 항목은 무었일까? 라고 하는 역전개 형태의 데이터 구성에 사용할 수 있습니다.
WITH T AS
(
SELECT '겔럭시' NM, '100' SELF_ID, '' PARENT_ID FROM DUAL
UNION ALL SELECT '카메라', '300', '100' FROM DUAL
UNION ALL SELECT '케이스', '400', '100' FROM DUAL
UNION ALL SELECT '메인보드', '200', '100' FROM DUAL
UNION ALL SELECT 'CPU', '410', '200' FROM DUAL
UNION ALL SELECT '메모리', '420', '200' FROM DUAL
UNION ALL SELECT '센서', '430', '200' FROM DUAL
UNION ALL SELECT '온도센서', '431', '430' FROM DUAL
UNION ALL SELECT '자이로센서', '432', '430' FROM DUAL
)
SELECT T.NM, T.SELF_ID, T.PARENT_ID, LPAD(' ', LEVEL*2) || T.NM AS NM2, LEVEL
FROM T
START WITH NM='자이로센서' CONNECT BY SELF_ID = PRIOR PARENT_ID
4. 계층형 쿼리의 정렬
ORDER SIBLINGS BY ~ 를 사용해서 정렬 합니다.
WITH T AS
(
SELECT '겔럭시' NM, '100' SELF_ID, '' PARENT_ID FROM DUAL
UNION ALL SELECT '카메라', '300', '100' FROM DUAL
UNION ALL SELECT '케이스', '400', '100' FROM DUAL
UNION ALL SELECT '메인보드', '200', '100' FROM DUAL
UNION ALL SELECT 'CPU', '410', '200' FROM DUAL
UNION ALL SELECT '메모리', '420', '200' FROM DUAL
UNION ALL SELECT '센서', '430', '200' FROM DUAL
UNION ALL SELECT '온도센서', '431', '430' FROM DUAL
UNION ALL SELECT '자이로센서', '432', '430' FROM DUAL
)
SELECT T.NM, T.SELF_ID, T.PARENT_ID, LPAD(' ', LEVEL*2) || T.NM AS NM2, LEVEL
FROM T
START WITH NM='겔럭시' CONNECT BY SELF_ID = PRIOR PARENT_ID
ORDER SIBLINGS BY NM DESC
이상으로 오라클의 계층형 쿼리 'START WITH ~ CONNECT BY ~' 에 대해서 알아 보았습니다.