[오라클, Oracle] 계층 쿼리 (hierarchical query) - 활용 예제

[순번 생성]

계층 쿼리를 사용하여 순번을 가진 테이블을 생성할 수 있다.

행 복제 시 해당 기법을 활용할 수 있다.

​

아래 쿼리는 100까지의 순번을 반환한다.

START WITH 절을 생략했기 때문에 DUAL 테이블의 전체 행이 루트 노드로 생성된다.

DUAL 테이블이 1행이므로 루트 노드가 1행으로 생성되고,

1행과 1행을 카티션 곱합 결과는 1행이므로 LEVEL <= 100 조건을 만족할 때까지 1행씩 레벨이 증가하는 것이다.

​

SELECT LEVEL AS LV

FROM DUAL

CONNECT BY LEVEL <= 100;

​

아래와 같이 XMLTABLE 함수를 사용해도 동일한 결과를 얻을 수 있다.

​

SELECT ROWNUM AS RN

FROM XMLTABLE ('1 TO 100');

​

[변경이력]

계층 쿼리를 응용하면 값의 변경 이력을 확인할 수 있따.

​

예제를 위해 아래와 같이 테이블을 생성하자

DROP TABLE T1 PURGE;

​

CREATE TABLE T1 (

YM VARCHAR2(6) -- 연월

, BF VARCHAT2(4) -- 변경 전 코드

, AF VARCHAR2(4) -- 변경 후 코드

);

​

INSERT INTO T1 VALUES ('205001', 'A', 'B');

INSERT INTO T1 VALUES ('205001', 'I', 'J');

INSERT INTO T1 VALUES ('205001', 'X', 'Y');

INSERT INTO T1 VALUES ('205004', 'B', 'C');

INSERT INTO T1 VALUES ('205004', 'J', 'K');

INSERT INTO T1 VALUES ('205007', 'C', 'D');

COMMIT;

​

아래 표는 코드 변경 이력을 표현하고 있다

A 코드는 2050년 1월에 B 코드로, B 코드는 2050년 4월에 C 코드로, C 코드는 2050년 7월에 D 코드로 변경되었다.

​

최초 - 205001 - 205004 - 205007

A - B - C - D

I - J - K

X - Y

​

아래 쿼리는 코드의 최종 변경 코드를 조회한다.

START WITH 절이 생략되어 전체 행이 루트 노드로 생성된다.

CONNECT_BY_ROOT 연산자로 루트 노드를 조회하고, CONNECT_BY_ISLEAF 슈도 칼럼으로 리프 노드만 조회했다.

​

SELECT BF, AF, YM

FROM (SELECT YM, CONNECT_BY_ROOT BF AS BF, AF, CONNECT_BY_ISLEAF AS LF

FROM T1

CONNECT BY BF = PRIOR AF)

WHERE LF = 1

ORDER BY 1;

​

아래 쿼리는 최초 코드의 변경 정보를 조회한다.

최초 코드로 루트 노드를 생성하기 위해

START WITH 절에서 변경 전 코드가 존재하지 않는 행을 조회한 후 순방향으로 계층을 전개했다.

​

SELECT BF, CD, YM, CD

FROM (SELECT CONNECT_BY_ROOT BF AS BF

, SUBSTR (SYS_CONNECT_BY_PATH (AF, ','), 2) AS CD

, SUBSTR (SYS_CONNECT_BY_PATH (YM, ','), 2) AS YM

, LEVEL AS CN

, CONNECT_BY_ISLEAF AS LF

FROM T1 A

START WITH NOT EXISTS (SELECT 1 FROM T1 X WHERE X.AF = A.BF)

CONNECT BY BF = PRIOR AF)

WHERE LF = 1

ORDER BY 1;

​

아래 쿼리는 최종 코드의 변경 정보를 조회한다.

최종 코드로 루트 노드를 생성하기 위해 START WITH 절에서 변경 후 코드가 존재하지 않는 행을 조회한 후 역방향으로 계층을 전개했다.

​

SELECT AF, CD, YM, CN

