[Oracle] 그룹 함수(Group Funtion)

그룹 함수(Group Funtion)

 그룹 함수로는 집계 함수를 제외하고, 소그룹 간의 소계를 계산하는 ROLLUP 함수, GROUP BY 항목들 간 다차원적인 소계를 계산할 수 있는 CUBE 함수, 특정 항목에 대한 소계를 계산하는 GROUPING SETS 함수가 있습니다.

 ROLLUP은 GROUP BY의 확장된 형태로 사용하기가 쉬우며 병렬로 수행이 가능하기 때문에 매우 효과적일 뿐 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합하도록 되어 있습니다. CUBE는 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻는 장점이 있는 반면에, 시스템에 부하를 많이 주는 단점이 있습니다. GROUPING SETS는 원하는 부분의 소계만 손쉽게 추출한 수 있는 장점이 있습니다. ROLLUP, CUBE, GROUPING SETS 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 정렬 컬럼을 명시해야 합니다.

ROLLUP 함수

 ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성됩니다. 중요한 것은, ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의해야 합니다.

 

GROUP BY 절 사용

-- 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장
SELECT DNAME
          , JOB
          , COUNT(*) AS "사원수"
          , SUM(SAL) AS "급여 총합"
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO                                                                                                                                               
 GROUP BY DNAME, JOB;

GROUP BY 절 + ORDER BY 절 사용

-- 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장에 ORDER BY 절을 사용
SELECT DNAME
          , JOB
          , COUNT(*) AS "사원수"
          , SUM(SAL) AS "급여 총합"
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO                                                                                                                                              
 GROUP BY DNAME, JOB
 ORDER BY DNAME, JOB;

GROUP BY 절 + ROLLUP 함수 사용 

-- 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수 사용
SELECT DNAME
          , JOB
          , COUNT(*) AS "사원수"
          , SUM(SAL) AS "급여 총합"
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO                                                                                                                                             
 GROUP BY ROLLUP( DNAME, JOB );

 

GROUPING COLUMNS(DNAME,JOB)에 대하여 다음과 같은 추가 LEVEL의 집계가 생성된 것을 확인할 수 있습니다.

1.GROUP BY 수행 시에 생되는 표준 집계 9건

2.DNAME 별 모든 JOB의 SUBTOTAL 3건

3.GRAND TOTAL 1건 

 ROLLUP의 경우 계층 간 집계에 대해서는 LEVEL 별 순서를 정렬하지만, 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않습니다. 계층 내 정렬을 위해서는 별도의 ORDER BY 절을 사용해야 합니다. 

 

GROUP BY 절 + ROLLUP 함수 + ORDER BY 절 사용

-- 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장에  ROLLUP 함수를 사용한 SQL 문장에 ORDER BY 절을 사용
SELECT DNAME
          , JOB
          , COUNT(*) AS "사원수"
          , SUM(SAL) AS "급여 총합"
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO
 GROUP BY ROLLUP( DNAME, JOB )
 ORDER BY DNAME, JOB;

 

GROUPING 함수

 ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시되고, 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시됩니다. 

-- 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수 및 GROUPING 함수 사용
SELECT DNAME 
          , GROUPING(DNAME)
          , JOB
          , GROUPING(JOB)
          , COUNT(*) AS "사원수"
          , SUM(SAL) AS "급여 총합"
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO                                                                                                                                            
 GROUP BY ROLLUP( DNAME, JOB )
 ORDER BY DNAME, JOB;

 

CUBE 함수 

 ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성합니다.

CUBE를 사용할 경우에는 내부적으로 Grouping Columns의 순서를 바꾸어도 또 한 번의 쿼리를 추가 수행해야 합니다. 뿐만 아니라 Grand Total은 양쪽의 쿼리에서 모두 생성이 되므로 한 번의 쿼리에서는 제거되어야만 하므로 ROLLUP에 비해 시스템 연산 대상이 많습니다.

-- 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수 및 GROUPING 함수 사용
SELECT DECODE(GROUPING(DNAME), 1, '전체 부서', DNAME) AS DNAME 
          , DECODE(GROUPING(JOB), 1, '전체 업무', JOB) AS JOB 
          , COUNT(*) AS "사원수"
          , SUM(SAL) AS "급여 총합"
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO                                                                                                                                             
 GROUP BY CUBE( DNAME, JOB );

 

 CUBE는 GROUPING COLUMNS이 가질 수 있는 모든 경우의 수에 대하여 Subtotal을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면, 2의 N승 LEVEL의 Subtotal을 생성하게 됩니다.

 

GROUPING SETS 함수 

 GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같습니다.

그리고 GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 컬럼이 표시되어야 합니다.

-- 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장에 GROUPING SETS 함수 사용
SELECT DECODE(GROUPING(DNAME), 1, '전체 부서', DNAME) AS DNAME 
          , DECODE(GROUPING(JOB), 1, '전체 업무', JOB) AS JOB 
          , COUNT(*) AS "사원수"
          , SUM(SAL) AS "급여 총합"
  FROM EMP, DEPT
 WHERE EMP.DEPTNO = DEPT.DEPTNO                                                                                                                                           
 GROUP BY GROUPING SETS( DNAME, JOB );