728x90
반응형


제 3절 계층형 질의와 셀프 조인


1. 계층형 질의


  테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. 이는 엔티티를 순환관계 데이터 모델로 설계한 경우 계층형 데이터가 발생하게 된다. 순환관계 데이터 모델의 예로는 조직, 사원, 메뉴 등이 있다.




가. 오라클(Oracle) 계층형 질의


  오라클은 계층형 질의를 지원하기 위해 아래와 같이 계층형 질의 구문을 제공한다. 


- START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트데이터를 지정한다.

- CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이턴는 CONNECT BY 절에 주어진 조건을 만족해야한다.

- PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다. 

만약 PRIOR 자식 = 부모 일 경우 계층구조에서 부모->자식 방향으로 순방향 전개를 한다.

PRIOR 부모 = 자식 일 경우 계층구조에서 자식->부모 방향으로 역방향 전개를 한다. 

- NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전재 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다

- ORDER SIBLINGS BY : 형제 노드(동일 레벨) 사이에서 정렬을 수행

- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출


오라클은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column)을 제공한다.




질의문에서 다음과 같은 질의문이 있다고 하자. LPAD 함수는 데이터를 들여쓰기 하기 위한 함수이다. 


SELECT LEVEL, LPAD(' ' , 4 * (LEVEL -1)) || EMPNO 사원, 

MGR 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM EMP

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;


  CONNECT BY PRIOR EMPNO = MGR 이 구문은, PRIOR 사원번호 = 관리자 라는 형태이므로 순방향 전개를 펼치게 된다. 또한, CONNECT_BY_ISLEAF 라는 가상 칼럼을 사용하여, 해당 데이터가 리프데이터(잎노드)인지 구별하고 있다.


  가상칼럼은 위 표처럼 LEVEL(데이터의 레벨, 루트가1), CONNECT_BY_ISLEAF(말단 노드인지 검사), CONNECT_BY_ISCYCLE(조상이 존재하면1, 그렇지 않으면0, CYCLE 옵션 사용 시 사용가능) 세가지가 있다.



  만일 위와는 다르게 쿼리문이 아래와 같이 써져있다면 역방향 전개를 하며 계층형 질의 결과를 보이게 된다.


SELECT LEVEL, LPAD(' ' , 4 * (LEVEL -1)) || EMPNO 사원, 

MGR 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM EMP

START WITH EMPNO = '7876'

CONNECT BY PRIOR MGR = EMPNO;



  오라클은 계층형 질의를 사용할 떄 사용자 편의성을 제공하기 위해 아래와 같은 함수를 제공한다. 하나는 SYS_CONNECT_BY_PATH로서, 루트 데이터로부터 자신인 데이터까지의 경로를 표시할 수 있다. 다른 하나는 CONNECT_BY_ROOT 로서, 현재 전개할 데이터의 루트데이터를 표시해준다. 단항연산자이다.




나. SQL Server 계층형 질의

  

  SQL Server 2000 버전까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았다. 조직도처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등프로그램 방식으로 전개해야만 했지만, 2005년 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 되었다. 


  CTE(Common Table Expression)을 재귀호출함으로써 Employees 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 결과는 다음과 같다.


WITH EMPLOYEES_ANCHOR AS (

SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL

FROM EMPLOYEES

WHERE REPORTSTO IS NULL / * 재귀 호출의 시작점 */

UNION ALL

SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1

FROM EMPLOYEES_ANCHOR A, EMPLOYEES R

WHERE A.EMPLOYEEID = R.REPORTSTO )

SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO

FROM EMPLOYEES_ANCHOR

GO

**************************

...



  위 커리를 보게 되면 UNION ALL 연산자로 두개의 쿼리를 결합했다. 둘 중 위에 있는 쿼리를 '앵커 멤버'(Anchor Member)라고 하고, 아래에 있는 쿼리를 '재귀 멤버' (Recursive Member)라고 한다. 아래는 재귀적 쿼리의 처리 과정이다.


1. CTE 식을 앵커 멤버와 재귀 멤버로 분할

2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기번 결과 집합(T0)을 만듦

3. Ti는 입력으로 사용하고 T(i+1)은 출력으로 사용하여 재귀 멤버를 실행

4. 빈 집합이 반환될 때까지 3단계를 반복

5. 결과 집합을 반환, T0에서 Tn까지의 UNION ALL


  정리하자면, 먼저 앵커 멤버가 시작점이자 Outer 집합이 되어, Inner 집합인 재귀 멤버와 조인을 시작하고, 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면, 더 조인할 수 없다면 지금까지 만들어진 결과 집합을 모두 합하여 리턴한다.






2. 셀프 조인


  셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라 FROM 절에 동일한 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼이름이 모두 동일하기 때문에 별칭을 쓰는 것이 필수적이다. 그리고 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별해주는 과정이 필요하다.


SELECT WORKER.EMPNO 사원번호, WORKER.ENAME 사원명, MANAGER.ENAME 관리자명

FROM EMP WORKER, EMP MANAGER

WHERE WORKER.MGR = MANAGER.EMPNO;


  자신과 상위, 차상위 관리자를 같은 줄에 표시하라 라는 질의의 쿼리문을 작성할 때 쓰이는 방식이다. 즉, 셀프조인은 한 테이블 내에서 두 칼럼이 연관관계가 있을 때 셀프조인을 수행하게 된다. 




728x90
반응형

'Computer Science > SQL-D' 카테고리의 다른 글

[SQLD] 25. 그룹함수(GROUP FUNCTION)  (0) 2016.09.01
[SQLD] 24. 서브쿼리  (0) 2016.09.01
[SQLD] 22. 집합 연산자 (SET OPERATOR)  (2) 2016.08.31
[SQLD] 21. 표준 조인(STANDARD JOIN)  (0) 2016.08.24
[SQLD] 20. 조인(JOIN)  (2) 2016.08.18