ROLLUP 실전예제1
Updated:
ROLLUP의 실전예제 테이블 생성
rollup은 안시 sql99의 대표적인 함수로 거의 모든 DBMS에서 제공하고 있습니다. 이 함수를 잘 이용하면 동일테이블을 몇번씩 접근해야 하는 SQL들을 단 한번만의 접근으로 처리할수 있는 강력한 기능을 제공하고 있습니다. 아래는 ROLLUP의 기본형태를 이용한 실습예제입니다. ROLL 테이블을 아래의 스크립트로 만들고 실습해봅니다.
create table roll
(gubun varchar2(10),
area_nm varchar2(10),
dept_nm varchar2(20),
kun_no varchar2(10),
corp_nm varchar2(20),
sal number(10));
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘2군’,’다온네트웍’,100000);
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘1군’,’다온네트웍’,300000);
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘2군’,’다온네트웍’,900000);
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘1군’,’다온네트웍’,800000);
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘2군’,’정말네트웍’,100000);
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘1군’,’정말네트웍’,300000);
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘2군’,’정말네트웍’,200000);
insert into roll values(‘구품관리’,’수도권’, ‘수도지사’, ‘1군’,’정말네트웍’,200000);
insert into roll values(‘구품관리’,’수도권’, ‘경기지사’, ‘2군’,’이온네트웍’,100000);
insert into roll values(‘구품관리’,’수도권’, ‘경기지사’, ‘1군’,’이온네트웍’,300000);
insert into roll values(‘구품관리’,’수도권’, ‘경기지사’, ‘2군’,’다온네트웍’,900000);
insert into roll values(‘구품관리’,’수도권’, ‘경기지사’, ‘1군’,’다온네트웍’,800000);
insert into roll values(‘구품관리’,’수도권’, ‘경기지사’, ‘2군’,’정말네트웍’,100000);
insert into roll values(‘구품관리’,’수도권’, ‘경기지사’, ‘1군’,’이온네트웍’,300000);
insert into roll values(‘구품관리’,’수도권’, ‘경기지사’, ‘2군’,’정말네트웍’,500000);
insert into roll values(‘신품관리’,’수도권’, ‘경기지사’, ‘1군’,’정말네트웍’,700000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘2군’,’다온네트웍’,100000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘1군’,’다온네트웍’,300000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘2군’,’다온네트웍’,900000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘1군’,’다온네트웍’,800000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘2군’,’정말네트웍’,100000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘1군’,’정말네트웍’,500000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘2군’,’정말네트웍’,600000);
insert into roll values(‘구품관리’,’영남권’, ‘부산지사’, ‘1군’,’정말네트웍’,900000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘2군’,’이온네트웍’,100000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘1군’,’이온네트웍’,800000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘2군’,’다온네트웍’,900000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘1군’,’다온네트웍’,800000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘2군’,’정말네트웍’,100000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘1군’,’이온네트웍’,300000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘2군’,’이온네트웍’,800000);
insert into roll values(‘신품관리’,’충남권’, ‘충청지사’, ‘1군’,’이온네트웍’,800000);
commit;
ROLLUP의 실전예제 테이블 설명
테이블의 구성은 아래와 같습니다. GUBUN 칼럼은 구품관리, 신품관리 2개의 값으로 구분되고, AREA_NM은 권역을 나타내는 칼럼으로 수도권, 영남권등의 몇 개의 값으로 되어있습니다. DEPT_NM은 지사를 나타내며 권역별로 몇 개의 지사로 나뉘어집니다. 즉 이런형태의 테이블내의 데이터들이 점점 깊어지는 구조를 가지고 있는 테이블이 ROLLUP을 사용하여 값을 조회하는데 안성맞춤입니다.
| GUBUN | AREA_NM | DEPT_NM | KUN_NO | CORP_NM | SAL |
|---|---|---|---|---|---|
| 구품관리 | 수도권 | 수도지사 | 2군 | 다온네트웍 | 100000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 다온네트웍 | 300000 |
| 구품관리 | 수도권 | 수도지사 | 2군 | 다온네트웍 | 900000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 다온네트웍 | 800000 |
| 구품관리 | 수도권 | 수도지사 | 2군 | 정말네트웍 | 100000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 정말네트웍 | 300000 |
| 구품관리 | 수도권 | 수도지사 | 2군 | 정말네트웍 | 200000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 정말네트웍 | 200000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 이온네트웍 | 100000 |
| 구품관리 | 수도권 | 경기지사 | 1군 | 이온네트웍 | 300000 |
| 구품관리 | 수도권 | 경기지사 | 1군 | 이온네트웍 | 300000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 정말네트웍 | 500000 |
| 구품관리 | 수도권 | 경기지사 | 1군 | 정말네트웍 | 700000 |
| 구품관리 | 영남권 | 부산지사 | 2군 | 다온네트웍 | 100000 |
| 구품관리 | 영남권 | 부산지사 | 2군 | 정말네트웍 | 600000 |
| 구품관리 | 영남권 | 부산지사 | 1군 | 정말네트웍 | 900000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 이온네트웍 | 100000 |
| 신품관리 | 충남권 | 충청지사 | 1군 | 이온네트웍 | 800000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 다온네트웍 | 900000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 이온네트웍 | 800000 |
| 신품관리 | 충남권 | 충청지사 | 1군 | 이온네트웍 | 800000 |
ROLLUP의 실전예제
가. 구품/신품관리 기준으로 Sub TOTAL 보여주기
ROLL 테이블에서 구분 칼럼을 기준으로 구품관리/신품관리 합계를 구하는 SQL입니다.
GROUP BY절을 보면 Partial rollup 기술을 사용하여 gubun칼럼이 rollup의 앞에 나와있습니다. 즉 gubun칼럼을 기준으로 서브토탈을 셍성하라는 이야기입니다.
SELECT gubun, area_nm, dept_nm, kun_no, corp_nm, sum(sal)
FROM roll
GROUP BY gubun, ROLLUP ((area_nm, dept_nm, corp_nm, kun_no))
ORDER BY 1, 2, 3;
| GUBUN | AREA_NM | DEPT_NM | KUN_NO | CORP_NM | SAL |
|---|---|---|---|---|---|
| 구품관리 | 수도권 | 경기지사 | 1군 | 다온네트웍 | 800000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 다온네트웍 | 900000 |
| 구품관리 | 수도권 | 경기지사 | 1군 | 이온네트웍 | 600000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 정말네트웍 | 600000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 이온네트웍 | 100000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 정말네트웍 | 500000 |
| 구품관리 | 수도권 | 수도지사 | 2군 | 정말네트웍 | 300000 |
| 구품관리 | 수도권 | 수도지사 | 2군 | 다온네트웍 | 1000000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 다온네트웍 | 1100000 |
| 구품관리 | 영남권 | 부산지사 | 1군 | 다온네트웍 | 1100000 |
| 구품관리 | 영남권 | 부산지사 | 2군 | 다온네트웍 | 1000000 |
| 구품관리 | 영남권 | 부산지사 | 2군 | 정말네트웍 | 700000 |
| 구품관리 | 영남권 | 부산지사 | 1군 | 정말네트웍 | 1400000 |
| 구품관리 | 10100000 | ||||
| 신품관리 | 수도권 | 경기지사 | 1군 | 정말네트웍 | 700000 |
| 신품관리 | 충남권 | 충청지사 | 1군 | 다온네트웍 | 800000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 다온네트웍 | 900000 |
| 신품관리 | 충남권 | 충청지사 | 1군 | 이온네트웍 | 1900000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 정말네트웍 | 100000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 이온네트웍 | 900000 |
| 신품관리 | 5300000 |
나. 구품/신품관리를기준으로 SUB/ALL TOTAL 보여주기
이번에는 구분칼럼을 기준으로 서브토탈을 내고 총합계도 같이 구하는 방법입니다.
이때 rollup의 앞에 있던 GUBNUN 칼럼을 ROLLUP절로 이동시켜 2차원 ROLLUP을 구성합니다.
(area_nm, dept_nm, kun_no, corp_nm) 칼럼을 Grouping on Composite 형태로 묶여있으므로 1개의 칼럼으로 생각하면 된다. 또한 실행계획을 보게 되면 테이블에 1번만 접근한 것을 알수 있습니다.
즉 union all을 여러 번 사용하여 동일한 결과값을 구할수 있겠지만 테이블에 여러 번 접근하므로 속돡 떨어집니다. Rollup은 기본적으로 1번만 접근하는 것을 원칙으로 합니다.
그러나 rollup도 복잡하게 사용하면 with절에 materialize 힌트를 사용한것처럼 temp테이블을 만들고 메모리상에서 여러 번 처리하도록 되어 있습니다. 그래도 union all 형태보다는 속도가 좋을거라 생각합니다.
SELECT gubun, area_nm, dept_nm, kun_no, corp_nm, sum(sal)
FROM roll
GROUP BY ROLLUP (gubun, (area_nm, dept_nm, kun_no, corp_nm))
ORDER BY 1, 2, 3;
Execution Plan
--------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=32 Bytes=2K)
1 0 SORT (GROUP BY ROLLUP) (Cost=4 Card=32 Bytes=2K)
2 1 TABLE ACCESS (FULL) OF 'ROLL' (TABLE) (Cost=3 Card=32 Bytes=2K)
| GUBUN | AREA_NM | DEPT_NM | KUN_NO | CORP_NM | SAL | |
|---|---|---|---|---|---|---|
| 구품관리 | 수도권 | 경기지사 | 1군 | 다온네트웍 | 800000 | |
| 구품관리 | 수도권 | 경기지사 | 2군 | 다온네트웍 | 900000 | |
| 구품관리 | 수도권 | 경기지사 | 1군 | 이온네트웍 | 600000 | |
| 구품관리 | 수도권 | 경기지사 | 2군 | 정말네트웍 | 600000 | |
| 구품관리 | 수도권 | 경기지사 | 2군 | 이온네트웍 | 100000 | |
| 구품관리 | 수도권 | 수도지사 | 1군 | 정말네트웍 | 500000 | |
| 구품관리 | 수도권 | 수도지사 | 2군 | 정말네트웍 | 300000 | |
| 구품관리 | 수도권 | 수도지사 | 2군 | 다온네트웍 | 1000000 | |
| 구품관리 | 수도권 | 수도지사 | 1군 | 다온네트웍 | 1100000 | |
| 구품관리 | 영남권 | 부산지사 | 1군 | 다온네트웍 | 1100000 | |
| 구품관리 | 영남권 | 부산지사 | 2군 | 다온네트웍 | 1000000 | |
| 구품관리 | 영남권 | 부산지사 | 2군 | 정말네트웍 | 700000 | |
| 구품관리 | 영남권 | 부산지사 | 1군 | 정말네트웍 | 1400000 | |
| 구품관리 | 10100000 | |||||
| 신품관리 | 수도권 | 경기지사 | 1군 | 정말네트웍 | 700000 | |
| 신품관리 | 충남권 | 충청지사 | 2군 | 다온네트웍 | 900000 | |
| 신품관리 | 충남권 | 충청지사 | 1군 | 다온네트웍 | 800000 | |
| 신품관리 | 충남권 | 충청지사 | 1군 | 이온네트웍 | 1900000 | |
| 신품관리 | 충남권 | 충청지사 | 2군 | 정말네트웍 | 100000 | |
| 신품관리 | 충남권 | 충청지사 | 2군 | 이온네트웍 | 900000 | |
| 신품관리 | 5300000 | |||||
| 15400000 |
다. 지사별로 SUB TOAL 보여주기
이번에는 지사별로 서브토탈을 구하는것입니다. Gubun, area_nm을 왕따(?)시켜 rollup절 앞에 기술합니다. 그리고 나머지 칼럼은 composite 형태로 하나로 묶어서 처리합니다.
SELECT gubun, area_nm, dept_nm, kun_no, corp_nm, sum(sal)
FROM roll
GROUP BY gubun, area_nm, ROLLUP ((dept_nm, kun_no, corp_nm))
ORDER BY 1, 2, 3;
| GUBUN | AREA_NM | DEPT_NM | KUN_NO | CORP_NM | SAL |
|---|---|---|---|---|---|
| 구품관리 | 수도권 | 경기지사 | 1군 | 다온네트웍 | 800000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 다온네트웍 | 900000 |
| 구품관리 | 수도권 | 경기지사 | 1군 | 이온네트웍 | 600000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 정말네트웍 | 600000 |
| 구품관리 | 수도권 | 경기지사 | 2군 | 이온네트웍 | 100000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 정말네트웍 | 500000 |
| 구품관리 | 수도권 | 수도지사 | 2군 | 정말네트웍 | 300000 |
| 구품관리 | 수도권 | 수도지사 | 2군 | 다온네트웍 | 1000000 |
| 구품관리 | 수도권 | 수도지사 | 1군 | 다온네트웍 | 1100000 |
| 구품관리 | 수도권 | 5900000 | |||
| 구품관리 | 영남권 | 부산지사 | 1군 | 다온네트웍 | 1100000 |
| 구품관리 | 영남권 | 부산지사 | 2군 | 다온네트웍 | 1000000 |
| 구품관리 | 영남권 | 부산지사 | 2군 | 정말네트웍 | 700000 |
| 구품관리 | 영남권 | 부산지사 | 1군 | 정말네트웍 | 1400000 |
| 구품관리 | 영남권 | 4200000 | |||
| 신품관리 | 수도권 | 경기지사 | 1군 | 정말네트웍 | 700000 |
| 신품관리 | 수도권 | 700000 | |||
| 신품관리 | 충남권 | 충청지사 | 1군 | 다온네트웍 | 800000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 다온네트웍 | 900000 |
| 신품관리 | 충남권 | 충청지사 | 1군 | 이온네트웍 | 1900000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 정말네트웍 | 100000 |
| 신품관리 | 충남권 | 충청지사 | 2군 | 이온네트웍 | 900000 |
| 신품관리 | 충남권 | 4600000 |