Oracle Demo schema인 HR을 사용하여 테스트
일반 GROUP BY를 사용하여 부서와 직무로 그룹화 하여 연봉의 합을 구하는 쿼리
SQL> select DEPARTMENT_ID
, JOB_ID
, SUM(salary)
from HR.EMPLOYEES
group by DEPARTMENT_ID, JOB_ID;
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- --------------------
60 IT_PROG 28800
100 FI_ACCOUNT 39600
80 SA_REP 243500
SA_REP 7000
20 MK_REP 6000
110 AC_ACCOUNT 8300
30 PU_MAN 11000
30 PU_CLERK 13900
50 ST_CLERK 55700
10 AD_ASST 4400
40 HR_REP 6500
50 ST_MAN 36400
90 AD_PRES 24000
100 FI_MGR 12008
80 SA_MAN 61000
50 SH_CLERK 64300
20 MK_MAN 13000
70 PR_REP 10000
110 AC_MGR 12008
90 AD_VP 34000
ROLLUP 사용
GROUP BY 결과로 나온 값에 부서별 총 연봉 합과 총 연봉합을 추가로 나타내 준다.
SQL> select DEPARTMENT_ID
, JOB_ID
, SUM(salary)
from HR.EMPLOYEES
group by ROLLUP(DEPARTMENT_ID, JOB_ID);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- --------------------
SA_REP 7000
7000
10 AD_ASST 4400
10 4400
20 MK_MAN 13000
20 MK_REP 6000
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900
40 HR_REP 6500
40 6500
50 ST_MAN 36400
50 SH_CLERK 64300
50 ST_CLERK 55700
50 156400
60 IT_PROG 28800
60 28800
70 PR_REP 10000
70 10000
80 SA_MAN 61000
80 SA_REP 243500
80 304500
90 AD_VP 34000
90 AD_PRES 24000
90 58000
100 FI_MGR 12008
100 FI_ACCOUNT 39600
100 51608
110 AC_MGR 12008
110 AC_ACCOUNT 8300
110 20308
691416
전체 급여 합만 보고 싶을 땐?
ROLLUP 으로 감싼 컬럼들을 다시한번 괄호로 감싸준다.
SQL> select DEPARTMENT_ID
, JOB_ID
, SUM(salary)
from HR.EMPLOYEES
group by ROLLUP((DEPARTMENT_ID, JOB_ID));
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- --------------------
SA_REP 7000
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_MAN 11000
30 PU_CLERK 13900
40 HR_REP 6500
50 ST_MAN 36400
50 SH_CLERK 64300
50 ST_CLERK 55700
60 IT_PROG 28800
70 PR_REP 10000
80 SA_MAN 61000
80 SA_REP 243500
90 AD_VP 34000
90 AD_PRES 24000
100 FI_MGR 12008
100 FI_ACCOUNT 39600
110 AC_MGR 12008
110 AC_ACCOUNT 8300
691416
부서급여 합만 보고 싶을땐?
DEPARTMENT_ID는 GROUP BY 뒤에 기술하고 JOB_ID만 ROLLUP 해준다.
SQL> select DEPARTMENT_ID
, JOB_ID
, SUM(salary)
from HR.EMPLOYEES
group by DEPARTMENT_ID, ROLLUP(JOB_ID);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- --------------------
SA_REP 7000
7000
10 AD_ASST 4400
10 4400
20 MK_MAN 13000
20 MK_REP 6000
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900
40 HR_REP 6500
40 6500
50 ST_MAN 36400
50 SH_CLERK 64300
50 ST_CLERK 55700
50 156400
60 IT_PROG 28800
60 28800
70 PR_REP 10000
70 10000
80 SA_MAN 61000
80 SA_REP 243500
80 304500
90 AD_VP 34000
90 AD_PRES 24000
90 58000
100 FI_MGR 12008
100 FI_ACCOUNT 39600
100 51608
110 AC_MGR 12008
110 AC_ACCOUNT 8300
110 20308
ROLLUP의 원리
위 이미지가 ROLLUP의 원리를 전부 설명해주는데, 각 번호는 해당 칼럼으로 GROUP BY한 결과를 준다는 뜻이다. 첫번째 GROUP BY절 같은 경우는 총 4개의 GROUP을 만든다는 뜻이다.
ROLLUP의 원리
1. ROLLUP의 인자로 들어온 컬럼을 오른쪽부터 하나씩 빼면서 GROUP을 만든다.
2. ()의미는 GROUP이 없는 즉, 전체에 대한 결과를 출력한다는 의미이다. EX(SUM함수 사용하면 전체 SUM구한다는 의미)
3. 괄호로 묶여져 있는 컬럼은 하나로 본다는 뜻
4. ROLLUP 이전에 일반 컬럼과 GROUP BY 한다면, 일반 컬럼은 끝까지 남는다.
GROUP BY ROLLUP(A, B, C) 예시
|
1
2
3
4
5
6
7
|
SELECT
DEPTNO
,JOB
,ENAME
,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB,ENAME);
|
GROUP BY ROLLUP(A, (B, C)) 예시
|
1
2
3
4
5
6
7
|
SELECT
DEPTNO
,JOB
,ENAME
,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,(JOB,ENAME));
|
GROUP BY A, ROLLUP((B, C)) 예시
|
1
2
3
4
5
6
7
|
SELECT
DEPTNO
,JOB
,ENAME
,SUM(SAL)
FROM EMP
GROUP BY DEPTNO,ROLLUP((JOB,ENAME));
|
ROLLUP 특이 예제
여태까지는 기본적으로 ROLLUP이 어떤 원리로 실행되는지 알아봤습니다. 그럼 위에 예제 처럼 결과를 출력할 수 있을까요? 일단, 결과를 보니, 한 칼럼에서 컬럼 이외의 값이 나오기도하고, 급여 컬럼에서는 평균값과 합계값이 같이 나오는 것을 볼 수 있습니다. 어떻게 결과를 이렇게 출력할 수 있을까요? 우선 GROUPING, GROUPING_ID 그리고 숫자 GROUP 추가에 대한 내용을 알아야합니다.
GROUPING
|
1
2
3
4
5
6
7
8
|
SELECT
DEPTNO
,GROUPING(DEPTNO) AS DG
,JOB
,GROUPING(JOB) AS JG
,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
|
GROUPING 함수는 ROLLUP이랑 같이 사용합니다. 해당 칼럼이 ROLLUP 되었을 때 그룹에서 빠져있다면 1을 반환합니다. 즉, GROUP BY ROLLUP 결과로 NULL이 나왔을 때 1을 반환합나다.
GROUPING_ID
|
1
2
3
4
5
6
7
8
9
|
SELECT
DEPTNO
,JOB
,GROUPING(DEPTNO) AS DG
,GROUPING(JOB) AS JG
,GROUPING_ID(DEPTNO, JOB) GI
,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
|
GROUPING_ID 함수는 인자로 들어온 각각 칼럼의 GROUPING 함수 값을 2진수로 합쳐 해당 2진수 값을 10진수로 변환한 값을 반환해줍니다.
숫자 GROUP
|
1
2
3
4
5
6
|
ELECT
DEPTNO
,JOB
,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(1,DEPTNO, JOB);
|
숫자가 들어간 ROLLUP은 뭘까요? 결과를 보니 마지막 2개의 줄이 같은 값이 나왔습니다. 헷갈릴 수 있지만 ROLLUP의 원리를 생각해보면 별 다를것 없습니다. JOB과 DEPTNO가 ROLLUP에서 빠지고 1만 남았을때 SUM(SAL)을 구하려고 합니다. 상수에 대한 GROUP은 없습니다. 즉, 전체 행에 대한 결과를 출력하라는 의미와 같습니다.
최종 풀이
'Oracle > SQL' 카테고리의 다른 글
| [ORACLE] SELECT ~ FOR UPDATE (4) | 2024.10.08 |
|---|---|
| [Oracle]ORA-02185: a token other than WORK follows COMMIT (0) | 2024.07.05 |
| [Oracle] EXECUTE IMMEDIATE 문 ( 동적 SQL) (0) | 2024.07.04 |
| [SQL] 계층형 쿼리 ( CONNECT BY ) (0) | 2024.03.05 |
| [SQL] 윈도우 함수(Window Function)의 종류(4)- 비율 관련 함수(CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT) (0) | 2023.04.18 |