psycoder
[Psycoder] ROLLUP으로 소계,합계내기 본문
오늘은 오라클에서 소계 ,합계내는 방법을 소개하고자 한다.
어느 프로젝트든 관리자단에는 통계를 내는 부분이 존재한다.(없는곳도 있겠지만....)
대충 예를 들자면 2008년도에 지출한 금액에 합계를 낸다든지, 혹은 지점별로 합계를 낸다던지. 이런식인거다.
뭐 쿼리문에서 간단한 SELECT문으로 통으로 가져와서 프로그램단에서 합계를 낼수도 있겠지만, 그것보단 기존 디비에서 가지고 올때 합계를 계산해서 가지고 온다면 우리 개발자들이 프로그램 할때 훨씬 편할꺼라 생각이 들어 이렇게 글을 남기는거다.
서두가 너무 긴것 같은데.이제 한번 들어가볼까용..?? ^^
우선 테스트할 테이블 몇개를 만들어보겠다
[그림 - 1] SELL TABLE
- SELL_CD = sell 테이블에 시퀀스값
- EMP_CD = 사원 코드(employee 테이블 참조)
- SELL_WON = 매출값
- SELL_DT = 매출날짜
[그림 - 2] EMPLOYEE TABLE
- EMP_CD = employee 테이블 시퀀스값
- EMP_NAME = 사원명
- CENTER_CD = 지점코드(center 테이블 참조)
[그림 - 3]CENTER TABLE
- CENTER_CD = center 테이블 시퀀스값
- CENTER_NAME = 지점명
우선 위의 그림과 같이 세개의 테이블을 만든후 임의 값들을 저장한다. 저장하였다면 우리가 만들어 볼 쿼리를
테스트할 모든 준비는 끝난거다.
자~ 우리가 만들어볼 쿼리는 년도를 입력을 했을때 지점별,사원명별루 합계가 나오게 하는것이다.
[그림 - 4]
이런 형식으로 값이 출력이 되게 만들려면 어떻게야 할까??
[그림 - 5]
쿼리를 보면 좀 복잡해 보이지만, rollup과 grouping만 알면은 쉽게 이해할 수 있을꺼다.
rollup연산자는 각 group의 결과들을 하나의 결과로 생성시켜준다.
다시말해서 rollup구문은 group by절과 같이 사용되며,group by절에 의해서 그룹 지어진 집합결과에 대해서 좀 더 상세한 정보를
출력해주는거다.
grouping함수는 저장되어 지는 값에는 0, 만들어진 값에서는 1이 표시가 된다.
자 그러면 쿼리를 설명해보겠다.
GROUP BY ROLLUP(TO_CHAR(c.sell_dt, 'yyyyMM'), a.center_cd, b.emp_cd)
우선 rollup연산자를 이용하여 매출일,지점코드,사원코드 순으로 집합결과를 생성시키고,grouping을 이용하여 이걸 구분을 한다.
GROUPING(TO_CHAR(c.sell_dt, 'yyyyMM'))
+ GROUPING(a.center_cd)
+ GROUPING(b.emp_cd) DIVISION_CD
집합결과값이 아닌경우는 grouping함수에서 0를 생성시키고, 그외 값일 경우 1을 발생시킨다.
[그림-4]를 보시면 aa,bb,cc 컬럼이 보일것이다. 그 값들의 값이 1이 출력이 된 행은 집합결과 정보가 출력되었다는거다.
aa컬럼이 매출일 집합결과여부, bb컬럼이 지점별 집합결과여부, cc컬럼이 사원별 집합결과여부 이다.
DECODE(GROUPING(TO_CHAR(c.sell_dt, 'yyyyMM'))
+ GROUPING(a.center_cd)
+ GROUPING(b.emp_cd), 1, '지점별소계', 2, '월별소계', 3, '합계', NULL) DIVISION_NAME,
이 세 컬럼 값의 합이 1이면 지점별 합계 2이면 월별합계 3이면 전체합계를 나타낸다.
CASE WHEN (GROUPING(TO_CHAR(c.sell_dt, 'yyyyMM'))
+ GROUPING(a.center_cd)
+ GROUPING(b.emp_cd) = 0)
THEN MAX(a.center_name) END CENTER_NAME,
또 이 세 컬럼 값의 합이 0이면 기존의 디비의 데이터, 즉 집합결과값이 아닌 순수 데이터를 보여주는것이다.
자 이해가 되시는지?? 제가 나름 열심히 설명을 한다 했는데 잘되었는지 잘 모르겠다.
암튼 오라클에서 rollup과 grouping함수를 잘쓴다면 통계쪽 개발업무의 어려움이 좀 덜지 않겠나?? 아닌가...^^
오늘 글은 이쯤에서 마무리 하겠다.
궁금한점 있으시면 댓글로 남겨주세용.
어느 프로젝트든 관리자단에는 통계를 내는 부분이 존재한다.(없는곳도 있겠지만....)
대충 예를 들자면 2008년도에 지출한 금액에 합계를 낸다든지, 혹은 지점별로 합계를 낸다던지. 이런식인거다.
뭐 쿼리문에서 간단한 SELECT문으로 통으로 가져와서 프로그램단에서 합계를 낼수도 있겠지만, 그것보단 기존 디비에서 가지고 올때 합계를 계산해서 가지고 온다면 우리 개발자들이 프로그램 할때 훨씬 편할꺼라 생각이 들어 이렇게 글을 남기는거다.
서두가 너무 긴것 같은데.이제 한번 들어가볼까용..?? ^^
우선 테스트할 테이블 몇개를 만들어보겠다
[그림 - 1] SELL TABLE
- SELL_CD = sell 테이블에 시퀀스값
- EMP_CD = 사원 코드(employee 테이블 참조)
- SELL_WON = 매출값
- SELL_DT = 매출날짜
[그림 - 2] EMPLOYEE TABLE
- EMP_CD = employee 테이블 시퀀스값
- EMP_NAME = 사원명
- CENTER_CD = 지점코드(center 테이블 참조)
[그림 - 3]CENTER TABLE
- CENTER_CD = center 테이블 시퀀스값
- CENTER_NAME = 지점명
우선 위의 그림과 같이 세개의 테이블을 만든후 임의 값들을 저장한다. 저장하였다면 우리가 만들어 볼 쿼리를
테스트할 모든 준비는 끝난거다.
자~ 우리가 만들어볼 쿼리는 년도를 입력을 했을때 지점별,사원명별루 합계가 나오게 하는것이다.
[그림 - 4]
이런 형식으로 값이 출력이 되게 만들려면 어떻게야 할까??
[그림 - 5]
쿼리를 보면 좀 복잡해 보이지만, rollup과 grouping만 알면은 쉽게 이해할 수 있을꺼다.
rollup연산자는 각 group의 결과들을 하나의 결과로 생성시켜준다.
다시말해서 rollup구문은 group by절과 같이 사용되며,group by절에 의해서 그룹 지어진 집합결과에 대해서 좀 더 상세한 정보를
출력해주는거다.
grouping함수는 저장되어 지는 값에는 0, 만들어진 값에서는 1이 표시가 된다.
자 그러면 쿼리를 설명해보겠다.
GROUP BY ROLLUP(TO_CHAR(c.sell_dt, 'yyyyMM'), a.center_cd, b.emp_cd)
우선 rollup연산자를 이용하여 매출일,지점코드,사원코드 순으로 집합결과를 생성시키고,grouping을 이용하여 이걸 구분을 한다.
GROUPING(TO_CHAR(c.sell_dt, 'yyyyMM'))
+ GROUPING(a.center_cd)
+ GROUPING(b.emp_cd) DIVISION_CD
집합결과값이 아닌경우는 grouping함수에서 0를 생성시키고, 그외 값일 경우 1을 발생시킨다.
[그림-4]를 보시면 aa,bb,cc 컬럼이 보일것이다. 그 값들의 값이 1이 출력이 된 행은 집합결과 정보가 출력되었다는거다.
aa컬럼이 매출일 집합결과여부, bb컬럼이 지점별 집합결과여부, cc컬럼이 사원별 집합결과여부 이다.
DECODE(GROUPING(TO_CHAR(c.sell_dt, 'yyyyMM'))
+ GROUPING(a.center_cd)
+ GROUPING(b.emp_cd), 1, '지점별소계', 2, '월별소계', 3, '합계', NULL) DIVISION_NAME,
이 세 컬럼 값의 합이 1이면 지점별 합계 2이면 월별합계 3이면 전체합계를 나타낸다.
CASE WHEN (GROUPING(TO_CHAR(c.sell_dt, 'yyyyMM'))
+ GROUPING(a.center_cd)
+ GROUPING(b.emp_cd) = 0)
THEN MAX(a.center_name) END CENTER_NAME,
또 이 세 컬럼 값의 합이 0이면 기존의 디비의 데이터, 즉 집합결과값이 아닌 순수 데이터를 보여주는것이다.
자 이해가 되시는지?? 제가 나름 열심히 설명을 한다 했는데 잘되었는지 잘 모르겠다.
암튼 오라클에서 rollup과 grouping함수를 잘쓴다면 통계쪽 개발업무의 어려움이 좀 덜지 않겠나?? 아닌가...^^
오늘 글은 이쯤에서 마무리 하겠다.
궁금한점 있으시면 댓글로 남겨주세용.
Comments