ROLLUP의 실전예제2
실전 예제1에 이어서 두번째 실전예제를 아래와 같이 설명합니다. 실전예제 1번 칼럼에서 실전예제 테이블 생성 스크립트를 이용하여 테이블을 생성한후 실전예제 1번부터 차근차근 테스트해봅니다
라. 지사별 기준으로 SUB/ALL TOAL 보여주기
실전예제 1의 의 포스팅과 이어서 봐주기 바랍니다. 실전예제 1의 “다” 항과 동일한 개념이나 총합계를 하나 더 구해주는 조건입니다. group by절의 rollup에서 제외가 된 2개의 칼럼을 ( ) 로 하나로 묶어 rollup절 안으로 넣어준다. 즉 2차원 rollup 형태로 만드므로 sub/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;
| GUBUN |
AREA_NM |
DEPT_NM |
KUN_NO |
CORP_NM |
SUM(SAL) |
| 구품관리 |
수도권 |
경기지사 |
1군 |
다온네트웍 |
800000 |
| 구품관리 |
수도권 |
경기지사 |
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 |
| |
|
|
|
|
15400000 |
마. DEPT_NM, CORP_NM, KUN_NO을 기준으로 SUB/ALL TOAL 을 전부 보여주기
이번에는 gubun, area_nm 를 기준으로 그안에 있는 SUB/ALL 합계를 전부 보여주는 조건입니다.
Composite 처리된 rollup안의 값을 ( ) 를 제거하고 조회합니다.
SELECT gubun, area_nm, dept_nm, kun_no, corp_nm, sum(sal)
FROM roll
GROUP BY gubun, area_nm, ROLLUP(dept_nm, corp_nm, kun_no)
ORDER BY 1, 2, 3;
| GUBUN |
AREA_NM |
DEPT_NM |
KUN_NO |
CORP_NM |
SUM(SAL) |
| 구품관리 |
수도권 |
경기지사 |
1군 |
다온네트웍 |
800000 |
| 구품관리 |
수도권 |
경기지사 |
2군 |
다온네트웍 |
900000 |
| 구품관리 |
수도권 |
경기지사 |
|
다온네트웍 |
1700000 |
| 구품관리 |
수도권 |
경기지사 |
1군 |
이온네트웍 |
600000 |
| 구품관리 |
수도권 |
경기지사 |
2군 |
이온네트웍 |
100000 |
| 구품관리 |
수도권 |
경기지사 |
|
이온네트웍 |
700000 |
| 구품관리 |
수도권 |
경기지사 |
2군 |
정말네트웍 |
600000 |
| 구품관리 |
수도권 |
경기지사 |
|
정말네트웍 |
600000 |
| 구품관리 |
수도권 |
경기지사 |
|
|
3000000 |
| 구품관리 |
수도권 |
수도지사 |
1군 |
다온네트웍 |
1100000 |
| 구품관리 |
수도권 |
수도지사 |
2군 |
다온네트웍 |
1000000 |
| 구품관리 |
수도권 |
수도지사 |
|
다온네트웍 |
2100000 |
| 구품관리 |
수도권 |
수도지사 |
1군 정말네트웍 |
500000 |
|
| 구품관리 |
수도권 |
수도지사 |
2군 |
정말네트웍 |
300000 |
| 구품관리 |
수도권 |
수도지사 |
|
정말네트웍 |
800000 |
| 구품관리 |
수도권 |
수도지사 |
|
|
2900000 |
| 구품관리 |
수도권 |
|
|
|
5900000 |
| 신품관리 |
충남권 |
충청지사 |
|
다온네트웍 |
1700000 |
| 신품관리 |
충남권 |
충청지사 |
1군 |
이온네트웍 |
1900000 |
| 신품관리 |
충남권 |
충청지사 |
2군 |
이온네트웍 |
900000 |
| 신품관리 |
충남권 |
충청지사 |
|
이온네트웍 |
2800000 |
| 신품관리 |
충남권 |
충청지사 |
2군 |
정말네트웍 |
100000 |
| 신품관리 |
충남권 |
충청지사 |
|
정말네트웍 |
100000 |
| 신품관리 |
충남권 |
충청지사 |
|
|
4600000 |
| 신품관리 |
충남권 |
|
|
|
4600000 |
바. 5개의 칼럼을 기준으로 5차원 rollup 전부 보여주기
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;
| GUBUN |
AREA_NM |
DEPT_NM |
KUN_NO |
CORP_NM |
SUM(SAL) |
| 구품관리 |
수도권 |
경기지사 |
1군 |
다온네트웍 |
800000 |
| 구품관리 |
수도권 |
경기지사 |
1군 |
이온네트웍 |
600000 |
| 구품관리 |
수도권 |
경기지사 |
1군 |
|
1400000 |
| 구품관리 |
수도권 |
경기지사 |
2군 |
다온네트웍 |
900000 |
| 구품관리 |
수도권 |
경기지사 |
2군 |
이온네트웍 |
100000 |
| 구품관리 |
수도권 |
경기지사 |
2군 |
정말네트웍 |
600000 |
| 구품관리 |
수도권 |
경기지사 |
2군 |
|
1600000 |
| 구품관리 |
수도권 |
경기지사 |
|
|
3000000 |
| 구품관리 |
수도권 |
수도지사 |
1군 |
다온네트웍 |
1100000 |
| 구품관리 |
수도권 |
수도지사 |
1군 |
정말네트웍 |
500000 |
| 구품관리 |
수도권 |
수도지사 |
1군 |
|
1600000 |
| 구품관리 |
수도권 |
수도지사 |
2군 |
다온네트웍 |
1000000 |
| 구품관리 |
수도권 |
수도지사 |
2군 |
정말네트웍 |
300000 |
| 구품관리 |
수도권 |
수도지사 |
2군 |
|
1300000 |
| 구품관리 |
수도권 |
수도지사 |
|
|
2900000 |
| 구품관리 |
수도권 |
|
|
|
5900000 |
| 구품관리 |
영남권 |
부산지사 |
1군 |
다온네트웍 |
1100000 |
| 구품관리 |
영남권 |
부산지사 |
1군 |
정말네트웍 |
1400000 |
| 구품관리 |
영남권 |
부산지사 |
1군 |
|
2500000 |
| 구품관리 |
영남권 |
부산지사 |
2군 |
다온네트웍 |
1000000 |
| 구품관리 |
영남권 |
부산지사 |
2군 |
정말네트웍 |
700000 |
| 구품관리 |
영남권 |
부산지사 |
2군 |
|
1700000 |
| 구품관리 |
영남권 |
부산지사 |
|
|
4200000 |
| 구품관리 |
영남권 |
|
|
|
4200000 |
| 구품관리 |
|
|
|
|
10100000 |
| 신품관리 |
수도권 |
경기지사 |
1군 |
정말네트웍 |
700000 |
| 신품관리 |
수도권 |
경기지사 |
1군 |
|
700000 |
| 신품관리 |
수도권 |
경기지사 |
|
|
700000 |
| 신품관리 |
수도권 |
|
|
|
700000 |
| 신품관리 |
충남권 |
충청지사 |
1군 |
다온네트웍 |
800000 |
| 신품관리 |
충남권 |
충청지사 |
1군 |
이온네트웍 |
1900000 |
| 신품관리 |
충남권 |
충청지사 |
1군 |
|
2700000 |
| 신품관리 |
충남권 |
충청지사 |
2군 |
다온네트웍 |
900000 |
| 신품관리 |
충남권 |
충청지사 |
2군 |
이온네트웍 |
900000 |
| 신품관리 |
충남권 |
충청지사 |
2군 |
정말네트웍 |
100000 |
| 신품관리 |
충남권 |
충청지사 |
2군 |
|
1900000 |
| 신품관리 |
충남권 |
충청지사 |
|
|
4600000 |
| 신품관리 |
충남권 |
|
|
|
4600000 |
| 신품관리 |
|
|
|
|
5300000 |
| |
|
|
|
|
15400000 |
사. Grouping Sets와 Rollup의 혼용문제
Grouping sets함수와 rollup을 동시에 사용하여 원하는 대로 데이터를 조회할수 있다. 여기에서 grouping sets함수안에 rollup이 들어가서 사용될수 있으나 rollup 안에 Grouping sets함수가 들어갈수 없음을 주의합니다.
아래의 sql과 같이 grouping sets와 rollup을 동시에 사용한 기술을 Concatenated groupings 라고 부르며, rollup안의 칼럼대로 sub/all total을 구하고, (corp_nm) 칼럼을 기준으로 sub total을 한번 더 구해주는것입니다.
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 |
이제까지 이야기한 ROLLUP의 기본형태를 응용하여 같이 사용해봅니다.
아래의 SQL이 이해가 되는가요? composite column형태로 (area_nm, dept_nm, corp_nm)을 묶어 2차원의 rollup을 구하고 있다. 그러므로 2개의 서브/all total이 구해지고 grouping sets 함수안에 corp_nm 칼럼을 하나 더 넣어 corp_nm 칼럼 기준으로 sub total을 조회합니다.
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 |
| 구품관리 |
수도권 |
수도지사 |
다온네트웍 |
2100000 |
| 구품관리 |
수도권 |
수도지사 |
정말네트웍 |
800000 |
| 구품관리 |
영남권 |
부산지사 |
다온네트웍 |
2100000 |
| 구품관리 |
영남권 |
부산지사 |
정말네트웍 |
2100000 |
| 구품관리 |
|
|
|
10100000 |
| 신품관리 |
수도권 |
경기지사 |
정말네트웍 |
700000 |
| 신품관리 |
충남권 |
충청지사 |
다온네트웍 |
1700000 |
| 신품관리 |
충남권 |
충청지사 |
이온네트웍 |
2800000 |
| 신품관리 |
충남권 |
충청지사 |
정말네트웍 |
100000 |
| 신품관리 |
|
|
|
5300000 |
| |
|
|
다온네트웍 |
7600000 |
| |
|
|
이온네트웍 |
3500000 |
| |
|
|
정말네트웍 |
4300000 |
| |
|
|
|
15400000 |
