-
Oracle SQL) 계층형 쿼리와 셀프조인SQLD/SQL 기본과 활용 2020. 9. 1. 16:54
# 계층형 데이터
- 동일 테이블에 계층적으로 상위 및 하위 데이터가 포함된 데이터
ex) EMP 테이블의 사원번호 및 그 사원의 관리자번호
# 셀프조인
- FROM 절에 동일 테이블을 2번이상 기술하는 조인 (재귀호출)
# 순방향 전개
- 자식 노드로 전개
# 역방향 전개
- 부모 노드로 전개
# 리프데이터
- 자식노드가 존재하지 않는 데이터
# 루트데이터
- 계층형데이터에서 최상위에 위치하는 데이터 (순방향이냐 역방향이냐에 따라 루트가 달라짐)
# 사이클
- 데이터 전개 중 이미 나타났던 동일한 데이터가 다시 나타날때, 이것을 사이클이 발생했다고 지칭하며, 런타임 오류를 발생시킨다.
# 계층형 쿼리(질의) (Hierarchical Query)
# Oracle
# Syntax SELECT ... FROM ... WHERE ... START WITH 조건 AND 조건 ... CONNECT BY [NOCYCLE] 조건 AND 조건 ... [ORDER SIBLINGS BY 칼럼1, 칼럼2, ...] +) [NOCYCLE] 키워드는 사이클이 발생한 이후의 데이터를 전개하지않을때 사용한다 +) [ORDER SIBLINGS BY 칼럼1, 칼럼2, ...] 키워드는 형제노드의 정렬기준을 정할때 사용한다 +) WHERE절은 모든 전개가 실행된 뒤 실행된다. # 계층형 쿼리에서 사용되는 가상칼럼 1. LEVEL - 루트데이터이면 1, 하위데이터이면 2, 리프데이터까지 1씩 증가한다. 2. CONNECT_BY_ISLEAF - 전개과정에서 해당데이터가 리프데이터이면 1, 아니면 0 3. CONNECT_BY_ISCYCLE - 사이클발생 지점에 1을 리턴하여 사이클 발생지점을 체크할 수 있다. # 계층형 쿼리에서 사용되는 함수 1. SYS_CONNECT_BY_PATH(col, str) - 데이터전개 경로표시 2. CONNECT_BY_ROOT(col) - 현재 전개할 데이터의 루트데이터 표시
# 사용예시 SQL> SELECT LEVEL AS LV ,EMPNO ,MGR ,CONNECT_BY_ISLEAF AS ISLEAF FROM EMP START WITH MGR IS NULL #(MGR == NULL 인 곳이 스타팅포인트) CONNECT BY MGR = PRIOR EMPNO; #(추출될 데이터의 MGR == 이전노드의 EMPNO) # 실행결과 LV EMPNO MGR ISLEAF _____________________________________________ 1 001 0 2 002 001 0 (# 리프노드가 아니다. 흘러온 방향대로 002의 자식노드를 검색한다) 3 003 002 1 (# EMPNO 003은 자식노드가없다 -> 003의 부모노드인 002로 돌아가서 다시 검색한다) 4 004 002 1 (# EMPNO 004은 자식노드가없다 -> 004의 부모노드인 002로 돌아가서 다시 검색한다) 5 313 001 1 (# EMPNO 313은 자식노드가없다 -> 313의 부모노드인 001로 돌아가서 다시 검색한다) 001 -> 002 -> 003 -> 002 -> 004 -> 002 -> 00 ( # EMPNO 313 의 MGR 001이 나오게 된 배경 ( EMPNO 004은 자식노드가없다 -> 004의 부모노드인 002로 돌아가서 다시 검색한다 ) 의 세부과정 -> 004를 MGR로 가지는 데이터를 찾는다(=004의 자식노드를 찾는다) -> 발견하지 못했다 (004는 리프노드이다) -> 스택에서 004를 삭제한다 -> 스택에서 데이터를 꺼낸다 (002) -> 002을 MGR로 가지는 노드 더이상 없음 -> 002 삭제 -> 스택에서 데이터를 꺼낸다 (001) -> 001을 MGR로 가지는 노드를 찾는다 -> EMP 313 ) # EMPNO 313 의 MGR 001이 나오게 된 배경을 보여주는 예시 쿼리문 SQL> SELECT LEVEL AS LV ,EMPNO ,MGR ,CONNECT_BY_ISLEAF AS ISLEAF ,SYS_CONNECT_BY_PATH(EMPNO,'->') AS PATH FROM EMP START WITH MGR IS NULL CONNECT BY MGR = PRIOR EMPNO; # 실행결과 LV EMPNO MGR ISLEAF PATH ___________________________________________________________________________ 1 001 0 ->001 2 002 001 0 ->001->002 3 003 002 1 ->001->002->003 4 004 002 1 ->001->002->004 5 313 001 0 ->001->313
728x90'SQLD > SQL 기본과 활용' 카테고리의 다른 글
SQL) Top N 쿼리 (0) 2020.09.01 SQL) WINDOW 함수(2) -그룹별 행 순서함수, 그룹별 비율함수 (0) 2020.08.31 SQL) WINDOW 함수(1) -그룹내 순위함수,일반 집계함수 (0) 2020.08.31 SQL) ROLLUP, CUBE 함수에 대한 이해와 고찰 (0) 2020.08.31 스칼라 서브쿼리(Scalar SubQuery), 인라인 뷰(Inline View), 뷰(View) (0) 2020.08.28