Mooyoung2020

Mooyoung2020

SQL Tunner

ROLLUP과 GROUPING 함수

Updated:

GROUPING 함수 기본개념

ROLLUP과 함께 많이 사용하는 GROUPING 함수로는 오라클에서는 Grouping, Grouping_id, Group_id 등 3개가 있습니다.
GROUPING 함수는 데이타자체의 가공이나 특별한 연산기능은 제공되지 않으나, Rollup에 의해 반환되는 레코드들의 식별자 역할을 수행하여, 레코드들을 하나 하나 식별할수 있으므로, 이 성질을 이용하여 여러가지 응용을 할수 있습니다.

복잡한 ROLLUP, CUBE, GROUPING SETS 함수를 사용할때에는 이런 GROUPING 함수안의 칼럼을 잘 선택하여야 제대로된 처리를 할수 있습니다.
그리고 간혹 nvl을 사용하여 decode, case절대신 사용하는 경우가 있는데 오라클 bug에 의해 특정버젼의 DBMS에서는 Wrong Results가 경고되고 있으니 가급적 nvl은 사용하지 마시길 바랍니다.

가. 예제 테이블 소개

  • 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 함수

GROUPING 함수는 아래와 같이 사용할수 있습니다. GROUP BY절에 사용된 칼럼만 GROUPING 함수에 사용할수 있으며, 이때 GROUPING 함수안에는 오직 하나의 칼럼만 들어갈수 있으며 0 혹은 1 값을 리턴합니다.

1. Grouping 함수 사용

select empno, sum(sal),  grouping(empno)
from emp
group by rollup(empno);
EMPNO SUM(SAL) GROUP(GB)
7369 800 0
7499 1600 0
7521 1250 0
7566 2975 0
7654 1250 0
7698 2850 0
7782 2450 0
7788 3000 0
7839 5000 0
7844 1500 0
7876 1100 0
7900 950 0
7902 3000 0
7934 1300 0
  29025 1

2. Decode, Case등으로 Grouping 함수 처리

Decode, Case 절로 아래와 같이 식별자 역할을 하는 Grouping 함수를 처리하면 됩니다.

select decode(grouping(empno), 1, '합계', empno) as empno, 
       sum(sal),  grouping(empno)
from emp
group by rollup(empno);
EMPNO SUM(SAL) GROUP(GB)
7369 800 0
7499 1600 0
7521 1250 0
7566 2975 0
7654 1250 0
7698 2850 0
7782 2450 0
7788 3000 0
7839 5000 0
7844 1500 0
7876 1100 0
7900 950 0
7902 3000 0
7934 1300 0
합계 29025 1

3. 2차원 ROLLUP과 Grouping 함수 처리

EMP 테이블에서 아래와 같이 2차원 ROLLUP 값을 구해봅니다

select decode(grouping(deptno),1,'소계', deptno) as deptno,
       empno, sum(sal)
from emp
group by rollup(deptno, empno);
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_ID 함수

GROUPING_ID(칼럼1, 칼럼N) 함수는 보통 2개 이상의 칼럼들을 사용하여 각 레코드별 식별자 역할을 수행하는 함수입니다. GROUPING (칼럼) 함수는 오직 1개의 칼럼만 들어갈수 단점이 있는데 2개이상의 칼럼을 사용할려면 GROUPING_ID 함수를 사용하여야 합니다. 대부분의 DBMS(MYSQL, 마리아등)가 GRPUPING_ID 함수를 지원하지 않고 있으며 오라클, 티베로등 DBMS는 현재 지원하고 있습니다.
GROUPING_ID 함수는 GRPUPING 함수를 서로 연결하여 놓은것으로 보면됩니다.즉

GROUPING_ID(칼럼1, 칼럼N) = GROUPING(칼럼1) GROUPING(칼럼N)

여기서 | 는 연결자입니다. 예를 들어 아래의 SQL에 사용된 grouping_id(deptno, empno)는 grouping(deptno) | grouping(empno) 와 동일하다. 여기서 grouping 함수들의 값들은 2진수값으로 나타나는데, grouping(deptno) | grouping(empno) 의 최대값은 1 | 1 입니다.

이것은 이진수로 11 인데, 십진수로 변환하면 3 으로 나타난다. 그래서 grouping_id(deptno, empno) 의 식별자 값들은 0, 1, 3 이렇게 3개의 값으로 나타나게 됩니다.

1. Rollup과 Grouping_id 함수를 사용하여 레코드 식별

select  deptno, empno, sum(sal), 
        grouping_id(deptno, empno) aa
