계층구조 쿼리의 활용2

Oracle 2014. 12. 3. 17:53

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

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

[그림] 계산예시

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

현재행을 시작점으로 스칼라서브쿼리에서 계층구조를 전개하여 합산을 한다.

분석:순서대로 보기.

SELECT empno

     , LEVEL lv

     , LPAD(' ', (LEVEL-1)*2, ' ') || ename AS ename

     , sal

     , (SELECT SUM(sal)

          FROM emp

         START WITH empno = a.empno //3번 empno가 a.empno와 같은게 기준으로 즉 나를 기준으로

         CONNECT BY PRIOR empno = mgr //4번 empno를 기준으로 mgr의 level정렬을 만들어 내고 합을 구한다.

        ) sum_sal

  FROM emp a

 START WITH mgr IS NULL //1번 mgr이 null인 게 기준값으로.

 CONNECT BY PRIOR empno = mgr // 2번 empno를 기준으로 mgr으로 level정렬을 만들어 내고. 

;

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 //1번 아래와 같은 데이터가 있고.

(

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 // 2번 START WITH가 없으면 지금 데이터가 상위데이터가 된다. 

A:A = A

A:B = A-B

A:B:C = A-B-C

A:C = A-C

B:B = B

B:C = B-C

C:C = C 이런식으로 만들어진다. 

 ORDER BY LEVEL, code //3번 위데이터를 레벨과 코드값으로 정렬을 하였다. 

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

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

[그림] 모든경우의 수

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-…)

분석: 순서대로 보기..

WITH test AS

(

SELECT 'A' code FROM dual

UNION ALL SELECT 'B' FROM dual

UNION ALL SELECT 'C' FROM dual

UNION ALL SELECT 'D' FROM dual

UNION ALL SELECT 'E' FROM dual

UNION ALL SELECT 'F' FROM dual

)

, copy_t AS

(

SELECT ROWNUM rn FROM test

)

SELECT SUBSTR(code,2) code

FROM copy_t,

(

SELECT LEVEL lv

, SYS_CONNECT_BY_PATH(code,'-') code

FROM test, copy_t

START WITH rn = 1

CONNECT BY PRIOR code != code

AND PRIOR rn + 1 = rn

)

WHERE rn <= lv

GROUP BY lv, code

HAVING COUNT(DISTINCT SUBSTR(code

, INSTR(code,'-',1,rn)+1

, INSTR(code||'-','-',2,rn) - INSTR(code,'-',1,rn) - 1)

) = lv

ORDER BY lv, code

;

1번.이건 인터넷에서 궁금증 검색하다 찾은 9i버전에서 쿼리.뭔가 복잡

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 //2번 nocycle는 무한루프 방지고 code가 code와 같지 않을때까지.. 
 ORDER BY LEVEL, code
;

문서에 대하여

- 강좌 URL : http://www.gurubee.net/lecture/2681
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비의 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

'Oracle' 카테고리의 다른 글

CUBRID vs MySQL vs ORACLE SQL 타입별 비교 (0)	2014.12.08
INSERT ALL (0)	2014.12.04
계층구조 쿼리의 활용 (0)	2014.12.03
오라클 계층구조 쿼리 (0)	2014.12.03
GROUPING SETS (0)	2014.12.03

은호아빠

여행, 맛집, 일상, 프로그래밍, 개발자, 윈도우, 웹, jsp, spring, db, mysql, oracle, c#