Oracle Advanced SQL 강좌
목록
계층구조 쿼리의 응용 3 5 99,999+

by 마농 계층구조쿼리 CONNECT BY LEVEL SYS_CONNECT_BY_PATH PRIOR NOCYCLE 테스트데이터생성 경우의수 [2014.01.17]

하위에서 상위로 누적 합계 구하기

아래는 현재행을 시작점으로 하위에서 상위로 누적합계를 구하는 예제이다.

  • [그림] 계산예시
  • 계산예시
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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를 출력하는 것을 알 수 있다.

?
1
2
3
4
5
6
7
8
9
10
11
12
-- 10개의 ROW를 출력하는 예제이다.
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10;
 
-- 10개의 ROW가 출력된다.
LEVEL
-------
      1
      2
      3
      4
...
     10

위 CONNECT BY LEVEL의 원리를 이용하여 아래와 같이 테스트 샘플데이터를 만들어 보자

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 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 수정이 불가피 하다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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 수정이 불가피하다.

아래는 계층구조쿼리를 사용하여 경우의 수를 조회한 예제이다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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)을 이용한 계층 전개 계층전개 조건이 반드시 이퀄(=)조건일 필요는 없다.

순서까지 고려한 모든 경우의 수

이번 예제는 순서까지 고려한 모든 경우의 수이다.

  • [그림] 모든경우의 수
  • 모든 경우의 수
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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-…)

- 강좌 URL : http://www.gurubee.net/lecture/2681

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
by 엘시아 [2014.07.08 11:03:32]

감사합니닷!

by 상유니 [2015.08.12 18:36:52]

응용은 어렵네요

by 므흣 [2016.05.02 22:48:09]

쉽지 않군요.

by 오뚝이 [2017.02.01 15:39:06]

감사합니다.^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입