Oracle ROLLUP, CUBE, GROUPING(), GROUPING SETS(), GROUP_ID() 함수 개념

ROLLUP

ROLLUP은 SELECT한 컬럼들을 GROUP BY 절과 함께 사용하게 되면 소계를 구할 수 있다

그 과정에는 CARTESIAN PRODUCT를 이용한 결과물들이란 것을 알아두자

또 한 ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게되므로 인수의 순서에도 주의하자

 

SELECT PRESS, BOOK_TYPE, BOOK_NAME, SUM(PRICE)

FROM TEST12

GROUP BY ROLLUP(PRESS, ROLLUP(BOOK_TYPE, BOOK_NAME)

 

위의 예제를 보면 알 수 있듯이 ROLLUP은 GROUP BY절에 사용하며 그룹별로 묶어주는 개념은 비슷하다

그리고 이 때 소계를 출력하고 싶다면 컬럼들을 ROLLUP으로 묶어주면 간단히 출력이 가능하다

 

CUBE

CUBE는 결합이 가능한 모든 값에 대하여 다차원 집계를 생성한다

GROUPING COLUMNS이 가질 수 있는 모든 경우에 대하여 SUBTOTAL을 생성해야 하는 경우에는 CUBE를 사용

ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다

 

SELECT PRESS, BOOK_TYPE, BOOK_NAME, SUM(PRICE)

FROM TEST12

GROUP BY CUBE(PRESS, BOOK_TYPE, BOOK_NAME)

 

위의 구문을 실행해보고 ROLLUP과 차이점을 생각해보자

 

 

GROUPING 함수

ROLLUP, CUBE, GROUPING SET등 새로운 그룹 함수를 지원하기위해 GROUPING 함수가 추가되었다

 

-ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1이 표시되고

-그 외의 결과는 GROUPING(EXPR) = 0이 표시된다

 

SELECT PRESS, GROUPING(PRESS), BOOK_TYPE, GROUPING(BOOK_TYPE),

BOOK_NAME, GROUPING(BOOK_NAME),SUM(PRICE)

FROM TEST12

GROUP BY ROLLUP(PRESS, BOOK_TYPE, BOOK_NAME)

 

출력값을 확인하자

 

 

GROUPING_ID(expr)와 GROUP ID() 함수

루트 노드의 경우는 1， 그 디음 계층은 2， 3을 순차적으로 반환하였다. 그룹핑 쿼리에서도 그룹핑되어 추출된 각각의 로우들에 대해 그룹핑 레벨을 알 수 있는데 바로 GROUPING_IDO 함수를 시용하면 된다

 

SELECT

      DECODE (GROUPING (department_id) ， 1， ' 전체부서', department_id) DEP,

      DECODE (GROUPING (job_id), 1, '합계', job_id) JOB，

      SUM (salary),

      GROUPING_ID (department_id) g_dep,

      GROUPING_ID (job_id) g_job,

      GROUPING_ID (department_id, job_id) g_total

FROM employees

WHERE department_id <= 40

GROUP BY CUBE(department_id, job_id)

ORDER BY 1,2;

 

결국 G_TOTAL 컬럼값은 0에서 3까지의 수를 반환하게 되는 것이다.

GROUPING_ID 함수의 표현식으로 부서번호와 직급 순서를 변경하면 G_TOTAL 컬럼이 반환하는 값도 달라지게 된다.

만약 위 쿼리에서 세부적인 정보는 제거하고 부서별 합계액과 직급별 합계액만 조회하고자 한다면 어떻게 해야 할까?

다음과 같이 HAVING 절에 GROUPING_ID() 함수를 사용하여 조건을 주면 된다

 

SELECT

   DECODE(GROUPING(department_id), 1, '전체부서 ', department_id) DEP，

   DECODE (GROUPING(job_id), 1, '합계', job_id) JOBS,

   SUM (salary)

FROM employees

WHERE department_id <= 40

GROUP BY CUBE (department_id, job_id)

HAVING GROUPING_ID(department_id, job_id) > 0 ORDER BY 1, 2;

 

 

GROUPING SETS(expr) 표현식

쿼리는 단순히 주문방법, 결혼여부, 성별 별로 주문금액을 집계한 것이다.

SELECT ORD.ORDER_mode 주문방법,

   DECODE (cus.marital_status, 'single', '미혼', 'married', '기혼') 결혼여부,

   DECODE (cus.gender, 'F', '여성', 'M', '남성') 성별,

   SUM (ord.order_total) 주문금액

FROM orders ord, customers cus

WHERE ord.customer_id = cus.customer_id

GROUP BY GROUPING SETS (ord.order_mode, cus.marital_status, cus.gender);

 

위의 GROUP함수의 결과값을 비교해보자

GROUPING SETS은 CUBE를 사용한 쿼리에서 개별표현식에 대한 전체합계만을 따로 추출한 것과 같다고 볼 수 있다.