응용 프로그램 개발 시에 내역을 출력하고 중간 중간에 소개를 구하는 양식이 있다고 가정하자.
예시)
===========================================
지출일자 부서 성명 지출금액
===========================================
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 절에서 추출 할 수 있다.
