[Oracle] 소계 및 총계 구하기(ROLLUP, GROUPING_ID 함수)

작성일자 : 2023-10-28

Ver 0.1.1

0. Intro

일반적으로 Sales와 관련된 데이터를 조회하거나 Tableau 대시보드 개발하고 나면 월별, 분기별, 연도별 혹은 제품과 같은 Dimension별로  GROUP BY를 이용하여 집계된 값을 확인한다. 

그리고 때에 따라 소계나 총합계 데이터가 필요할 때도 있다. 소계나 총계를 구하기 위해서 쿼리를 다시 작성해도 되지만,

GROUP BY에서 ROLLUP 함수를 통해서 소계 및 총합계를 구할 수 있다.

 

GROUP BY를 다룬 아래 글에서도 물론 ROLLUP과 CUBE, GROUPING SETS에 대해서 언급 했지만, 이번 포스팅에서는 ROLLUP에 대해서 집중정으로 정리해보려 한다.

 

ROLLUP 함수는 실무에서도 정말 정말 많이 사용되는 함수인 만큼 아주 큰 도움이 될 것같다.

[SQL]GROUP BY

---

1. GROUP BY 컬럼이 1개인 경우

SELECT job
     , SUM(sal) 
  FROM SCOTT.emp
 GROUP BY ROLLUP(job);

---

2. GROUP BY 컬럼이 두개 이상인 경우

SELECT job
     , deptno
     , SUM(sal) 
  FROM SCOTT.emp
 GROUP BY ROLLUP(job, deptno);

---

3. GROUP BY 컬럼이 두개 이상인 경우,  총 합계만 표시하고 싶을 때

기본적으로 두개 이상의 컬럼에서 ROLLUP을 하게 되면 소계가 출력된다. 이런 상황에서 총 합계만을 표시하고 싶다면 GROUPING_ID을 활용하면 된다.

 

본격적인 GROUPING_ID 적용에 앞서 GROUPING과 GROUPING_ID 둘간의 차이에 대해서 알아야한다.

GROUPING은 컬럼이 소계나 총합계 등 집계된 데이터일 경우 1을 리턴하고 그렇지 않다면 0을 리턴하는 함수이다. 

출처 : https://jhnyang.tistory.com/411

GROUPING은 괄호안에 하나의 컬럼만 올수 있다. 하지만 사용하다보면 다수의 컬럼에 대한 GROUPING 값이 모두 필요한 경우가 있다. 이러한 경우를 위해서 GROUPING_ID 함수가 있는 것이다.

 

GROUPING_ID 함수는 GROUPING_ID(COL1,COL2, ...) 의 NULL여부에 따라 0,1,2,3 ... 을 리턴한다.

GROUPING_ID는 다수의 컬럼에 대한 GROUPING 값을 알게 해주며, 하나의 함수로 다수의 결과 데이터를 얻을 수 있도록 GROUPING_ID는 이진법을 이용한다. GROUPING 결과값이 ON(1), OFF(0) 두가지로만 표현될 수 있기 때문에 비트방식으로 표현할 수 있다.

출처 : https://jhnyang.tistory.com/411

이에 바로 위의 쿼리에서 GROUPING_ID(job, deptno)까지 붙여서 조회하면 아래와 같은 GROUPING_ID가 출력된다.

 

SELECT job
     , deptno
     , SUM(sal)
     , GROUPING_ID(job, deptno)
  FROM SCOTT.emp
 GROUP BY ROLLUP(job, deptno);

각 데이터는 0, 소계는 1, 총합계는 3으로 GROUPING_ID가 리턴되었다. 이 상태에서 GROUPING_ID를 Having절로 GROUPING_ID가 0,3만을 걸러낸다면 소계는 사라지게 될 것이다.

SELECT job
     , deptno
     , SUM(sal)
  FROM SCOTT.emp
 GROUP BY ROLLUP(job, deptno)
HAVING GROUPING_ID(job, deptno) IN (0, 3);

컬럼이 더 많아진다면 GROUPING_ID 조회를 통해 ID 값을 확인하고, Having 절로 원하는 소계 및 합계만 조회할 수 있을 것이다.

---

4. ROLLUP 행 데이터에 Label 표시

소계나 총계는 기본적으로 '-'으로 표기가 되는데 사용자가 원하는 데이터로 보이게끔 할 수도 있다. DECODE 함수를 기반으로 더 나아가 NVL함수도 적절하게 사용하여 원하는 형태로 표기를 할 수 있다.

SELECT DECODE(GROUPING(job), 1, 'Grand Total', job) AS job
     , deptno
     , SUM(sal) AS sal
  FROM SCOTT.emp
 GROUP BY ROLLUP(job, deptno)
HAVING GROUPING_ID(job, deptno) IN (0, 3);

SELECT DECODE(GROUPING_ID(job, deptno), 0, job, 1, 'Sub Total', 3, 'Grand Total') AS job
     , deptno
     , SUM(sal) AS sal
  FROM SCOTT.emp
 GROUP BY ROLLUP(job, deptno);