from emp 
group by rollup(deptno, empno);
DEPTNO EMPNO SUM(SAL) AA
10 7782 2450 0
10 7839 5000 0
10 7934 1300 0
10   8750 1
20 7369 800 0
20 7566 2975 0
20 7788 1100 0
20 7876 1100 0
20 7902 3000 0
20   8975 1
30 7900 950 0
30 7499 1600 0
30 7521 1250 0
30 7654 1250 0
30 7698 2850 0
30 7844 1500 0
30   9400 1
    27125 3

2. Decode를 이용하여 원하는 형태로 Grouping_id 함수 처리

각 레코드별 “식별자” 역할을 하는 grouping_id 함수를 처리합니다. 이때에는 보통 case절, decode절을 많이 사용합니다.

select DECODE(grouping_id(deptno, empno), 1, '소계', 3, '합계', deptno) as deptno, 
       empno, sum(sal),    
       grouping_id(deptno, empno) aa
from emp 
group by rollup(deptno, empno);
DEPTNO EMPNO SUM(SAL) AA
10 7782 2450 0
10 7839 5000 0
10 7934 1300 0
소계   8750 1
20 7369 800 0
20 7566 2975 0
20 7788 1100 0
20 7876 1100 0
20 7902 3000 0
소계   8975 1
30 7900 950 0
30 7499 1600 0
30 7521 1250 0
30 7654 1250 0
30 7698 2850 0
30 7844 1500 0
소계   9400 1
합계   27125 3

라. GROUP_ID 함수

GROUP_ID() 함수의 ()안에는 칼럼을 넣을수 없습니다. 이함수를 처음 공부할때는 왜 이함수가 오라클에서 필요한지를 이해하기 어려웠으나 이제는 이 함수가 없으면 굉장히 곤란을 겪는다. 이 함수의 용도는

GROUP BY ROLLUP (1, DNAME, 1, JOB)

에서와 같이 SUB TOTAL과 ALL TOTAL 양쪽 동시에 복제를 1개 더 만들게 되면 기존의 GROUPING 함수와 GROUPING_ID 함수가 제대로 된 식별값을 리턴하지 못합니다. 그때 이 GROUP_ID() 함수를 사용하게 되면, 0,1,2,3…..등 복제된 갯수만큼 숫자를 리턴해준다. 이 값으로 우리는 식별자값으로 활용하여 DECDOE, CASE로 처리하면 됩니다. 이때 주의해야할것은 오라클의 BUG로 보여지는데

GROUP BY 1, ROLLUP (1, DNAME, 1, JOB)
형태로 1 이라는 값을 Partial Rollup 형태로 맨앞에 하나 넣어야 정확한 값을 리턴받을수 있습니다.

SELECT deptno, decode(grouping(empno), 1, decode(group_id(), 
                                          0, deptno||' 합계', 
                                          1, deptno||' 평균',  
                                          2 ,deptno||' 최소값', 
                                          3, deptno||' 최고값'), empno) empno, 
       decode(group_id(), 0, sum(sal), 1, round(avg(sal)), 2, min(sal), 3, max(sal) ) sal, 
       grouping(empno) aa,  group_id() bb
FROM EMP
GROUP BY 1, rollup(1, 1, 1, deptno, 1, 1, 1, empno)    
ORDER BY deptno, aa, empno desc;
DEPTNO EMPNO SUM(SAL) AA BB
10 7934 1300 0 0
10 7839 5000 0 0
10 7782 2450 0 0
10 10 합계 8750 1 0
10 10 평균 2917 1 1
10 10 최소값 1300 1 2
10 10 최고값 5000 1 3
20 7902 3000 0 0
20 7876 1100 0 0
20 7788 3000 0 0
20 7566 2975 0 0
20 7369 800 0 0
20 20 합계 10875 1 0
20 20 평균 2175 1 1
20 20 최소값 800 1 2
20 20 최고값 3000 1 3
30 7900 950 0 0
30 7844 1500 0 0
30 7698 2850 0 0
30 7654 1250 0 0
30 7521 1250 0 0
30 7499 1600 0 0
30 30 합계 9400 1 0
30 30 평균 1567 1 1
30 30 최소값 950 1 2
30 30 최고값 2850 1 3
  합계 29025 1 0
  평균 2073 1 1
  최소값 800 1 2
  최고값 5000 1 3

라. 결언

Grouping 함수들은 ROLLUP, CUBE, GROUPING SETS 함수들과 같이 사용되는 경우가 매우 많으며 그만큼 중요한 역할을 수행하고 있습니다.
Grouping 함수들이 어떤 값들을 리턴하는지 완전히 숙지하면 향후 고급SQL형태를 처리하는데 많은 도움이 될것이라 생각합니다.

You May Also Enjoy