[Oracle] 오라클 고급 JOIN 테크닉 - 데이터 복제를 통한 소계 구하기

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

SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2;

-- 두 개의 집합이 자동 생성

- Let's 실습!!

CREATE TABLE MINA.EMP AS 

SELECT TO_CHAR((MOD(ROWNUM,3)+1)*10) DEPT_NO,

                 '7'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(100, 1000),0)) EMP_NO,

                 ROUND(DBMS_RANDOM.VALUE(1000, 10000),0) SAL

       FROM DUAL

 CONNECT BY LEVEL <= 20 ;

▼
Cartesian Product

SELECT *

  FROM MINA.EMP A,

           (SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 2) B

ORDER BY DEPT_NO, EMP_NO, ROWNUM;

▼
부서별 소계 구하기

SELECT DEPT_NO 부서코드,

            DECODE(NO,1,TO_CHAR(EMP_NO),'부서계') 사원번호

  FROM MINA.EMP A,

           (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2) B

ORDER BY 부서코드, 사원번호 ;

SELECT DEPT_NO 부서코드,

            DECODE(NO,1,TO_CHAR(EMP_NO),'부서계') 사원번호,

            SUM(SAL) 급여합,

            ROUND(AVG(SAL)) 급여평균

  FROM MINA.EMP A,

           (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2) B

GROUP BY DEPT_NO, NO, DECODE(NO,1,TO_CHAR(EMP_NO),'부서계')

ORDER BY 부서코드, 사원번호;

▼
부서별 소계 & 총계 구하기

SELECT DECODE(NO,3,NULL,TO_CHAR(DEPT_NO)) 부서코드,

            DECODE(NO,1,TO_CHAR(EMP_NO),2,'부서계','총계') 사원번호,

            SUM(SAL) 급여합,

            ROUND(AVG(SAL)) 급여평균

  FROM MINA.EMP A,

           (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 3) B

GROUP BY DECODE(NO,3,NULL,TO_CHAR(DEPT_NO)), NO, DECODE(NO,1,TO_CHAR(EMP_NO),2,'부서계','총계')

ORDER BY 부서코드, 사원번호;

* 그룹 함수를 사용해도 같은 결과를 도출해낼 수 있음

SELECT DEPT_NO 부서코드,

            CASE WHEN GROUPING(EMP_NO)=1 AND GROUPING(DEPT_NO)=1 THEN '총계'

                     WHEN GROUPING(EMP_NO)=1 THEN '부서계'

                     ELSE TO_CHAR(EMP_NO) END 사원번호,

            SUM(SAL) 급여합,

            ROUND(AVG(SAL)) 급여평균

  FROM MINA.EMP

GROUP BY ROLLUP(DEPT_NO, EMP_NO)

ORDER BY 부서코드, 사원번호;