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

 

 GROUPING SETS() 함수는 이전 포스팅에서 다뤘던 ROLLUP()과 마찬가지로 GROUP BY 절에서 사용하는 함수이며, 인자로 받는 컬럼 별 집계함수 값을 리턴하는 함수이다.

 위와 같은 결과를 얻고 싶을 때, GROUPING SETS 함수를 활용하면 다음과 같이 짤 수 있다.

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

 근데 뭔가 이상하지 않은가? 위와 같은 결과를 더 쉽게 추출하는 방법을 우리는 알고 있다.

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

 GROUP BY 절에 아무 함수 없이 그냥 부서명으로 그룹핑만 해주어도 위와 같은 결과가 나오고, 심지어 이게 보기도 더 편하다. 그래서 GROUPING SETS() 함수는 인자를 한 개만 주면 사용할 이유가 없고, 인자를 두개 이상 줬을 때 그 진가를 발휘한다.

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

 GROUPING SETS() 함수는 첫번째로 받은 인자에 대한 집계함수 값을 출력한 뒤, 두번째로 받은 인자에 대한 집계함수 값을 출력한다. 마찬가지로 인자를 세개 줬을 때도 동일한 결과가 나온다.

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

MGR 컬럼의 마지막 값은 총 합계가 아닌 MGR컬럼이 NULL 인 사원에 대한 합계이다.

 

 또한 ROLLUP() 함수를 사용했을 때 추가로 출력되는 총 합계에 대한 정보를 받고 싶다면 빈 괄호를 인자로 줌으로써 해당 결과를 얻을 수 있다.

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

 이렇듯, GROUPING SETS() 함수는 인자로 받는 컬럼들 각각의 집계함수 값을 출력할 수 있다. GROUPING SETS()은 확장 GROUP 함수 중에 가장 직관적으로 결과를 얻을 수 있는 함수여서 가장 많이 사용하는 확장 GROUP 함수이기도 하다. 다른 확장 GROUP 함수로 표현할 수 있다면 GROUPING SETS 함수로도 표현 가능하다.

-- ROLLUP() 함수 사용
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
;

-- GROUPING SETS() 함수 사용
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), B.DNAME, ())
 ORDER BY 1,2
;

 

---

 

 확장 GROUP 함수는 필수는 아니지만 알아두면 굉장히 유용하게 쓸 수 있는 함수이다. 단순히 원하는 값을 손 쉽게 얻을 수 있다는 것을 떠나서 성능적인 측면에서도 많은 도움을 준다. 비슷한 역할을 하는 ROLLUP() 함수의 사용 유무 비교를 오라클 성능 분석표를 통해 보면 확실히 알 수 있다.

 여기서 중요하게 볼 부분은 Elapsed Time과 Query 컬럼이다. Elapsed Time은 쿼리를 실행하는데 걸린 시간이고, Query는 결과를 얻기까지 시스템이 돌린 쿼리의 횟수이다. 데이터가 적고 ERD 규모가 적은 환경에서 테스트해보았는데도 불구하고 한 눈에 봐도 차이가 나는 것이 보인다. 이런 차이는 실무에서 방대한 규모의 데이터를 다룬다면 더 뚜렷하게 보일 것이다.
 본인이 소규모 프로젝트만 진행하고 당장 급하게 쿼리를 짜야하는 상황이라면 이런 성능표까지 비교분석하면서 쿼리를 짤 필요는 없을 것이다(결과가 잘나오는 것이 가장 중요하니까). 하지만 막대한 양의 데이터를 다루는 큰 규모의 프로젝트에서 쿼리를 짠다거나, 튜너의 업무를 맡게 되었다면 이런 성능 분석을 통해 최대한 메모리 소모를 적게하는 코드 방식을 고민해 볼 필요가 있다고 생각한다.

 

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

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