CONNECT BY와 계층형 질의

참 SQL에 별의별거 다 있다... 그죠?

 

트리를 아십니까? 의사결정이건 이진탐색이건 이 트리라는 건 시작 레벨과 끝 레벨이 있고 레벨들에 노드가 있고 그 노드끼리 연결이 되어 있는 뭐 그런건데, DB를 이런 식으로 계층형으로 탐색할 수 있다는 얘기.

---

순방향 탐색

select LEVEL, ENAME 이름, EMPNO 사원, MGR 관리자 from SCOTT.EMP start with MGR IS NULL connect by prior EMPNO=MGR;
select LEVEL, ENAME 이름, EMPNO 사원, MGR 관리자 from SCOTT.EMP start with MGR IS NULL connect by MGR = prior EMPNO;

connect by 뒤에가 좀 다르지만 두 쿼리가 같은 의미다. 위에서부터 밑으로 내려가면서 누가 누구 관리자인지 좀 봅시다 뭐 이런 의미. 여기서!!! 우리가 외워야 하는 건 저 커넥트 바이 뒤에 prior 붙은 거다. prior EMPNO=MGR하고 MGR = prior EMPNO 둘 다 순방향인데 순서만 좀 다른거다. 근데 둘이 순서만 다르지 prior는 다 자식 칼럼에 붙어있다. 순방향 탐색은 prior 자식 = 부모 혹은 부모 = prior 자식꼴로 쓸 수 있다. 이거 몰라서 기출문제 여러개 틀림... 아오 내 2점!!! 

 

근데 솔직히 이것만 봐서는 뭐가 뭔지 모르시겠죠? 그럴때는

select LEVEL, ENAME 이름, EMPNO 사원번호, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF from SCOTT.EMP start with MGR IS NULL connect by MGR = prior EMPNO;

CONNECT_BY_ISLEAF를 통해 누가 말단인지를 알 수 있다.

 

select LEVEL, ENAME 이름, EMPNO 사원번호, MGR 관리자, SYS_CONNECT_BY_PATH(EMPNO,'/') ROOT from SCOTT.EMP start with MGR IS NULL connect by MGR = prior EMPNO;

 

SYS_CONNECT_BY_PATH(EMPNO,'/')를 통해 어디서 어떻게 레벨이 갈라지는지를 알 수 있다. 근데 칼럼명 잘못 지은 것 같음.

 

select CONNECT_BY_ROOT EMPNO 시작사원, SYS_CONNECT_BY_PATH(EMPNO,'/') ROOT, EMPNO 사원, MGR 관리자 from SCOTT.EMP START WITH EMPNO IN (7566, 7698, 7782) connect by MGR = prior EMPNO;

이런것도 된다. 저기 IN에 들어가 있는 사원은 7839(킹)가 매니저인 사원들이고, 저 트리들은 휘하 사원들의 관리 하에 있는 사원들이라는 얘기. 여기서는 깔끔하게 나왔는데 터미널에서는 format 줘야 한 줄에 나오더라...

 

역방향 탐색

아까까지 봤던 순방향 탐색은 가계도에서 할머니/삼촌/사촌, 할머니/아빠/나, 할머니/고모/사촌 이런 식으로 조상에서부터 시작해서 쭉 내려왔다면 역벙향 탐색은 시발점이 나다. 그러니까 나/아빠/할머니 이렇게 가기 때문에 순방향 탐색과 달리 큰 그림을 그릴 수는 없고, 위와 같은 상황이라면 특정 매니저의 윗사람이 누구인지만 찾을 수 있다.

 

select LEVEL, EMPNO 사원, MGR 관리자 from SCOTT.EMP START WITH EMPNO='7876' CONNECT BY PRIOR MGR=EMPNO;

이게 역방향 탐색이다. 가장 아래 계층(7876번 사원)의 관리자, 그 윗사람, 그리고 그 윗사람까지 해서 최종적으로 대빵이 7879번이라는 얘기. 어? 쟤도 순방향처럼 순서 바꿀 수 있나요?

select LEVEL, EMPNO 사원, MGR 관리자 from SCOTT.EMP START WITH EMPNO='7876' CONNECT BY EMPNO=PRIOR MGR;

어이 제미나이. 안된다며.

 

순방향과 달리 역방향 탐색에서는 Prior가 부모 칼럼에 붙는다. 그래서 커넥트 바이 옆에 Prior 부모 = 자식 혹은 자식 = Prior 부모로 쓰게 된다. 아니EC 제미나이가 자식 = Prior 부모 안된댔는데 잘만되는데? 구글 CEO 나와보십쇼.

 

select LEVEL, EMPNO 사원, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF from SCOTT.EMP START WITH EMPNO='7876' CONNECT BY EMPNO=PRIOR MGR;

 

얘도 isleaf가 되긴 되는데… 엥? 자세히 보니까 위치가 좀 다른데요? 거꾸로 가는거니까 잎에서 가는거면 뿌리가 종점이거든.

 

select CONNECT_BY_ROOT EMPNO 시작사원, SYS_CONNECT_BY_PATH(EMPNO,'/') 경로, EMPNO 사원, MGR 관리자 from SCOTT.EMP START WITH EMPNO='7876' CONNECT BY EMPNO=PRIOR MGR;

당연하지만 커넥트 바이 루트도 된다.

 

NOCYCLE

벤젠을 아십니까? 그 육각형 고리같이 생긴 애 말하는거다. 그거 말고도 유기화학하다 보면 사이클로핵세인이라고 배우는데 아 이거 그리는법도 나감... ㅡㅡ 아무든 걔도 고리거든요? 그럼 고리의 특징이 뭐죠? 그죠 계속 돕니다. 사이클의 특징이 이미 나타났던 동일한 데이터가 또 나타난다는건데 이걸 갔던 길을 계속 뻉뺑이를 돌다 보면 이 길 왔던 길인데 왜 이러지? 하고 멘탈이 나가서 런타임 오류를 내놓는다. 그럴때 써주는건데, 노사이클 옵션은 쉽게 말하자면 한번 갔던 길이면 가지 마라는 얘기.

 

그 어디서 부정 불능 얘기하면서 0/0 하면 0에서 0을 계에에에에에에에에에에에에에에에에에속 빼야 한다고 했죠? 이게 아마 부정일건데, 그래서 0으로 나눌라하면 안돼 못해 에러낼거야 하는거랑 비슷하다. 물론 노사이클은 에러 나기 전에 어어 가지마라 하는거에 가깝다만…

 

ORDER SIBLINGS BY

자식 노드들도 정렬해라 이 얘기다. 그니까 할머니 밑에 큰고모 큰삼촌 작은고모 작은삼촌 아빠 이렇게 있으면 어떤 기준(예: 나이순)으로 고모 삼촌 아빠 정렬하라는 얘기.