FROM (SELECT CONNECT_BY_ROOT AF AS AF

, SUBSTR (SYS_CONNECT_BY_PATH (BF, ','), 2) AS CD

, SUBSTR (SYS_CONNECT_BY_PATH (YM, ','), 2) AS YM

, LEVEL AS CN

, CONNECT_BY_ISLEAF AS LF

FROM T1 A

START WITH NOT EXISTS (SELECT 1 FROM T1 X WHERE X.BF = A.AF)

CONNECT BY AF = PRIOR BF)

WHERE LF = 1

ORDER BY 1;

​

[생성순서]

계층 쿼리를 사용하면 순차적으로 계산되는 계정의 생성 순서를 결정할 수 있다.

​

예제를 위해 아래와 같이 테이블을 생성

​

DROP TABLE T1 PURGE;

​

CREATE TABLE T1 (

CD VARCHAR2(1) -- 계정

, C1 VARCHAR2(1) -- 계산계정1

, C2 VARCHAR2(1) -- 계산계정2

, C3 VARCHAR2(1) -- 계산계정3

, C4 VARCHAR2(1) -- 계산계정4

);

​

INSERT INTO T1 VALUES ('A', 'B', 'C', 'D', 'E');

INSERT INTO T1 VALUES ('B', 'F', 'G', 'H', NULL);

INSERT INTO T1 VALUES ('C', 'I', 'J', NULL, NULL);

INSERT INTO T1 VALUES ('D', 'K', NULL, NULL, NULL);

INSERT INTO T1 VALUES ('E', 'B', 'C', NULL, NULL);

INSERT INTO T1 VALUES ('F', 'C', 'D', NULL, NULL);

​

COMMIT;

​

T1 테이블의 데이터를 아래와 같이 표현할 수 있다.

계정(CD)은 계산계정(C1, C2, C3, C4)으로 계산된다.

계정 A는 계정 B, C, D, E로 계산되고, 계정 B는 계정 F, G, H로 계산된다.

계정 A를 계산하기 위해서는 계정 B를 먼저 생성해야 한다.

​

CD - C1 - C2 - C3 - C4

A - B - C - D - E

B - F - G - H

C - I - J

D - K

E - B - C

F - C - D

​

아래 쿼리로 계정의 생성 순서를 결정할 수 있다.

먼저 생성할 필요가 없는 계산계정으로 생성되는 계정을 루트 노드로 생성한 후 역방향으로 계층을 전개했다.

계정 A는 다섯 번째로 생성해야 한다.

​

SELECT CD, MAX (LEVEL) AS LV

FROM T1 A

START WITH NOT EXISTS (SELECT 1 FROM T1 X WHERE X.CD IN (A.C1, A.C2, A.C3, A.C4))

CONNECT BY PRIOR CD IN (C1, C2, C3, C4)

GROUP BY CD

ORDER BY 2, 1;

​

위 쿼리의 CONNECT BY 절은 아래와 같이 해석된다.

CONNECT BY (C1 = PRIOR CD OR C2 = PRIOR CD OR C3 = PRIOR CD OR C4 = PRIRO CD)

​

[누적 연산]

재귀 서브 쿼리 팩토링을 사용하면 상위 노드의 값을 누적 연산할 수 있다.

​

아래 쿼리는 상위 노드의 SAL을 하위 노드로 누적 집계한다.

​

WITH W1 (EMPNO, ENAME, MGR, SAL, LV, C1) AS (

SELECT EMPNO, ENAME, MGR, SAL, 1 AS LV, SAL AS C1

FROM EMP

WHERE MGR IS NULL

UNION ALL

SELECT C.EMPNO, C.ENAME, C.MGR, C.SAL, P.LV + 1 AS LV, P.C1 + C.SAL AS C1

FROM W1 P, EMP C

WHERE C.MGR = P.EMPNO)

SEARCH DEPTH FIRST BY EMPNO SET SO

SELECT LV, EMPNO, LPAD (' ', LV - 1, ' ') || ENAME AS ENAME, MGR, SAL, C1

FROM W1

ORDER BY SO;

​

계층 쿼리 절은 부모 노드의 값만 참조할 수 있기 때문에 누적 연산이 불가능하다.

​

SELECT LEVEL AS LV, EMPNO, LPAD (' ', LEVEL - 1, ' ') || ENAME AS ENAME, MGR

, SAL, SAL + PRIOR SAL AS C1

FROM EMP

START WITH MGR IS NULL

CONNECT BY MGR = PRIOR EMPNO;

​