[Oracle] 확장 GROUP 함수 - ROLLUP()

테이블 구조

 

 일상적인 업무에서 쿼리를 짜다 보면, 합계를 구하고 싶을 때가 정말 많다. 예시로 DEPT 테이블의 부서명 컬럼을 기준으로 해당 부서에 속한 사원들의 총 급여를 구하려면 쿼리를 어떻게 짜야할까?

 확장 GROUP 함수를 모른다면, 다음과 같이 작성했을 것이다.

SELECT B.DNAME
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY B.DNAME
 UNION ALL
SELECT NULL
     , SUM(A.SAL)
  FROM EMP A
 ORDER BY 1
;

 

 위 방식이 틀렸다는 것은 아니다. 하지만 확장 GROUP 함수를 쓴다면 다음처럼 간략하게 표현 가능하다.

SELECT B.DNAME
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY ROLLUP(B.DNAME)
 ORDER BY 1
;

 코드가 훨씬 간결해져 눈에 잘들어온다. 위의 예시처럼 기준이 되는 컬럼이 적다면 두가지 방식 중 어떤 방식을 사용하던 성능적으로나 코드적으로 크게 문제될 것이 없을 것이다. 다음 예시와 같이 기준이 되는 컬럼이 늘어날수록 작성 방식에 따라 차이가 극명하게 드러날 것이다.

위에서 비교했던 방식대로 비교해보면,

SELECT B.DNAME
     , A.JOB
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY B.DNAME, A.JOB
 UNION ALL
SELECT B.DNAME
     , NULL
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY B.DNAME
 UNION ALL
SELECT NULL
     , NULL
     , SUM(A.SAL)
  FROM EMP A
 ORDER BY 1,2
;

SELECT B.DNAME
     , A.JOB
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY ROLLUP(B.DNAME, A.JOB)
 ORDER BY 1,2
;

 한 눈에 봐도 코드 길이가 확연히 차이난다. 그룹핑을 하는 기준이 되는 컬럼이 많아질수록 코드의 길이는 더 길어지고 성능면에서도 더욱 차이가 심해질 것이다.

 

 GROUP BY 절에 선언하여 사용하는 ROLLUP() 함수는 인자로 받는 기준 컬럼 별 집계 함수 값을 출력 한 뒤, 마지막 ROW에 전체 집계 함수 값을 추가로 리턴해준다. 위의 코드를 예로 들어 추가 설명하자면, 인자로 받은 부서명을 기준으로 선언된 집계함수를 통해 부서별 총 급여를 출력한 뒤(여기까진 일반 GROUP BY와 같다), 추가로 전체 총 급여를 출력한다. 만약 해당 쿼리에서 SUM 함수가 아닌 AVG 함수가 사용됐다면, 부서명 별 평균 급여를 모두 출력하고 전체 부서의 평균 급여가 추가로 출력되는 것이다.

 이해를 돕기 위해 다음 예시를 보자.

SELECT A
     , B
     , SUM(C)
  FROM TABLE
 GROUP BY ROLLUP(A,B)
 ORDER BY 1,2
;

 

A	B	SUM(C)
A1	B1	A1별 B1별 C의 총합
A1	B2	A1별 B2별 C의 총합
A1		A1별 C의 총합
A2	B1	A2별 B1별 C의 총합
A2	B2	A2별 B2별 C의 총합
A2		A2별 C의 총합
		C의 총합

 인자로 받는 컬럼의 수 만큼 상위 그룹의 총합이 추가로 출력된다고 생각하면 된다. (위 예시의 경우 인자로 A,B 두개의 컬럼을 줬으니 A별 총합, 전체 총합을 추가로 출력한다.)

 

 만약 전체 총합을 출력하지 않고 그룹핑되는 컬럼 중 특정 컬럼의 총합만 출력하고 싶다면, 상위 그룹에 해당하는 컬럼을 ROLLUP() 함수 바깥으로 꺼내주면 된다.

SELECT B.DNAME
     , A.JOB
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY B.DNAME, ROLLUP(A.JOB)
 ORDER BY 1,2
;

 부서 별 직업의 총합만 구하고 싶고 전체 부서 총합은 구하고 싶지 않을 때, ROLLUP() 함수의 인자로 있는 DNAME을 밖으로 빼서 따로 그루핑해주면 위와 같이 전체 부서의 총합이 없이 부서 별 전체 직업의 총합만 추가로 출력할 수 있다.

 

---

 

 확장 GROUP 함수는 필수는 아니지만 알아두면 굉장히 유용하게 쓸 수 있는 함수이다. 단순히 원하는 값을 손 쉽게 얻을 수 있다는 것을 떠나서 성능적인 측면에서도 많은 도움을 준다. 위에서 비교했던 코드를 오라클 성능 분석 표를 통해 자세히 비교해보면 더 체감이 된다.

 여기서 중요하게 볼 부분은 Elapsed Time과 Query 컬럼이다. Elapsed Time은 쿼리를 실행하는데 걸린 시간이고, Query는 결과를 얻기까지 시스템이 돌린 쿼리의 횟수이다. 데이터가 적고 ERD 규모가 적은 환경에서 테스트해보았는데도 불구하고 한 눈에 봐도 차이가 나는 것이 보인다. 이런 차이는 실무에서 방대한 규모의 데이터를 다룬다면 더 뚜렷하게 보일 것이다.

본인이 소규모 프로젝트만 진행하고 당장 급하게 쿼리를 짜야하는 상황이라면 이런 성능표까지 비교분석하면서 쿼리를 짤 필요는 없을 것이다(결과가 잘나오는 것이 가장 중요하니까). 하지만 막대한 양의 데이터를 다루는 큰 규모의 프로젝트에서 쿼리를 짠다거나, 튜너의 업무를 맡게 되었다면 이런 성능 분석을 통해 최대한 메모리 소모를 적게하는 코드 방식을 고민해 볼 필요가 있다고 생각한다.

 

[Oracle] 확장 GROUP 함수 - GROUPING SETS()

 

[Oracle] 확장 GROUP 함수 - CUBE()