꿈꾸는 개발자, DBA 커뮤니티 구루비

(3) 데이터 복제를 통한 소계 구하기

쿼리를 작성하다 보면 데이터 복제 기법을 활용해야 할 때가 많다. 데이터복제를 위해 일부러 카티션 곱(Cartesian Product)을 발생시켜 복제하기도 한다.
전통적으로 많이 쓰던 방식은 복제용 테이블(copy_t)을 미리 만들어두고 이를 활용하는 것이다.


SQL>create table copy_t (no number, no2 varchar2(2));

Table created.

SQL>insert into copy_t
  2  select rownum, lpad(rownum,2,'0') from all_tables where rownum<=31;

31 rows created.

SQL>commit;

Commit complete.

SQL>alter table copy_t add constraint copy_t_pk primary key(no);

Table altered.

SQL>create unique index copy_t_no2_idx on copy_t(no2);

Index created.

-- 아래 쿼리를 실행하면 emp 테이블에 있는 14개의 레코드가 3개씩 총 42개로 복제된다.
SQL>select *
  2    from emp a, copy_t b
  3    where b.no<=3
  4  ;


     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO       NO NO
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- --
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20        1 01
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20        2 02
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20        3 03
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30        1 01
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30        2 02
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30        3 03
      .
      .
      .

42 rows selected.


-- 오라클 9i부터는 dual테이블에 START WITH절 없이 CONNECT BY 구문을 사용하면 두 개의 집합이 자동으로 만들어진다.
SQL>select rownum  from dual  connect by level <= 2;

	ROWNUM
----------
         1
         2

-- 이 방법을 사용해 emp 테이블을 복제하는 방법은 아래와 같다.
SQL>select *
  2     from emp a,
  3     (select rownum  from dual  connect by level <= 2) b
  4     order by empno
  5  ;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO     ROWNUM
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20        1
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20        2
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30        2
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30        1
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30        1
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30        2
      .
      .
      .
28 rows selected.

데이터 복제 기법을 활용하면 아래와 같이 단일 SQL로도 부서별 소계를 구할 수 있다.


SQL>break on 부서번호
SQL>column 부서번호 format 9999
SQL>column 사원번호 format a10
SQL>select deptno 부서번호
  2       , decode(no, 1, to_char(empno), 2, '부서계') 사원번호
  3       , sum(sal) 급여합, round(avg(sal)) 급여평균
  4  from   emp a, (select rownum no from dual connect by level <= 2)
  5  group by deptno, no, decode(no, 1, to_char(empno), 2, '부서계')
  6  order by 1, 2;


부서번호 사원번호       급여합   급여평균
-------- ---------- ---------- ----------
      10 7782             2450       2450
         7839             5000       5000
         7934             1300       1300
         부서계           8750       2917
      20 7369              800        800
         7566             2975       2975
         7788             3000       3000
         7876             1100       1100
         7902             3000       3000
         부서계          10875       2175
      30 7499             1600       1600
         7521             1250       1250
         7654             1250       1250
         7698             2850       2850
         7844             1500       1500
         7900              950        950
         부서계           9400       1567

17 rows selected.

이처럼 group by를 잘 구사하면 우리가 원하는 데이터 집합을 자유자재로 가공해 낼 수 있다.
아래는 세 개로 복제하고서 총계까지 구하는 사례다.


SQL>column 부서번호 format a10
SQL>select decode(no, 3, null, to_char(deptno)) 부서번호
  2       , decode(no, 1, to_char(empno), 2, '부서계', 3, '총계') 사원번호
  3       , sum(sal) 급여합, round(avg(sal)) 급여평균
  4  from   emp a, (select rownum no from dual connect by level <= 3)
  5  group by decode(no, 3, null, to_char(deptno))
  6        , no, decode(no, 1, to_char(empno), 2, '부서계', 3, '총계')
  7  order by 1, 2;


부서번호   사원번호       급여합   급여평균
---------- ---------- ---------- ----------
10         7782             2450       2450
           7839             5000       5000
           7934             1300       1300
           부서계           8750       2917
20         7369              800        800
           7566             2975       2975
           7788             3000       3000
           7876             1100       1100
           7902             3000       3000
           부서계          10875       2175
30         7499             1600       1600
           7521             1250       1250
           7654             1250       1250
           7698             2850       2850
           7844             1500       1500
           7900              950        950
           부서계           9400       1567
           총계            29025       2073

18 rows selected.

표준 ROLLUP 구문을 사용하면 데이터 복제 기법을 쓰지 않고도 아래와 같이 간편하게 소계및 총계를 구할 수 있다.


SQL>break on 부서번호
SQL>column 부서번호 format 9999
SQL>column 사원번호 format a10
SQL>select deptno 부서번호
  2       , case when grouping(empno) = 1 and grouping(deptno) = 1 then '총계'
  3              when grouping(empno) = 1 then '부서계'
  4              else to_char(empno) end  사원번호
  5       , sum(sal) 급여합, round(avg(sal)) 급여평균
  6  from   emp
  7  group by rollup(deptno, empno)
  8  order by 1, 2;


부서번호 사원번호       급여합   급여평균
-------- ---------- ---------- ----------
      10 7782             2450       2450
         7839             5000       5000
         7934             1300       1300
         부서계           8750       2917
      20 7369              800        800
         7566             2975       2975
         7788             3000       3000
         7876             1100       1100
         7902             3000       3000
         부서계          10875       2175
      30 7499             1600       1600
         7521             1250       1250
         7654             1250       1250
         7698             2850       2850
         7844             1500       1500
         7900              950        950
         부서계           9400       1567
         총계            29025       2073

18 rows selected.