ROLLUP과 GROUPING SETS 함수
Updated:
GROUPING SETS함수 기본개념
GROUPING SETS 함수는 GROUP BY 절에서 그룹 조건을 여러 개 지정할 수 있는 함수입니다. 이 함수의 결과는 각 그룹 조건에 대해 별도로 GROUP BY한 결과를 UNION ALL한 결과와 동일합니다. Grouping Sets 함수는 하나의 SQL문에 의해 여러 개의 그룹 조건을 한꺼번에 지정하여 복잡한 그룹 처리 과정을 단순하게 구성할 수 있습니다.
Grouping sets 함수 사용이 불가능한 과거 버전 DBMS에서는 복잡한 union all 연산자를 사용하여 기술하던 것을 이 함수를 사용하게 되면 간단하게 한 문장 안에서 해결할 수 있어, 검색시 효율성이 증대 된다. 다시 말해서, Grouping Sets 함수를 사용하면, group by …union all 을 사용한 것보다 SQL 문이 간단해지고 또한 실행시 성능이 빨라집니다.
예제 테이블 소개
- EMP 테이블
EMP 테이블은 오라클 DBMS를 설치시 샘플스키마인 SCOTT유저의 메인 테이블중 하나로 아래와 같이 구성되어 있습니다. EMP 테이블중 DEPTNO, EMPNO, SAL칼럼만 가지고 테스트해봅니다.
| DEPTNO | EMPNO | SAL |
|---|---|---|
| 10 | 7934 | 1300 |
| 10 | 7839 | 5000 |
| 10 | 7782 | 2450 |
| 20 | 7902 | 3000 |
| 20 | 7876 | 1100 |
| 20 | 7788 | 3000 |
| 20 | 7566 | 2975 |
| 20 | 7369 | 800 |
| 30 | 7900 | 950 |
| 30 | 7844 | 1500 |
| 30 | 7698 | 2850 |
| 30 | 7654 | 1250 |
| 30 | 7521 | 1250 |
| 30 | 7499 | 1600 |
가. GROUPING SETS 함수문법
이 함수의 기본 문법은 아래와 같으며, ROLLUP, CUBE 의 기능을 전부 사용할수 있으며 GROUP BY절에 ROLLUP과 CUBE함수룰 포함할수 있습니다.
SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)
FROM 테이블명
WHERE 조건
GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...[GROUPING SETS (컬럼명,컬럼명, ...), ...]
HAVING 그룹조건
ORDER BY 컬럼명 또는 위치번호
1. 실전예제
UNION ALL은 2번 테이블에 접근하지만 GROUPING SETS는 오라클 옵티마이저가 한번만 테이블에 접근하여 데이타를 처리합니다.
- UNION ALL로 구현시
select deptno,job,null as mgr,sum(sal) from emp group by deptno,job UNION ALL select deptno,null as JOB,mgr,sum(sal) from emp group by deptno,mgr; - GROUPING SETS로 구현시
select deptno, job, manager, sum(sal) from emp group by GROUPING SETS((deptno,job),(deptno,mgr));
| DEPTNO | JOB | MANAGER | SUM(SAL) |
|---|---|---|---|
| 20 | CLERK | 1900 | |
| 30 | SALESMAN | 5600 | |
| 20 | MANAGER | 2975 | |
| 30 | CLERK | 950 | |
| 10 | PRESIDENT | 5000 | |
| 30 | MANAGER | 2850 | |
| 10 | CLERK | 1300 | |
| 10 | MANAGER | 2450 | |
| 20 | ANALYST | 6000 | |
| 20 | 7839 | 2975 | |
| 10 | 7839 | 2450 | |
| 30 | 7698 | 6550 | |
| 20 | 7566 | 6000 | |
| 10 | 7782 | 1300 | |
| 20 | 7902 | 800 | |
| 10 | 5000 | ||
| 30 | 7839 | 2850 | |
| 20 | 7788 | 1100 |
2. 실전예제 2
실전예제 테이블에서 deptno + job별 사람수와 deptno별 사람수, job별 사람수를 한번에 구하라.
select deptno,job,count(*)
from emp
group by grouping sets((deptno, job), deptno, job);
| DEPTNO | JOB | COUNT(*) |
|---|---|---|
| 10 | CLERK | 1 |
| 20 | CLERK | 2 |
| 30 | CLERK | 1 |
| 20 | ANALYST | 2 |
| 10 | MANAGER | 1 |
| 20 | MANAGER | 1 |
| 30 | MANAGER | 1 |
| 30 | SALESMAN | 4 |
| 10 | PRESIDENT | 1 |
| CLERK | 4 | |
| ANALYST | 2 | |
| MANAGER | 3 | |
| SALESMAN | 4 | |
| PRESIDENT | 1 | |
| 30 | 6 | |
| 20 | 5 | |
| 10 | 3 |
3. 실전예제 3
2차원 ROLLUP과 동일하게 값이 나오도록 GROUPING SETS 함수를 만들어봅니다. 표 1-1를 참고하여 만들어봅니다.
- ROLLUP으로 구현시
select decode(grouping(deptno),1,'소계', deptno) as deptno, empno, sum(sal) from emp<br/> group by ROLLUP(deptno, empno); - GROUPING SETS 로 구현시
select decode(grouping(deptno),1,'소계', deptno) as deptno, empno, sum(sal) from emp group by GROUPING SETS((deptno, empno), deptno, ( ) );
| DEPTNO | EMPNO | SUM(SAL) |
|---|---|---|
| 10 | 7782 | 2450 |
| 10 | 7839 | 5000 |
| 10 | 7934 | 1300 |
| 소계 | 8750 | |
| 20 | 7369 | 800 |
| 20 | 7566 | 2975 |
| 20 | 7788 | 1100 |
| 20 | 7876 | 1100 |
| 20 | 7902 | 3000 |
| 소계 | 8975 | |
| 30 | 7900 | 950 |
| 30 | 7499 | 1600 |
| 30 | 7521 | 1250 |
| 30 | 7654 | 1250 |
| 30 | 7698 | 2850 |
| 30 | 7844 | 1500 |
| 소계 | 9400 | |
| 총합계 | 27125 |
나. GROUPING SETS와 ROLLUP, CUBE과의 관계
1. GROUPING SETS과 관계설명
아래의 표 1-1을 보면 일반적인 GROUP BY UNION ALL절과 ROLLUP, CUBE, GROUPING SETS함수와의 관계도를 보여주고 있습니다.
- 표 1-1 GROUP BY UNION ALL절과 GROUPING SETS 함수관계
| composite column 문의 경우 | group by 문의 경우 |
|---|---|
| group by grouping sets(a,b,c) | group by a union all group by union all group by c |
| group by grouping sets(a,b,(b,c)) | group by a union all group by b union all group by b,c |
| group by grouping sets((a,b,c)) | group by a,b,c |
| group by grouping sets(a,rollup(b,c)) | group by a union all group by rollup(b,c) |
| group by rollup(a,b,c) | group by (a,b,c) union all group by (a,b)union all group by (a) union all group by () |
| group by cube(a,b,c) | group by (a,b,c) union all group by (a,b) union all group by (a,c) union all group by (b,c) union all group by (a) union all group by (b) union all group by (c) union all group by () |
2. 실전예제
Grouping sets함수와 Rollup을 동시에 사용하여 원하는 대로 데이터를 조회할수 있습니다. 여기에서 Grouping sets함수안에 Rollup이 들어가서 사용될수 있으나 Rollup 안에 Grouping sets함수가 들어갈수 없음을 주의합니다. 아래의 sql과 같이 Grouping sets와 Rollup을 동시에 사용한 기술을 Concatenated groupings 라고 부르며, Rollup안의 칼럼대로 Sub/All Total을 구하고, (corp_nm) 칼럼을 기준으로 Sub total을 한번 더 구해주는것입니다. (실전예제 2 칼럼을 참고하시기 바랍니다)
SELECT gubun, area_nm, dept_nm, corp_nm, sum(sal)
FROM roll
GROUP BY grouping sets(ROLLUP(gubun, area_nm, dept_nm, corp_nm),(corp_nm))
ORDER BY 1,2,3,4;
| GUBUN | AREA_NM | DEPT_NM | CORP_NM | SUM(SAL) |
|---|---|---|---|---|
| 구품관리 | 수도권 | 경기지사 | 다온네트웍 | 1700000 |
| 구품관리 | 수도권 | 경기지사 | 이온네트웍 | 700000 |
| 구품관리 | 수도권 | 경기지사 | 정말네트웍 | 600000 |
| 구품관리 | 수도권 | 경기지사 | 3000000 | |
| 구품관리 | 수도권 | 수도지사 | 다온네트웍 | 2100000 |
| 구품관리 | 수도권 | 수도지사 | 정말네트웍 | 800000 |
| 구품관리 | 수도권 | 수도지사 | 2900000 | |
| 구품관리 | 수도권 | 5900000 | ||
| 구품관리 | 영남권 | 부산지사 | 다온네트웍 | 2100000 |
| 구품관리 | 영남권 | 부산지사 | 정말네트웍 | 2100000 |
| 구품관리 | 영남권 | 부산지사 | 4200000 | |
| 구품관리 | 영남권 | 4200000 | ||
| 구품관리 | 10100000 | |||
| 신품관리 | 수도권 | 경기지사 | 정말네트웍 | 700000 |
| 신품관리 | 수도권 | 경기지사 | 700000 | |
| 신품관리 | 수도권 | 700000 | ||
| 신품관리 | 충남권 | 충청지사 | 다온네트웍 | 1700000 |
| 신품관리 | 충남권 | 충청지사 | 이온네트웍 | 2800000 |
| 신품관리 | 충남권 | 충청지사 | 정말네트웍 | 100000 |
| 신품관리 | 충남권 | 충청지사 | 4600000 | |
| 신품관리 | 충남권 | 4600000 | ||
| 신품관리 | 5300000 | |||
| 다온네트웍 | 7600000 | |||
| 이온네트웍 | 3500000 | |||
| 정말네트웍 | 4300000 | |||
| 15400000 |
SELECT gubun, area_nm, dept_nm, corp_nm, sum(sal)
FROM roll
GROUP BY grouping sets(ROLLUP(gubun, area_nm, dept_nm, corp_nm),(dept_nm))<
ORDER BY 1,2,3,4;
| GUBUN | AREA_NM | DEPT_NM | CORP_NM | SUM(SAL) |
|---|---|---|---|---|
| 구품관리 | 수도권 | 경기지사 | 다온네트웍 | 1700000 |
| 구품관리 | 수도권 | 경기지사 | 이온네트웍 | 700000 |
| 구품관리 | 수도권 | 경기지사 | 정말네트웍 | 600000 |
| 구품관리 | 수도권 | 경기지사 | 3000000 | |
| 구품관리 | 수도권 | 수도지사 | 다온네트웍 | 2100000 |
| 구품관리 | 수도권 | 수도지사 | 정말네트웍 | 800000 |
| 구품관리 | 수도권 | 수도지사 | 2900000 | |
| 구품관리 | 수도권 | 5900000 | ||
| 구품관리 | 영남권 | 부산지사 | 다온네트웍 | 2100000 |
| 구품관리 | 영남권 | 부산지사 | 정말네트웍 | 2100000 |
| 구품관리 | 영남권 | 부산지사 | 4200000 | |
| 구품관리 | 영남권 | 4200000 | ||
| 구품관리 | 10100000 | |||
| 신품관리 | 수도권 | 경기지사 | 정말네트웍 | 700000 |
| 신품관리 | 수도권 | 경기지사 | 700000 | |
| 신품관리 | 수도권 | 700000 | ||
| 신품관리 | 충남권 | 충청지사 | 다온네트웍 | 1700000 |
| 신품관리 | 충남권 | 충청지사 | 이온네트웍 | 2800000 |
| 신품관리 | 충남권 | 충청지사 | 정말네트웍 | 100000 |
| 신품관리 | 충남권 | 충청지사 | 4600000 | |
| 신품관리 | 충남권 | 4600000 | ||
| 신품관리 | 5300000 | |||
| 경기지사 | 3700000 | |||
| 부산지사 | 4200000 | |||
| 수도지사 | 2900000 | |||
| 충청지사 | 4600000 | |||
| 15400000 |
SELECT gubun, area_nm, dept_nm, corp_nm, sum(sal)
FROM roll
GROUP BY grouping sets(ROLLUP(gubun, area_nm, dept_nm, corp_nm),(area_nm))
ORDER BY 1,2,3,4;
| GUBUN | AREA_NM | DEPT_NM | CORP_NM | SUM(SAL) |
|---|---|---|---|---|
| 구품관리 | 수도권 | 경기지사 | 다온네트웍 | 1700000 |
| 구품관리 | 수도권 | 경기지사 | 이온네트웍 | 700000 |
| 구품관리 | 수도권 | 경기지사 | 정말네트웍 | 600000 |
| 구품관리 | 수도권 | 경기지사 | 3000000 | |
| 구품관리 | 수도권 | 수도지사 | 다온네트웍 | 2100000 |
| 구품관리 | 수도권 | 수도지사 | 정말네트웍 | 800000 |
| 구품관리 | 수도권 | 수도지사 | 2900000 | |
| 구품관리 | 수도권 | 5900000 | ||
| 구품관리 | 영남권 | 부산지사 | 다온네트웍 | 2100000 |
| 구품관리 | 영남권 | 부산지사 | 정말네트웍 | 2100000 |
| 구품관리 | 영남권 | 부산지사 | 4200000 | |
| 구품관리 | 영남권 | 4200000 | ||
| 구품관리 | 10100000 | |||
| 신품관리 | 수도권 | 경기지사 | 정말네트웍 | 700000 |
| 신품관리 | 수도권 | 경기지사 | 700000 | |
| 신품관리 | 수도권 | 700000 | ||
| 신품관리 | 충남권 | 충청지사 | 다온네트웍 | 1700000 |
| 신품관리 | 충남권 | 충청지사 | 이온네트웍 | 2800000 |
| 신품관리 | 충남권 | 충청지사 | 정말네트웍 | 100000 |
| 신품관리 | 충남권 | 충청지사 | 4600000 | |
| 신품관리 | 충남권 | 4600000 | ||
| 신품관리 | 5300000 | |||
| 수도권 | 6600000 | |||
| 영남권 | 4200000 | |||
| 충남권 | 4600000 | |||
| 15400000 |
다. 결언
GROUPING SETS 함수는 ROLLUP, CUBE와 동일한 기능을 가지고 있으며, 오히려 더 광범위하고 쉽게 사용할수 있는 특징이 있습니다. 오라클 DBMS에서는 GROUPING SETS 함수의 실행계획은 ROLLUP과 매우 유사하게 나오며 테이블에 한번만 접근하는 특징이 있어 성능상 유리하게 사용할수 있습니다. 그리고 실행계획은 복잡한 데이타 처리로직을 가진 sql이라면 임시 테이블을 메모리에 만든후 멀티테이블 인서트 방식을 혼용하여 데이타를 처리하는 복잡한 방식과, GSTR(Grouping Sets TO Rollup)기능을 혼용하여 실행계획이 나오는 경우가 많습니다. 여기는 튜닝을 논하는 칼럼이 아니므로 다음기회에 포스팅하기로 하겠습니다.