Oracle Grouping(), ROLLUP, CUBE

* ROLLUP 연산자
 - GROUP BY절에 있는 컬럼들을 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고,
   각 그룹에 계산함수를 적용한다.
 - GROUP BY절의 결과는 누적 계산 결과이다.
 

* CUBE 연산자
 - GROUP BY절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
 

* GROUPING 함수
 - 각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다.
 - 해당컬럼에 대해 계산되었다면 0, 그렇지 않다면(컬럼값이 NULL) 1을 반환한다.
 - GROUP BY절에 나타나는 컬럼에 적용된다.

사용 예)

  -- table생성(사원이름,급여,부서,직위,입사년도)
  CREATE TABLE roll_test (
    name   VARCHAR2(10),
    sal    NUMBER,
    dept   VARCHAR2(10),
    duty   VARCHAR2(10),
    entYear NUMBER(4)
  );

INSERT INTO roll_Test VALUES('kim' , 1000, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('no' , 1500, 'AA', '00', 2004);
INSERT INTO roll_Test VALUES('choi', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('park', 2000, 'BB', '02', 2003);
INSERT INTO roll_Test VALUES('lee' , 3000, 'CC', '03', 2002);
INSERT INTO roll_Test VALUES('cho' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('lyu' , 4000, 'DD', '04', 2001);
INSERT INTO roll_Test VALUES('ham' , 4000, 'AA', '04', 2001);
INSERT INTO roll_Test VALUES('kang', 7000, 'DD', '05', 2001);
COMMIT;

SELECT * FROM roll_Test;

-- 1. 각 부서에 대한 급여 소계를 구하고, 총계를 구하라
--    (하나의 Column Grouping)

-----일반-----------
SELECT dept, SUM(sal)
FROM   roll_Test
GROUP BY dept;
---------------------

----- ROLLUP ---------
SELECT dept, SUM(sal), GROUPING(dept)
FROM   roll_Test
GROUP BY ROLLUP(dept);
-----------------------

----- CUBE ----------
SELECT dept, SUM(sal), GROUPING(dept)
FROM roll_Test
GROUP BY CUBE(dept);
-----------------------

-- 일반적인 GROUP BY를 사용할 경우 급여 소계만 나오고, 총계는 따로 구해야 함
-- ROLLUP과 CUBE 차이점 없음

-- 2. 각 부서별, 직위별 급여 소계를 구하고, 총계를 구하라
--    (두개의Column Grouping)

-----NORMAL-----------
SELECT dept, duty, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty;
-----------------------

----- ROLLUP ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty);
-----------------------

----- CUBE ----------
SELECT dept, duty, SUM(sal), GROUPING(dept), GROUPING(duty)
FROM   roll_Test
GROUP BY CUBE(dept, duty);
-----------------------

-- ROLLUP은 부서에 대한 소계 / 부서에 대한 직위별 소계만 볼 수 있고,
-- CUBE는 부서에 대한 소계 / 부서에 대한 직위별 소계 / 직위별 소계를 볼 수 있음
-- GROUP BY 내의 왼쪽 컬럼부터 자동으로 오름차순 정렬 됨

-- 3. 각 부서별, 직위별, 입사년도별 급여 소계를 구하고, 총계를 구하라
--    (세개의 Column Grouping)

-----NORMAL-----------
SELECT dept, duty, entYear, SUM(sal)
FROM   roll_Test
GROUP BY dept, duty, entYear;
-----------------------

----- ROLLUP ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY ROLLUP(dept, duty, entYear);
-----------------------

----- CUBE ----------
SELECT dept, duty, entYear, SUM(sal), GROUPING(dept), GROUPING(duty), GROUPING(entYear)
FROM   roll_Test
GROUP BY CUBE(dept, duty, entYear);
-----------------------

- ROLLUP 사용시 3개의 소계와 1개의 총계를 구할 수 있음
  (부서별, 부서*직위별, 부서*직위*입사년도별, 총계)
  ※ GROUP BY 내의 가장 왼쪽 컬럼을 기준으로 하여 순차적으로 하위 그룹 생성

- CUBE 사용시 7개의 소계와 1개의 총계를 구할 수 있음.
  (부서별, 직위별, 입사년도별, 부서*직위별, 부서*입사년도별, 직위*입사년도별, 부서*직위*입사년도별, 총계)
  ※ 생성 가능한 모든 경우를 그룹 생성
 

사용 예)

* 부서에 대한 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 1
AND GROUPING(entYear) = 1;

* 각 부서에 대한 직위별 소계를 보고 싶을 때
HAVING GROUPING(dept) = 0 AND GROUPING(duty) = 0
AND GROUPING(entYear) = 1;

간단한 예)

SELECT 
   DECODE(GROUPING(rnum), 1, '합계', MAX(a)) AS a
 , DECODE(GROUPING(rnum), 1, SUM(CASE WHEN rnum >= tcnt-1 THEN b END), SUM(b)) AS b
 , DECODE(GROUPING(rnum), 1, SUM(CASE WHEN rnum >= tcnt-1 THEN c END), SUM(c)) AS c
FROM(
 SELECT
    t.*
  , ROWNUM AS rnum
  , COUNT(*) OVER() AS tcnt
 FROM(
  SELECT 'aa' AS a, 10 AS b, 20 AS c FROM dual UNION ALL
  SELECT 'bb' AS a, 20 AS b, 10 AS c FROM dual UNION ALL
  SELECT 'cc' AS a, 30 AS b, 20 AS c FROM dual UNION ALL
  SELECT 'dd' AS a, 20 AS b, 30 AS c FROM dual UNION ALL
  SELECT '소계'  , 80 AS b, 80 AS c FROM dual UNION ALL
  SELECT 'gg'    , 20 AS b, 20 AS c FROM dual
  ) t 
 )
GROUP BY ROLLUP(rnum)
ORDER BY rnum NULLS LAST

[출처] 그전 로우의 합계를 쿼리 해온 값의 마지막 로우|작성자 최선을 다하는 삶