SQLD 계층형 질의와 셀프 조인

계층형 질의란?

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 사용

 

* 계층형 데이터

동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

 

START WITH

계층 구조 전개의 시작 위치를 지정하는 구문

CONNECT BY

전개될 자식 데이터를 지적하는 구문

PRIOR

CONNECT BY 절에 사용

• PRIOR 자식 = 부모
  부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개
• PROR 부모 = 자식
  자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 역방향 전개

NOCYCLE

사이클(Cycle)이 발생한 데이터는 런타임 오류가 발생하지만, NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다

 

* 사이클(Cycle)

데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 사이클(Cycle)이 형성되었다고 말한다.

 

ORDER SIBLINGS BY

형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다

정렬할 때 계층형 구조까지 보존하려면 ORDER SIBLINGS BY 절을 사용해야 한다

* LEVEL

루트 데이터이면 1, 하위 데이터이면 2. 리프(Leaf) 데이터까지 1씩 증가

 

CONNECT_BY_ISLEAF

전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0

CONNECT_BY_ISCYCLE

해당 데이터의 조상 데이터가 존재하면 1, 그렇지 않으면 0

 

* 리프 데이터 - 자식이 없는 데이터

* 조상 데이터 - 자신으로부터 루트까지의 경로에 존재하는 데이터

SELECT C3
FROM TAB1
START WITH CS IS NULL
CONNECT BY PRIOR C1 = C2
ORDER SIBLINGS BY C3 DESC

• LEVEL1 => C2가 NULL인 행
• LEVEL2 => LEVEL1에서의 C1의 값과 동일한 C2
• ORDER SIBLINGS BY => 동일 LEVEL 사이에서 내림차순 정렬 

C3

A

C

B

D

 

SQL Server 계층형 질의

CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개

 

문제풀이

SELECT A.부서코드, A.부서명, A.상위부서코드, B.매출액, LVL
FROM (SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
	FROM 부서
        START WITH 부서코드 = '120'
        CONNECT BY PRIOR 상위부서코드 = 부서코드
        UNION
        SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
        FROM 부서
        START WITH 부서코드 = '120'
        CONNECT BY 상위부서코드 = PRIOR 부서코드) A LEFT OUTER JOIN 매출 B
        ON (A.부서코드 = B.부서코드))
ORDER BY A.부서코드

 

• LEVEL1 => 부서코드가 120인 행

부서코드(PK)	부서명	상위 부서코드(FK)
120	일본지사	100

• CONNECT BY PRIOR => 자식 노드로 일본지사의 상위 부서 코드를 선택(역방향 전개)
• LEVEL2 => 부서코드가 100인 행

부서코드(PK)	부서명	상위 부서코드(FK)
100	아시아부	NULL

• 상위부서코드가 NULL이므로 재귀적인 호출 종료
• UNION => 중복 제거
• LEFT OUTER JOIN이 사용되었으므로 상위부서코드가 120인 행은 모두 출력
• 매출 테이블(Outer 대상이 되는 테이블)의 경우 ON 절에 매핑되는 정보만 출력

부서코드(PK)	부서명	상위 부서코드(FK)	매출액
120	일본지사	100	NULL
121	도쿄지점	120	1500
122	오사카지점	120	1000

• ORDER BY => 부서코드 순으로 정렬

셀프 조인이란?

동일 테이블 사이의 조인으로 하나의 테이블에서 두 개의 칼럼이 연관 관계를 가지고 있는 경우에 사용한다.

따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름 모두 동일하기 때문에 식별을 위해서 반드시 테이블 별칭(Alias)를 사용해야 한다. 

 

문제풀이

SELECT A.일자, SUM(B.매출액) AS 누적매출액
FROM 일자별매출 A JOIN 일자별매출 B ON (A.일자 >= A.일자) AS 누적매출액
GROUP BY A.일자
ORDER BY A.일자

• 일자별매출 A 쪽이 먼저 읽혔을 때 B 쪽의 데이터는 일자별로 데이터가 쌓이게 되면서 다음과 같이 데이터 형성이 된다.

일자	매출액
2015.11.01	1000
2015.11.02	1000
2015.11.02	1000

 

• SUM(B.매출액) => 일자별로 SUM 연산 진행