3.3. 계층구조 쿼리의 응용
하위에서 상위로 누적 합계 구하기
아래는 현재행을 시작점으로 하위에서 상위로 누적합계를 구하는 예제이다.
- [그림] 계산예시
SELECT
empno
,
LEVEL
lv
, LPAD(
' '
, (
LEVEL
-1)*2,
' '
) || ename
AS
ename
, sal
, (
SELECT
SUM
(sal)
FROM
emp
START
WITH
empno = a.empno
CONNECT
BY
PRIOR
empno = mgr
) sum_sal
FROM
emp a
START
WITH
mgr
IS
NULL
CONNECT
BY
PRIOR
empno = mgr
;
EMPNO LV ENAME SAL SUM_SAL
------- ---------- -------------------- ---------- ----------
7839 1 KING 5000 29025
7566 2 JONES 2975 10875
7788 3 SCOTT 3000 4100
7876 4 ADAMS 1100 1100
7902 3 FORD 3000 3800
7369 4 SMITH 800 800
7698 2 BLAKE 2850 9400
7499 3 ALLEN 1600 1600
7521 3 WARD 1250 1250
7654 3 MARTIN 1250 1250
7844 3 TURNER 1500 1500
7900 3 JAMES 950 950
7782 2 CLARK 2450 3750
7934 3 MILLER 1300 1300
현재행을 시작점으로 스칼라서브쿼리에서 계층구조를 전개하여 합산을 한다.
CONNECT BY LEVEL을 이용한 테스트 샘플데이터 생성
프로젝트 진행시 테스트 데이터 생성이 필요한 경우가 있는데 CONNECT BY LEVEL을 활용하면 손쉽게 테스트 샘플 데이터를 생성 할 수 있다.
아래 예제와 같이 CONNECT BY LEVEL을 사용하면, 특정 LEVEL이 될 때 까지 ROW를 출력하는 것을 알 수 있다.
-- 10개의 ROW를 출력하는 예제이다.
SELECT
LEVEL
FROM
DUAL
CONNECT
BY
LEVEL
<= 10;
-- 10개의 ROW가 출력된다.
LEVEL
-------
1
2
3
4
...
10
위 CONNECT BY LEVEL의 원리를 이용하여 아래와 같이 테스트 샘플데이터를 만들어 보자
-- 10 만건의 데이터를 가지는 emp_sample 테이블을 생성하는 예제이다.
CREATE
TABLE
emp_sample
AS
SELECT
-- 1. EMPNO(Unique 컬럼)
LEVEL
empno,
-- 2. JOB(천만건 데이터를 10개 그룹으로 분류)
'SALESMAN_'
||CHR(65 + MOD(
LEVEL
, 10)) job,
-- 3. HIREDATE(금일+9일까지)
SYSDATE + MOD(
LEVEL
, 10) hiredate,
-- 4. DEPTNO (0, 10, 20, 30, 40)
MOD(
LEVEL
,5)*10 deptno
FROM
DUAL
CONNECT
BY
LEVEL
<= 100000;
-- 테이블과 데이터가 정상적으로 생성되었는지 확인해 보자
SELECT
*
FROM
emp_sample
WHERE
ROWNUM < 10;
EMPNO JOB HIREDATE DEPTNO
----- ----------- -------- ----------
1 SALESMAN_B 11/06/28 10
2 SALESMAN_C 11/06/29 20
3 SALESMAN_D 11/06/30 30
4 SALESMAN_E 11/07/01 40
...
경우의 수 조회
이번 예제는 그림과 같이 (A, B, C) 3개 코드 조합으로 나올 수 있는 모든 경우의 수를 조회하는 예제이다.
- [그림] 경우의 수
아래는 계층구조쿼리를 사용하지 않고, UNION ALL을 이용하여 작성하였다. 아래 쿼리는 self 조인과 UNION ALL 조합의 한계가 있다. 코드의 개수가 늘어날 경우 SQL 수정이 불가피 하다.
WITH
test
AS
(
SELECT
'A'
code
FROM
dual
UNION
ALL
SELECT
'B'
FROM
dual
UNION
ALL
SELECT
'C'
FROM
dual
)
SELECT
a.code
FROM
test a
UNION
ALL
SELECT
a.code ||
'-'
|| b.code
AS
code
FROM
test a
, test b
WHERE
a.code < b.code
UNION
ALL
SELECT
a.code ||
'-'
|| b.code ||
'-'
|| c.code
AS
code
FROM
test a
, test b
, test c
WHERE
a.code < b.code
AND
b.code < c.code
;
CODE
---------------
A
B
C
A-B
A-C
B-C
A-B-C
7
rows
selected.
코드의 개수가 늘어날 경우 SQL 수정이 불가피하다.
아래는 계층구조쿼리를 사용하여 경우의 수를 조회한 예제이다.
WITH
test
AS
(
SELECT
'A'
code
FROM
dual
UNION
ALL
SELECT
'B'
FROM
dual
UNION
ALL
SELECT
'C'
FROM
dual
)
SELECT
SUBSTR(SYS_CONNECT_BY_PATH(code,
'-'
), 2) code
FROM
test
CONNECT
BY
PRIOR
code < code
ORDER
BY
LEVEL
, code
;
CODE
-----------------
A
B
C
A-B
A-C
B-C
A-B-C
7
rows
selected.
부등호 조건(PRIOR CODE < CODE)을 이용한 계층 전개 계층전개 조건이 반드시 이퀄(=)조건일 필요는 없다.
순서까지 고려한 모든 경우의 수
이번 예제는 순서까지 고려한 모든 경우의 수이다.
- [그림] 모든경우의 수
WITH
test
AS
(
SELECT
'A'
code
FROM
dual
UNION
ALL
SELECT
'B'
FROM
dual
UNION
ALL
SELECT
'C'
FROM
dual
)
SELECT
SUBSTR(SYS_CONNECT_BY_PATH(code,
'-'
), 2) code
FROM
test
CONNECT
BY
NOCYCLE
PRIOR
code != code
ORDER
BY
LEVEL
, code
;
CODE
-------------------
A
B
C
A-B
A-C
B-A
B-C
C-A
C-B
A-B-C
A-C-B
B-A-C
B-C-A
C-A-B
C-B-A
15
rows
selected.
- - 부등호(!=) 사용시 순환구조 발생 (예: A-B-C-A-…)
- - NOCYCLE 사용으로 에러 방지 (예: A-B-C (여기까지만) -A-…)
'Database > Oracle SQL2' 카테고리의 다른 글
4.2. Conditional INSERT ALL (0) | 2019.04.25 |
---|---|
4.1. Unconditional INSERT ALL (0) | 2019.04.25 |
3.2. 계층구조 쿼리의 활용 (0) | 2019.04.24 |
3.1. 계층구조 쿼리(Hierarchical Queries)란? (0) | 2019.04.24 |
2.5. GROUPING SETS (0) | 2019.04.24 |
2.4. Cube (0) | 2019.04.24 |
2.3. Rollup 결과 행 구분 (0) | 2019.04.24 |
2.2. Rollup 예제 (0) | 2019.04.24 |