1. 데이터 분석 개요
2. ROLLUP 함수
3. CUBE 함수
4. GROUPING SETS 함수
GROUP BY FUNCTION 설명
링크 : http://scidb.tistory.com/search/group%20by%20extension
SELECT A,B, SUM(C)
FROM T
GROUP BY ROLLUP (A,B)
==>
SELECT A,B, SUM(C)
FROM T
GROUP BY A,B
UNION ALL
SELECT A,NULL, SUM(C)
FROM T
GROUP BY A
UNION ALL
SELECT NULL,NULL SUM(C)
FROM T
GROUP BY NULL
SELECT A,B, SUM(C)
FROM T
GROUP BY CUBE(A,B)
==>
SELECT NULL, NULL, SUM(C)
FROM T
GROUP BY NULL
UNION ALL
SELECT NULL, B, SUM(C) << ROLLUP(A,B) 에 없는 경우의 수
FROM T
GROUP BY B
UNION ALL
SELECT A, NULL, SUM(C)
FROM T
GROUP BY A
UNION ALL
SELECT A, B, SUM(C)
FROM T
GROUP BY A, B
SELECT A,B, SUM(C)
FROM T
GROUP BY
GROUPING SETS (A,B )
==>
SELECT A, NULL, SUM(C)
FROM T
GROUP BY A
UNION ALL
SELECT NULL, B, SUM(C)
FRO T
GROUP BY B
* STEP 1. 일반적인 GROUP BY 절 사용
* 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL
* Oracle 과거 버전에서 GROUP BY 사용지 자동 정렬은 더이상 지원 되지 않는다. ==> 명시적 정렬 필요
SQL> SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB ;
DNAME JOB Total Empl Total Sal
-------------- --------- ---------- ----------
SALES SALESMAN 4 5600
ACCOUNTING PRESIDENT 1 5000
RESEARCH CLERK 2 1900
SALES MANAGER 1 2850
SALES CLERK 1 950
ACCOUNTING MANAGER 1 2450
ACCOUNTING CLERK 1 1300
RESEARCH MANAGER 1 2975
RESEARCH ANALYST 2 6000
9 rows selected
* STEP 1-2. GROUP BY 절 + ORDER BY 절 사용
* 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ORDER BY 절 사용함으로써 부서, 업무별로 정렬
SQL> SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB
ORDER BY DNAME, JOB ;
DNAME JOB Total Empl Total Sal
-------------- --------- ---------- ----------
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
RESEARCH MANAGER 1 2975
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
9 rows selected.
* STEP 2. ROLLUP 함수
* 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수 사용
SQL> SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB) ;
DNAME JOB Total Empl Total Sal
-------------- --------- ---------- ----------
SALES CLERK 1 950 < L1(GROUP BY 수행 시 생성되는 표준 집계)
SALES MANAGER 1 2850 < L1
SALES SALESMAN 4 5600 < L1
SALES 6 9400 << L2(DNAME 별 모든 JOB 의 SUBTOTAL(소계))
RESEARCH CLERK 2 1900 < L1
RESEARCH ANALYST 2 6000 < L1
RESEARCH MANAGER 1 2975 < L1
RESEARCH 5 10875 << L2
ACCOUNTING CLERK 1 1300 < L1
ACCOUNTING MANAGER 1 2450 < L1
ACCOUNTING PRESIDENT 1 5000 < L1
ACCOUNTING 3 8750 << L2
14 29025 <<< L3(GRANT TOTAL(총계))
13 rows selected.
* STEP 2-2. ROLLUP 함수 + ORDER BY 절 사용
* 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수 사용
* 추가로 ORDER BY 절 사용해서 부서, 업무별로 정렬
SQL> SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB)
ORDER BY DNAME, JOB ;
DNAME JOB Total Empl Total Sal
-------------- --------- ---------- ----------
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING 3 8750
RESEARCH ANALYST 2 6000
RESEARCH CLERK 2 1900
RESEARCH MANAGER 1 2975
RESEARCH 5 10875
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES 6 9400
14 29025
13 rows selected.
* STEP 3. GROUPING 함수 사용 : ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가됨
* ROLLUP 이나 CUBE 에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 로 표시
* 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시된다.
* GROUPING 함수와 CASE/DECODE 를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있음(Report 작성시 )
SQL> SELECT DNAME, GROUPING(DNAME),
JOB, GROUPING(JOB),
COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB) ;
DNAME GROUPING(DNAME) JOB GROUPING(JOB) Total Empl Total Sal
-------------- --------------- --------- ------------- ---------- ----------
SALES 0 CLERK 0 1 950
SALES 0 MANAGER 0 1 2850
SALES 0 SALESMAN 0 4 5600
SALES 0 1 6 9400 << JOB 별 소계
RESEARCH 0 CLERK 0 2 1900
RESEARCH 0 ANALYST 0 2 6000
RESEARCH 0 MANAGER 0 1 2975
RESEARCH 0 1 5 10875 << JOB 별 소계
ACCOUNTING 0 CLERK 0 1 1300
ACCOUNTING 0 MANAGER 0 1 2450
ACCOUNTING 0 PRESIDENT 0 1 5000
ACCOUNTING 0 1 3 8750 << JOB 별 소계
1 1 14 29025 << DNAME, JOB 전체 소계
13 rows selected.
* STOP 4. GROUPING 함수 + CASE 사용 [ Reporting 용도 ]
SQL> SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB) ;
DNAME JOB Total Empl Total Sal
--------------- --------- ---------- ----------
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES All Jobs 6 9400
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
RESEARCH All Jobs 5 10875
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING All Jobs 3 8750
All Departments All Jobs 14 29025
13 rows selected.
* DECODE 로 표현
SQL> SELECT DECODE(GROUPING(DNAME),1,'All Departments',DNAME) AS DNAME,
DECODE(GROUPING(JOB),1, 'All Jobs',JOB) AS JOB,
COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB) ;
DNAME JOB Total Empl Total Sal
--------------- --------- ---------- ----------
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES All Jobs 6 9400
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
RESEARCH All Jobs 5 10875
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING All Jobs 3 8750
All Departments All Jobs 14 29025 << GROUP BY DNAME, ROLLUP(JOB) 시 삭제되는 ROW [ 아래 ROLLUP 함수 일부 사용 참조 ]
13 rows selected.
* STEP 4-2. ROLLUP 함수 일부 사용
* GROUP BY ROLLUP(DNAME,JOB) 조건에서 GROUP BY DNAME, ROLLUP(JOB) 조건으로 변경
SQL> SELECT DECODE(GROUPING(DNAME),1,'All Departments',DNAME) AS DNAME,
DECODE(GROUPING(JOB),1, 'All Jobs',JOB) AS JOB,
COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB) ; /
DNAME JOB Total Empl Total Sal
--------------- --------- ---------- ----------
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES All Jobs 6 9400
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
RESEARCH All Jobs 5 10875
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
ACCOUNTING All Jobs 3 8750
12 rows selected.
* STEP 4-3. ROLLUP 함수 결합 컬럼 사용
* JOB 과 MGR는 하나의 집합으로 간주하고, 부서별, JOB & MGR 에 대한 ROLLUP 결과를 출력
SQL> SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, (JOB,MGR)) ;
DNAME JOB MGR Total Sal
-------------- --------- ---------- ----------
SALES CLERK 7698 950
SALES MANAGER 7839 2850
SALES SALESMAN 7698 5600
SALES 9400
RESEARCH CLERK 7788 1100
RESEARCH CLERK 7902 800
RESEARCH ANALYST 7566 6000
RESEARCH MANAGER 7839 2975
RESEARCH 10875
ACCOUNTING CLERK 7782 1300
ACCOUNTING MANAGER 7839 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
29025
14 rows selected.
* STEP 5. CUBE 함수 이용
* GROUP BY ROLLUP(DNAME, JOB) ==> GROUP BY CUBE(DNAME, JOB)
* 2의 N승(GROUP BY 컬럼갯수) 만큼 집합이 강제로 생성 ==> 4가지 경위에 대한 UNION ALL 을 의미하는 듯
SQL> SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME, JOB) ;
DNAME JOB Total Empl Total Sal
--------------- --------- ---------- ----------
All Departments All Jobs 14 29025 <<< 총계
All Departments CLERK 4 4150 << 1. ROLLUP 함수에 비해 추가된 "업무별 집계"
All Departments ANALYST 2 6000 << 2. ROLLUP 함수에 비해 추가된 "업무별 집계"
All Departments MANAGER 3 8275 << 3. ROLLUP 함수에 비해 추가된 "업무별 집계"
All Departments SALESMAN 4 5600 << 4. ROLLUP 함수에 비해 추가된 "업무별 집계"
All Departments PRESIDENT 1 5000 << 5. ROLLUP 함수에 비해 추가된 "업무별 집계"
SALES All Jobs 6 9400 <<
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
RESEARCH All Jobs 5 10875 <<
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
ACCOUNTING All Jobs 3 8750 <<
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
18 rows selected.
* STEP 5-2. UNION ALL 사용 SQL
* CUBE 와 같은 결과를 UNION ALL 로 구현
* 결과 데이타는 같으나 행들의 정렬은 다를 수 있음
* CUBE 의 장점 : UNION ALL 과 다르게 테이블을 한번만 Access
SQL> SELECT DNAME, JOB, COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB
UNION ALL
SELECT DNAME, 'ALL JOBS', COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME
UNION ALL
SELECT 'ALL DEPERTMENTS', JOB, COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB
UNION ALL
SELECT 'ALL DEPERTMENTS', 'ALL JOBS', COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO ;
DNAME JOB TOTAL EMPL TOTAL SAL
--------------- --------- ---------- ----------
SALES SALESMAN 4 5600
ACCOUNTING PRESIDENT 1 5000
RESEARCH CLERK 2 1900
SALES MANAGER 1 2850
SALES CLERK 1 950
ACCOUNTING MANAGER 1 2450
ACCOUNTING CLERK 1 1300
RESEARCH MANAGER 1 2975
RESEARCH ANALYST 2 6000
RESEARCH ALL JOBS 5 10875
SALES ALL JOBS 6 9400
ACCOUNTING ALL JOBS 3 8750
ALL DEPERTMENTS MANAGER 3 8275
ALL DEPERTMENTS ANALYST 2 6000
ALL DEPERTMENTS PRESIDENT 1 5000
ALL DEPERTMENTS SALESMAN 4 5600
ALL DEPERTMENTS CLERK 4 4150
ALL DEPERTMENTS ALL JOBS 14 29025
18 rows selected.
* 일반 그룹함수를 이용한 SQL
* 부서별, JOB 별 인원수와 급여 합
SQL> SELECT DNAME, 'ALL JOBS' JOB, COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME
UNION ALL
SELECT 'ALL DEPARTMENTS' DNAME, JOB, COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB ;
DNAME JOB TOTAL EMPL TOTAL SAL
--------------- --------- ---------- ----------
RESEARCH ALL JOBS 5 10875
SALES ALL JOBS 6 9400
ACCOUNTING ALL JOBS 3 8750
ALL DEPARTMENTS MANAGER 3 8275
ALL DEPARTMENTS ANALYST 2 6000
ALL DEPARTMENTS PRESIDENT 1 5000
ALL DEPARTMENTS SALESMAN 4 5600
ALL DEPARTMENTS CLERK 4 4150
8 rows selected.
* GROUPING SETS 사용 SQL
SQL> SELECT DECODE(GROUPING(DNAME), 1,'ALL DEPARTMENTS', DNAME) AS DNAME,
DECODE(GROUPING(JOB), 1,'ALL JOBS' , JOB ) AS JOB,
COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS ( DNAME, JOB ) ;
DNAME JOB TOTAL EMPL TOTAL SAL
--------------- --------- ---------- ----------
RESEARCH ALL JOBS 5 10875
SALES ALL JOBS 6 9400
ACCOUNTING ALL JOBS 3 8750
ALL DEPARTMENTS MANAGER 3 8275
ALL DEPARTMENTS ANALYST 2 6000
ALL DEPARTMENTS PRESIDENT 1 5000
ALL DEPARTMENTS SALESMAN 4 5600
ALL DEPARTMENTS CLERK 4 4150
8 rows selected.
* GROUPING SETS 사용 SQL - 순서 변경
* 일반 그룹함수를 GROUPING SETS 함수로 변경하여 부서별, JOB별 인원수와 급여 합을 구하는데 GROUPING SETS 의 인수들의 순서를 변경
* GROUPING SETS 인수들은 평등한 관계이므로 인수의 순서과 바뀌어도 결과는 같다.
SQL> SELECT DECODE(GROUPING(DNAME), 1,'ALL DEPARTMENTS',DNAME) AS DNAME,
DECODE(GROUPING(JOB), 1,'ALL JOBS',JOB) AS JOB,
COUNT(*) "TOTAL EMPL", SUM(SAL) "TOTAL SAL"
FROM EMP,DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS ( JOB, DNAME ) ;
DNAME JOB TOTAL EMPL TOTAL SAL
--------------- --------- ---------- ----------
ALL DEPARTMENTS MANAGER 3 8275
ALL DEPARTMENTS ANALYST 2 6000
ALL DEPARTMENTS PRESIDENT 1 5000
ALL DEPARTMENTS SALESMAN 4 5600
ALL DEPARTMENTS CLERK 4 4150
RESEARCH ALL JOBS 5 10875
SALES ALL JOBS 6 9400
ACCOUNTING ALL JOBS 3 8750
8 rows selected.
* 3개의 인수를 이용한 GROUPING SETS 이용
* 부서-JOB-매니저 별 집계와, 부서-JOB 별 집계와, JOB-매니저 별 집계를 GROUPING SETS 함수를 이용해서 구현
SQL> SELECT DNAME, JOB, MGR, SUM(SAL) "TOTAL SAL"
2 FROM EMP, DEPT
3 WHERE DEPT.DEPTNO = EMP.DEPTNO
4 GROUP BY GROUPING SETS (( DNAME, JOB, MGR), (DNAME, JOB), ( JOB,MGR));
DNAME JOB MGR TOTAL SAL
-------------- --------- ---------- ----------
SALES CLERK 7698 950
ACCOUNTING CLERK 7782 1300
RESEARCH CLERK 7788 1100
RESEARCH CLERK 7902 800
RESEARCH ANALYST 7566 6000
SALES MANAGER 7839 2850
RESEARCH MANAGER 7839 2975
ACCOUNTING MANAGER 7839 2450
SALES SALESMAN 7698 5600
ACCOUNTING PRESIDENT 5000 << DNAME+JOB+MGR 기준의 집계
--------------------------------------------------------------------
CLERK 7698 950
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 800
ANALYST 7566 6000
MANAGER 7839 8275
SALESMAN 7698 5600
PRESIDENT 5000 << JOB + MGR 기준의 집계
--------------------------------------------------------------------
SALES SALESMAN 5600
ACCOUNTING PRESIDENT 5000
RESEARCH CLERK 1900
SALES MANAGER 2850
SALES CLERK 950
ACCOUNTING MANAGER 2450
ACCOUNTING CLERK 1300
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975 << DNAME+JOB 기준의 집계
--------------------------------------------------------------------
27 rows selected.