세부내역과 소계를 한개의 쿼리문장으로 수행하는 SQL

응용 프로그램 개발 시에 내역을 출력하고 중간 중간에 소개를 구하는 양식이 있다고 가정하자.

예시)
===========================================
    지출일자   부서    성명      지출금액
===========================================
   2012-03-01 기술부   홍길동      1,000
   2012-03-02 기술부   홍길동      2,000
   2012-03-03 기술부   홍길동      3,000
   2012-03-04 기술부   홍길동      4,000
   기술부(홍길동)소계                10,000
   2012-03-05 기술부   김개똥      5,000
   2012-03-06 기술부   김개똥      6,000
   2012-03-07 기술부   김개똥      7,000
   기술부(김개똥)소계                18,000
   2012-03-08 기술부   엄길순      8,000
   2012-03-09 기술부   엄길순      9,000
   2012-03-10 기술부   엄길순     10,000
   기술부(엄길순)소계                 27,000
   기술부 소계                              55,000
   2012-03-11 영업부   영업맨     13,000
   2012-03-12 영업부   영업맨     14,000
   2012-03-13 영업부   영업맨     15,000
   영업부(영업맨)소계                 42,000
   2012-03-14 영업부   김영업     16,000
   2012-03-15 영업부   김영업     17,000
   2012-03-16 영업부   김영업     18,000
   영업부(영업맨)소계                 51,000
   영업부 소계                              93,000
   전체 합계                                148,000
===========================================

 

○ 처리방법
  - 1안) 전체 내역을 쿼리해서 이름별, 부서별로 합계를 구하면서 처리하는 방법
  - 2안) 쿼리를 어느정도 사용할 줄 아는 개발자라면 union all을 사용하여 처리하는 방법

○ 처리방법에 대한 검토내용
  - 1안은 이름별, 부서별로 변수를 사용해서 순서에 맞게 처리함으로 약간의 코딩 능력 및 공수가 필요하다.
  - 2안은 부서가 추가되거나, 이름이 추가될때 쿼리를 수정해야 할 수도 있으며,
    옵티마이져가 union all의 각각의 쿼리를 수행함으로 성능이 좋지 않다.
  - 하나의 쿼리로 위의 양식대로 추출할 수 있는 쿼리를 작성한다면, 코딩량도 줄어들 것이고, 성능도 보장 될 것이다.

○ DB 구조
create table t_expn(
  expn_date date,
  dept_nm varchar(60),
  user_nm varchar(60),
  expn_amt numeric(20)
  );

○ 샘플 데이타
insert into t_expn values('2012-03-01', '기술부', '홍길동',  1000 );
insert into t_expn values('2012-03-02', '기술부', '홍길동',  2000 );
insert into t_expn values('2012-03-03', '기술부', '홍길동',  3000 );
insert into t_expn values('2012-03-04', '기술부', '홍길동',  4000 );
insert into t_expn values('2012-03-05', '기술부', '김개똥',  5000 );
insert into t_expn values('2012-03-06', '기술부', '김개똥',  6000 );
insert into t_expn values('2012-03-07', '기술부', '김개똥',  7000 );
insert into t_expn values('2012-03-08', '기술부', '엄길순',  8000 );
insert into t_expn values('2012-03-09', '기술부', '엄길순',  9000 );
insert into t_expn values('2012-03-10', '기술부', '엄길순', 10000 );
insert into t_expn values('2012-03-11', '영업부', '영업맨', 13000 );
insert into t_expn values('2012-03-12', '영업부', '영업맨', 14000 );
insert into t_expn values('2012-03-13', '영업부', '영업맨', 15000 );
insert into t_expn values('2012-03-14', '영업부', '김영업', 16000 );
insert into t_expn values('2012-03-15', '영업부', '김영업', 17000 );
insert into t_expn values('2012-03-16', '영업부', '김영업', 18000 );

 

○ 쿼리문
select case when to_char(expn_date, 'YYYY-MM-DD')  is null  and dept_nm is not null  and user_nm is not null  then  dept_nm ||'('|| user_nm || ')'||' 소계' 
                  when to_char(expn_date, 'YYYY-MM-DD')  is null and dept_nm is not null  and user_nm is null   then  dept_nm ||' 소계'
                  when to_char(expn_date, 'YYYY-MM-DD')  is null and dept_nm is null  and user_nm is null   then '전체 합계'
                  ELSE  to_char(expn_date, 'YYYY-MM-DD') END AS [지출일자], 
         case when to_char(expn_date, 'YYYY-MM-DD')  is null  then ''
                  ELSE dept_nm END AS [부서],
         case when to_char(expn_date, 'YYYY-MM-DD')  is null  then ''
                  ELSE user_nm END AS [성명],
         to_char( sum_amt, '9,999,999') AS [지출금액]
from (   
      select  expn_date, dept_nm, user_nm, sum(expn_amt) AS sum_amt
      from t_expn AS B
      group by dept_nm, user_nm, expn_date

      with rollup
     ) AS X(expn_date, dept_nm, user_nm, sum_amt);

 

○ 실행결과
================================================
 지출일자            부서    성명    지출금액
================================================
2012-03-01         기술부 홍길동     1,000
2012-03-02         기술부 홍길동     2,000
2012-03-03         기술부 홍길동     3,000
2012-03-04         기술부 홍길동     4,000
기술부(홍길동) 소계                 10,000
2012-03-05         기술부 김개똥     5,000
2012-03-06         기술부 김개똥     6,000
2012-03-07         기술부 김개똥     7,000
기술부(김개똥) 소계                 18,000
2012-03-08         기술부 엄길순     8,000
2012-03-09         기술부 엄길순     9,000
2012-03-10         기술부 엄길순    10,000
기술부(엄길순) 소계                 27,000
기술부 소계                          55,000
2012-03-11         영업부 영업맨    13,000
2012-03-12         영업부 영업맨    14,000
2012-03-13         영업부 영업맨    15,000
영업부(영업맨) 소계                 42,000
2012-03-14         영업부 김영업    16,000
2012-03-15         영업부 김영업    17,000
2012-03-16         영업부 김영업    18,000
영업부(김영업) 소계                  51,000
영업부 소계                          93,000
전체 합계                           148,000

 

 

○ 고려사항 및 참고사항

  - 데이타 건수가 많을 경우에는 별도의 통계테이블을 설계하여 with rollup 을 사용하여 성능을 향상시킬 수 있다.
  - 위의 예시에서 개인별 소개, 부서별소개, 전체합계 소개 중 필요한 것만을 쿼리할 경우에는 where 절에서 추출 할 수 있다.