계층형 질의

계층형 데이터

 

셀프조인

셀프조인이란 동인한 테이블 사이의 조인을 말한다. 따라서 from  절에 동일 테이블이 두 번이상 나타난다.

동일 테이블 사이의 조인을 수행하면 테이블과 컬럼 이름 모두 동일하므로 식별을 위해 반드시 테이블 별칭을 사용해야 한다. 

 

JONES 의 자식 노드를 조회하는 쿼리  (MGR가 JONES 의 EMPNO 인 행) 

SELECT B.EMPNO, B.ENAME, B.MGR
FROM EMP A, EMP B
WHRE A.ENAME = 'JONES'
AND B.MGR = A.EMPNO;

 

JONES 의 자식의 자식 노드를 조회하는 쿼리 (MGR이 JONES 의 자식인 SCOTT 와 FORM 의 EMPNO 인 행)

SELECT C.EMPNO, C.ENAME, C.MGR
FROM EMP A, EMP B, EMP C
WHRE A.ENAME = 'JONES'
AND B.MGR = A.EMPNO
AND C.MGR = B.EMPNO;

 

깊은 레벨의 노드를 조회하기 위해 셀프조인을 반복해야한다.

ORACLE 과 SQL SERVER 는 순환관계를 가진 데이터를 조회할 수 있는 계층 질의 기능을 제공한다.

---

계층형 질의

1. ORACLE 계층형 질의

SELECT FROM 테이블
	WHERE 조건
START WITH 조건
CONNECT BY [NOCYCLE] 조건
[ORDER SIBILINGS BY 컬럼,컬럼 .. ]

START WITH 절 : 계층 구조 전개의 시작 위치를 지정한다. 즉 루트 데이터를 지정한다.(엑세스)

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

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

   (FK) = PRIOR (PK) 형태를 사용하면 부모데이터에서 자식데이터로 전개하는 순방향 전개를 한다.

   (PK) = PRIOR (FK) 는 역방향 전개를 한다. 

NOCYCLE : 데이터 전개 중 동일한 데이터가 다시 나타나면 사이클이 발생했다고 한다. 사이클이 발생한 데이터는 런타임 오류가 난다. NOCYCLE 옵션을 추가하면 오류를 방발생시키지 않고 발생 이후의 데이터를 전개하지 않는다.

ORDER SIBLIGS BY : 형제 노트(동일 LEVEL)사이에서 정렬을 수행한다.

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

SELECT LEVEL, EMPNO , MGR	
	,CONNECT_BY_ISLEAF AS ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;

 

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

LEVEL : 루트 데이터면 1, 그 하위 데이터는 2이다.  리프 데이터까지 1씩 증가한다.

CONNECT_BY_ISLEAF : 전개과정에서 해당 데이터가 리프 데이터면 1, 아니면 0이다.

CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 갖는데 , 해당 데이터가 조상이면 1 , 아니면 2이다.

여기서 조상이란 자신으로부터 루트까지 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용가능하다.

 

ORACLE 은 계층형 질의를 사용할 때 사용자 편의를 위해 아래와 같은 함수를 제공한다.

SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.  

CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다.

SELECT CONNECT_BY_ROOT(EMPNO) AS ROOT_EMPNO
    ,SYS_CONNECT_BY_PATH(EMPNO,',') AS PATH
    ,EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;

 

 

2. SQL SERVER 계층형 질의

CTE (Common Table Expression) 를 재귀 호출함으로써 EMPLOYEES 데이터의 최상위부터 시작해 

하위 방향으로 계층 구조를 전개하도록 작성한 쿼리.

 

With 로 작성을 시작하며, CTE 결과를 select 할 때 expression_name 을 사용한다.

WITH expression_name [ ( column_name [,...n] ) ] 
AS 
( CTE_query_definition )


Select [Column1,Column2,Column3 …..] from expression_name

WITH EMPLOYEES_ANCHOR AS 
(
	SELECT EMPLOYEEDID, LASTNAME, FIRSTNAME, REPORTSTO, 1 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 B
		WHERE R.REPORTSTO = A.EMPLOYEEID 
)    
SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO 
FROM EMPLOYEES_ANCHOR

GO

WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 두 개의 쿼리를 결합했다.

둘 중 위에 있는 쿼리를 앵커멤버(Anchor Member),

아래에 있는 쿼리를 재귀멤버(Recursive Member) 라고 한다.

 

재귀적 쿼리의 처리과정

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

2. 앵커 멤버를 실행해 첫 번째 호출 또는 기본 결과 집합(T0)을 생성한다.

3. Ti 는 입력으로 사용하고 Ti +1 은 출력으로 사용해 재귀 멤버를 생성한다.

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

5. 결과 집합을 반환한다. 이것은 T0 에서 Tn 까지의 UNION ALL 이다.

 

앵커 멤버가 시작점이나 Outer 집합이 돼 Inner 집합인 재귀 멤버와 조인을 시작한다. 

이어서 앞서 조인한 결과가 다시 Outer 집합이 돼 재귀 멤버와 조인을 반복하다가 조인 결과가 비어있어 더 이상 조인이 불가할 때 지금까지 만든 결과 집합을 리턴한다.

 

 

 

 

 

조직도와 같은 모습의 결과를 출력하도록, CTE 에 Sort 라는 정렬용 컬럼을 추가하고, 쿼리 마지막에 ORDER BY 를 추가한다. (단 앵커 멤버와 재귀 멤버 양쪽에서 convert 함수 등으로 데이터 형식을 일치시켜야 한다.)

 

WITH T_EMP_ANCHOR AS 
(
	SELECT EMPLOYEEDID, MANAGERID, 0 AS LEVEL, CONVERT(VARCHAR(1000),EMPLOYEEID) AS SORT
    	FROM T_EMP
		WHERE MANAGERID IS NULL // 재귀호출의 시작점
UNION ALL
	SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL+1 
    				,CONVERT(VARCHAR(1000), A.SORT + ',' + R.EMPLOYEEID) AS SORT
    	FROM T_EMP_ANCHOR A, T_EMP R
		WHERE R.MANAGERID = A.EMPLOYEEID 
)    
SELECT LEVEL, EMPLOYEEID, MANAGERID, SORT
FROM T_EMP_ANCHOR
ORDER BY SORT

GO

CTE 안에서 SORT 컬럼에 EMPLOYEEID 를 재귀적으로 더해 나가면 정렬 기준으로 삼을 수 있는 값이 만들어진다.

 

 

MySql 테스트 

with RECURSIVE rowCT (col1) as
(
    select 1  from dual
    union all 
    select col1+1 from rowCT where col1<10 
)
select * from rowCT;

---

출처

SQL 전문가 가이드 - 한국데이터산업진흥원