SQL: ROLLUP 기본사용법

https://engineering-skcc.github.io/sql/ROLLUP%EA%B8%B0%EB%B3%B8/

ROLLUP 기본사용법

 

ROLLUP 기본사용법

 Updated: March 17, 2020

 List

• ROLLUP이란?
• ROLLUP의 기본사용법
  • 가. 1차원 Rollup
  • 나. 2차원 ROLLUP
  • 다. Partial Rollup
  • 라. Grouping On Composite Columns

ROLLUP이란?
Permalink

ROLLUP은 ANSI SQL 99표준에서 가장 많이 사용하는 함수로 이것을 잘 이용하면 쉽게 최적의 성능을 보장하는 SQL을 만들수 있습니다. ROLLUP을 잘 사용하기 위해서는 UNION ALL, WITH, ROLLUP, DECODE절간의 형태변환 개념을 이해해야 하는데 이부분은 다음 기회에 소개하겠습니다.

ROLLUP의 기본형태는 ROLLUP안에 들어가는 칼럼 개수로 1차원, 2차원, 3차원 ROLLUP등으로 나눌수 있으며, 그외 PARTIAL, GROUPING ON COMPOSITE 형태가 많이 사용되고 있습니다. 아래의 자료를 가지고 먼저 기본형태를 이해하고 다음에 실전예제를 풀어보면 ROLLUP을 쉽게 이해할수 있습니다.

ROLLUP은 UNION ALL로 만들어진 라인수가 많고 테이블에 중복접근하는 SQL을 가장 간략하게, 그리고 테이블에 한번만 접근하여 데이타를 조회할수 있습니다. 그러므로 성능상 최고의 위치에 있는 기술중 하나입니다.

ROLLUP의 기본사용법Permalink

가. 1차원 Rollup
Permalink

1차원은 GROUP BY 절의 ROLLUP에 하나의 칼럼이 들어간 경우를 이야기한것입니다. 이는 총계가 생성되어 나오는 경우가 되며 가장 많이 사용되는 단순한 형태의 예제입니다

select empno, sum(sal)
from emp
group by rollup(empno);

EMPNOSUM(SAL) 

7369	800
7499	1600
7521	1250
7566	2975
7654	1250
7698	2850
7782	2450
7788	3000
7839	5000
7844	1500
7876	1100
7900	950
7902	3000
7934	1300
	29025	–생성값

나. 2차원 ROLLUP
Permalink

위의 예제에서는 ROLLUP 안에 2개의 칼럼이 들어가 있습니다. 이런경우 DEPTNO별 총계와 전체 총계가 나타난다. 즉 ROLLUP(DEPTNO, EMPNO)에서 가장 안쪽에 있는 칼럼인 DEPTNO별로 총계가 한번 생성되고 전체 총계가 한번 더 생성되는 케이스입니다

select deptno,empno, sum(sal)
from emp
group by rollup(deptno, empno);

DEPTNOEMPNOSUM(SAL)

10	7782	2450
10	7839	5000
10	7934	1300
10		8750
20	7369	800
20	7566	2975
20	7788	1100
20	7876	1100
20	7902	3000
20		8975
30	7900	950
30	7499	1600
30	7521	1250
30	7654	1250
30	7698	2850
30	7844	1500
30		9400
		27125

다. Partial Rollup
Permalink

부분(Partial) Rollup형태는 group by절의 rollup에서 제외된 칼럼이 앞쪽에 위치하는 경우입니다. 이런경우 제외된 deptno 칼럼d을 기준으로 서브 총계가 한번 생성되고 전체 총계는 나오지 않는 특징이 있습니다. 많이 사용되는 방법이니 잘 기억하길 바라며 응용예제에서 어떻게 사용되는지 살펴보면 쉽게 이해할수 있는 방법입니다

select deptno, empno, sum(sal)
from emp
group by  deptno, rollup(empno);

DEPTNOEMPNOSUM(SAL)

10	7782	2450
10	7839	5000
10	7934	1300
10		8750
20	7369	800
20	7566	2975
20	7788	1100
20	7876	1100
20	7902	3000
20		8975
30	7900	950
30	7499	1600
30	7521	1250
30	7654	1250
30	7698	2850
30	7844	1500
30		9400

라. Grouping On Composite Columns
Permalink

이 방법은 여러 개의 칼럼이 select절에 위치하는데 오로지 내가 필요한 자료는 전체 총계만 필요한경우 많이 사용되는 방법입니다. rollup안에서 deptno, empno, ename을 ( )로 한번 더 묶어주면 Deptno||empno||ename 형태로 하나의 칼럼으로 인식하게 됩니다. 즉 1차원이 된다는 이야기입니다. 그러므로 전체 총계가 1번만 생성됩니다.

select deptno,empno, ename, sum(sal)
from emp 
group by rollup((deptno,empno,ename));

DEPTNOEMPNOENAMESUM(SAL)

10	7782	CLARK	2450
10	7839	KING	5000
10	7934	MILLER	1300
20	7369	SMITH	800
20	7566	JONES	2975
20	7788	SCOTT	1100
20	7876	ADAMS	1100
20	7902	FORD	3000
30	7900	JAMES	950
30	7499	ALLEN	1600
30	7521	WARD	1250
30	7654	MARTIN	1250
30	7698	BLAKE	2850
30	7844	TURNER	1500
			27125