[SQL]ROLLUP/CUBE

ROLLUP/CUBE

출처 : http://sqler.pe.kr/sql2k/28.asp

◈ ROLLUP operator 

  - ROLLUP구문은 GROUP BY절과 같이 사용 되며, GROUP BY절에 의해서 그룹 지어진 

    집합결과에 대해서 좀 더 상세한 정보를 반환하는 기능을 수행합니다.

  - SELECT절에 ROLLUP을 사용함으로써 보통의 select된 데이터와 그 데이터의

    총계를 구할 수 있습니다.

==============================================================================================

※ 각 그룹 요소별로 SUM,AVG,등 을 보여준다.

    SELECT a,b,c,d,SUM(d) 

    FROM sales

    GROUP BY a,b,c,d 

    WHIT ROLLUP

  -- a를 그룹 sum(d)값과, a,b를 그룹 sum(d) ,  a,b,c 그룹 sum(d), a,b,c,d 그룹 sum(d)를 보여준다.

==============================================================================================

-- Group By를 사용해서 직업별로 급여 합계를 구하는 예제 입니다.

SELECT job, SUM(sal)

FROM emp

GROUP BY job 

JOB          SUM(SAL)

---------- ----------

ANALYST          600

CLERK              3200

MANAGER        33925

PRESIDENT      5000

SALESMAN      4000

-- 아래 SQL문은 위의 예제에 ROLLUP구문을 사용해서 직업별로 급여 합계를 구하고

   총계를 구하는 예제 입니다.

SELECT job, SUM(sal)

FROM emp

GROUP BY job

WITH ROLLUP

JOB          SUM(SAL)

---------- ----------

ANALYST          6000

CLERK              3200

MANAGER        33925

PRESIDENT       5000

SALESMAN       4000

                       52125   --> 급여 합계에 대한 총계가 추가 되었습니다.

우선 간단하게 ROLLUP  Operator의 예제를 살펴보았습니다. 

조금더 복잡한(?) 예제를 하나더 해보면은요..

-- 부서의 직업별로 인원수와 급여 합계를 구하는 예제를 하나더 해보겠습니다.

-- 일반적인 Group By절을 사용해서 SQL문을 구현해보면은요.. 아래와 같이 하면 되겠죠..

SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count

FROM emp a, dept b

WHERE a.deptno = b.deptno

GROUP BY b.dname, a.job

DNAME       JOB               SAL  EMP_COUNT

----------  ---------- ---------- ----------

ACCOUNTING  CLERK               1300          1

ACCOUNTING  MANAGER          2450          1

ACCOUNTING  PRESIDENT        5000          1

RESEARCH    ANALYST            6000          2

RESEARCH    CLERK                1900          2

RESEARCH    MANAGER           2975          1

SALES       MANAGER              28500          1

SALES       SALESMAN             4000          3

-- 결과를 보면은요..  부서별로 인원이 몇명이고, 또 급여합계가 얼마가 되는지 한눈에 보이지 않죠...

   일일이 부서에 해당하는 직업별 급여와 사원수를 일일이 더해야 되죠..

-- 이런 경우 ROLLUP  Operator를 적용해서 구현을 하면은 부서별 급여합계와 사원 총수를 

   쉽게 볼 수 있습니다...

SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count

FROM emp a, dept b

WHERE a.deptno = b.deptno

GROUP BY b.dname, a.job

WITH ROLLUP

DNAME      JOB               SAL  EMP_COUNT

---------- ---------- ---------- ----------

ACCOUNTING CLERK              1300          1

ACCOUNTING MANAGER          2450          1

ACCOUNTING PRESIDENT        5000          1

ACCOUNTING                         8750          3  -->  ACCOUNTING 부서의 급여합계와 전체 사원수..

RESEARCH   ANALYST            6000          2

RESEARCH   CLERK                1900          2

RESEARCH   MANAGER           2975          1

RESEARCH                            10875          5 -->  RESEARCH 부서의 급여합계와 전체 사원수..

SALES      MANAGER              28500          1

SALES      SALESMAN             4000          3

SALES                                  32500          4 -->  SALES부서의 급여합계와 전체 사원수..

                                            52125         12 ->  전체 급여 합계와 전체 사원수

위와 같이 ROLLUP Operator을 일반적인 누적에 대한 총계를 구할때 사용하면 아주 편리하게 

사용 할 수 있습니다.

저자별로..... 거기에 각각의 잭종류 별로 로열티 별로 볼 수는 없을가...

흠흠흠.. 저 0376같은 저자를 보니... 사업과 정신세계에 대한 책을 썼는데...

각각에대해 좀더 자세한 값을 보고 싶구먼.. 흠흠흠....

  

select pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값

from titles

GROUP BY pub_id, type, royalty, ytd_sales

WITH ROLLUP　

저자 책종류 로열티 팔린수 평균값 

---- ------------ ----------- ----------- --------------------- 

0736 business 24 18722 2.9900

0736 business 24 NULL 2.9900

0736 business NULL NULL 2.9900

0736 psychology 10 111 7.0000

0736 psychology 10 3336 7.9900

0736 psychology 10 4072 19.9900

0736 psychology 10 NULL 11.6600

0736 psychology 12 2045 10.9500

0736 psychology 12 NULL 10.9500

0736 psychology NULL NULL 11.4825

0736 NULL NULL NULL 9.7840

0877 mod_cook 12 2032 19.9900

0877 mod_cook 12 NULL 19.9900

0877 mod_cook 24 22246 2.9900

0877 mod_cook 24 NULL 2.9900

0877 mod_cook NULL NULL 11.4900

0877 psychology 10 375 21.5900

0877 psychology 10 NULL 21.5900

0877 psychology NULL NULL 21.5900

0877 trad_cook 10 375 20.9500

0877 trad_cook 10 4095 14.9900

0877 trad_cook 10 NULL 17.9700

0877 trad_cook 14 15096 11.9500

0877 trad_cook 14 NULL 11.9500

0877 trad_cook NULL NULL 15.9633

0877 UNDECIDED NULL NULL NULL

0877 UNDECIDED NULL NULL NULL

0877 UNDECIDED NULL NULL NULL

0877 NULL NULL NULL 15.4100

1389 business 10 3876 11.9500

1389 business 10 4095 19.9900

1389 business 10 NULL 17.3100

1389 business NULL NULL 17.3100

1389 popular_comp NULL NULL NULL

1389 popular_comp NULL NULL NULL

1389 popular_comp 10 4095 20.0000

1389 popular_comp 10 NULL 20.0000

1389 popular_comp 16 8780 22.9500

1389 popular_comp 16 NULL 22.9500

1389 popular_comp NULL NULL 21.4750

1389 NULL NULL NULL 18.9760

NULL NULL NULL NULL 14.7662

(42개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

　

　

흠... 저 결과의 두 번째줄의 NULL과 같은 저 값은 무얼가????

아항~~~ 바로 총합일 경우구먼!!!!!!

그럼 결과 맨 밑줄의 다 널인놈은?

아하~~~ 모든책 저자, 종류에 대한~~~  총 평균을 말하는군!!!!

흠흠흠 (뿌듯~~~) 좋구먼... ^_^ 흠~~~~~~~

각각의 저자별로 책종류 별로... 로열티 별로.. 팔린수 별로 아주 자세히 보이는 구먼...

근데...

각각의 저자별이 아닌 책종류가 중요한데...

그렇쿠먼!!! 일케함 되겠구먼!!!!!!

  

select type 책종류,pub_id 저자, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값

from titles

GROUP BY type,pub_id, royalty, ytd_sales

WITH ROLLUP

　

책종류 저자 로열티 팔린수 평균값 

------------ ---- ----------- ----------- --------------------- 

business 0736 24 18722 2.9900

business 0736 24 NULL 2.9900

business 0736 NULL NULL 2.9900

business 1389 10 3876 11.9500

business 1389 10 4095 19.9900

business 1389 10 NULL 17.3100

business 1389 NULL NULL 17.3100

business NULL NULL NULL 13.7300

mod_cook 0877 12 2032 19.9900

mod_cook 0877 12 NULL 19.9900

mod_cook 0877 24 22246 2.9900

mod_cook 0877 24 NULL 2.9900

mod_cook 0877 NULL NULL 11.4900

mod_cook NULL NULL NULL 11.4900

popular_comp 1389 NULL NULL NULL

popular_comp 1389 NULL NULL NULL

popular_comp 1389 10 4095 20.0000

popular_comp 1389 10 NULL 20.0000

popular_comp 1389 16 8780 22.9500

popular_comp 1389 16 NULL 22.9500

popular_comp 1389 NULL NULL 21.4750

popular_comp NULL NULL NULL 21.4750

psychology 0736 10 111 7.0000

psychology 0736 10 3336 7.9900

psychology 0736 10 4072 19.9900

psychology 0736 10 NULL 11.6600

psychology 0736 12 2045 10.9500

psychology 0736 12 NULL 10.9500

psychology 0736 NULL NULL 11.4825

psychology 0877 10 375 21.5900

psychology 0877 10 NULL 21.5900

psychology 0877 NULL NULL 21.5900

psychology NULL NULL NULL 13.5040

trad_cook 0877 10 375 20.9500

trad_cook 0877 10 4095 14.9900

trad_cook 0877 10 NULL 17.9700

trad_cook 0877 14 15096 11.9500

trad_cook 0877 14 NULL 11.9500

trad_cook 0877 NULL NULL 15.9633

trad_cook NULL NULL NULL 15.9633

UNDECIDED 0877 NULL NULL NULL

UNDECIDED 0877 NULL NULL NULL

UNDECIDED 0877 NULL NULL NULL

UNDECIDED NULL NULL NULL NULL

NULL NULL NULL NULL 14.7662

(45개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

흠하하하~~~ 아주아주 좋은 자료군~~~ 이렇게 조은 명령이 있어 세세히

모든 자료를 들여다 볼 수 있으니.. 호오~~~ 죽이는구먼~~~~ 핫핫핫~~~~

그런데... 지금처럼... 저자별 각각의 책의 종류와....

종류별 각각의 책의 저자의 평균값을 동시에 볼 수는 없을가???????

조금 길어질 것도 같지만...

음.. 이게 좋겠군....

　

  

SELECT pub_id 저자, type 책종류, royalty 로열티, ytd_sales 팔린수, AVG(price) 평균값

FROM titles

GROUP BY pub_id, type, royalty, ytd_sales

WITH CUBE

　

저자 책종류 로열티 팔린수 평균값 

---- ------------ ----------- ----------- --------------------- 

0736 business 24 18722 2.9900

0736 business 24 NULL 2.9900

0736 business NULL NULL 2.9900

0736 psychology 10 111 7.0000

0736 psychology 10 3336 7.9900

0736 psychology 10 4072 19.9900

0736 psychology 10 NULL 11.6600

0736 psychology 12 2045 10.9500

0736 psychology 12 NULL 10.9500

0736 psychology NULL NULL 11.4825

0736 NULL NULL NULL 9.7840

0877 mod_cook 12 2032 19.9900

0877 mod_cook 12 NULL 19.9900

0877 mod_cook 24 22246 2.9900

0877 mod_cook 24 NULL 2.9900

0877 mod_cook NULL NULL 11.4900

0877 psychology 10 375 21.5900

0877 psychology 10 NULL 21.5900

0877 psychology NULL NULL 21.5900

0877 trad_cook 10 375 20.9500

0877 trad_cook 10 4095 14.9900

0877 trad_cook 10 NULL 17.9700

0877 trad_cook 14 15096 11.9500

0877 trad_cook 14 NULL 11.9500

0877 trad_cook NULL NULL 15.9633

0877 UNDECIDED NULL NULL NULL

0877 UNDECIDED NULL NULL NULL

0877 UNDECIDED NULL NULL NULL

0877 NULL NULL NULL 15.4100

1389 business 10 3876 11.9500

1389 business 10 4095 19.9900

1389 business 10 NULL 17.3100

1389 business NULL NULL 17.3100

1389 popular_comp NULL NULL NULL

1389 popular_comp NULL NULL NULL

1389 popular_comp 10 4095 20.0000

1389 popular_comp 10 NULL 20.0000

1389 popular_comp 16 8780 22.9500

1389 popular_comp 16 NULL 22.9500

1389 popular_comp NULL NULL 21.4750

1389 NULL NULL NULL 18.9760

NULL NULL NULL NULL 14.7662

NULL business 10 3876 11.9500

NULL business 10 4095 19.9900

NULL business 10 NULL 17.3100

NULL business 24 18722 2.9900

NULL business 24 NULL 2.9900

NULL business NULL NULL 13.7300

NULL mod_cook 12 2032 19.9900

NULL mod_cook 12 NULL 19.9900

NULL mod_cook 24 22246 2.9900

NULL mod_cook 24 NULL 2.9900

NULL mod_cook NULL NULL 11.4900

NULL popular_comp NULL NULL NULL

NULL popular_comp NULL NULL NULL

NULL popular_comp 10 4095 20.0000

NULL popular_comp 10 NULL 20.0000

NULL popular_comp 16 8780 22.9500

NULL popular_comp 16 NULL 22.9500

NULL popular_comp NULL NULL 21.4750

NULL psychology 10 111 7.0000

NULL psychology 10 375 21.5900

NULL psychology 10 3336 7.9900

NULL psychology 10 4072 19.9900

NULL psychology 10 NULL 14.1425

NULL psychology 12 2045 10.9500

NULL psychology 12 NULL 10.9500

NULL psychology NULL NULL 13.5040

NULL trad_cook 10 375 20.9500

NULL trad_cook 10 4095 14.9900

NULL trad_cook 10 NULL 17.9700

NULL trad_cook 14 15096 11.9500

NULL trad_cook 14 NULL 11.9500

NULL trad_cook NULL NULL 15.9633

NULL UNDECIDED NULL NULL NULL

NULL UNDECIDED NULL NULL NULL

NULL UNDECIDED NULL NULL NULL

0877 NULL NULL NULL NULL

1389 NULL NULL NULL NULL

NULL NULL NULL NULL NULL

NULL NULL NULL NULL NULL

0736 NULL 10 111 7.0000

NULL NULL 10 111 7.0000

0877 NULL 10 375 21.2700

NULL NULL 10 375 21.2700

0736 NULL 10 3336 7.9900

NULL NULL 10 3336 7.9900

1389 NULL 10 3876 11.9500

NULL NULL 10 3876 11.9500

0736 NULL 10 4072 19.9900

NULL NULL 10 4072 19.9900

0877 NULL 10 4095 14.9900

1389 NULL 10 4095 19.9933

NULL NULL 10 4095 18.7425

NULL NULL 10 NULL 16.4440

0877 NULL 12 2032 19.9900

NULL NULL 12 2032 19.9900

0736 NULL 12 2045 10.9500

NULL NULL 12 2045 10.9500

NULL NULL 12 NULL 15.4700

0877 NULL 14 15096 11.9500

NULL NULL 14 15096 11.9500

NULL NULL 14 NULL 11.9500

1389 NULL 16 8780 22.9500

NULL NULL 16 8780 22.9500

NULL NULL 16 NULL 22.9500

0736 NULL 24 18722 2.9900

NULL NULL 24 18722 2.9900

0877 NULL 24 22246 2.9900

NULL NULL 24 22246 2.9900

NULL NULL 24 NULL 2.9900

0877 UNDECIDED NULL NULL NULL

0877 NULL NULL NULL NULL

1389 popular_comp NULL NULL NULL

1389 NULL NULL NULL NULL

NULL NULL NULL NULL NULL

0736 psychology NULL 111 7.0000

0736 NULL NULL 111 7.0000

NULL NULL NULL 111 7.0000

0877 psychology NULL 375 21.5900

0877 trad_cook NULL 375 20.9500

0877 NULL NULL 375 21.2700

NULL NULL NULL 375 21.2700

0877 mod_cook NULL 2032 19.9900

0877 NULL NULL 2032 19.9900

NULL NULL NULL 2032 19.9900

0736 psychology NULL 2045 10.9500

0736 NULL NULL 2045 10.9500

NULL NULL NULL 2045 10.9500

0736 psychology NULL 3336 7.9900

0736 NULL NULL 3336 7.9900

NULL NULL NULL 3336 7.9900

1389 business NULL 3876 11.9500

1389 NULL NULL 3876 11.9500

NULL NULL NULL 3876 11.9500

0736 psychology NULL 4072 19.9900

0736 NULL NULL 4072 19.9900

NULL NULL NULL 4072 19.9900

0877 trad_cook NULL 4095 14.9900

0877 NULL NULL 4095 14.9900

1389 business NULL 4095 19.9900

1389 popular_comp NULL 4095 20.0000

1389 NULL NULL 4095 19.9933

NULL NULL NULL 4095 18.7425

1389 popular_comp NULL 8780 22.9500

1389 NULL NULL 8780 22.9500

NULL NULL NULL 8780 22.9500

0877 trad_cook NULL 15096 11.9500

0877 NULL NULL 15096 11.9500

NULL NULL NULL 15096 11.9500

0736 business NULL 18722 2.9900

0736 NULL NULL 18722 2.9900

NULL NULL NULL 18722 2.9900

0877 mod_cook NULL 22246 2.9900

0877 NULL NULL 22246 2.9900

NULL NULL NULL 22246 2.9900

0877 NULL NULL NULL NULL

1389 NULL NULL NULL NULL

0736 NULL 10 NULL 11.6600

0877 NULL 10 NULL 19.1766

1389 NULL 10 NULL 17.9825

0736 NULL 12 NULL 10.9500

0877 NULL 12 NULL 19.9900

0877 NULL 14 NULL 11.9500

1389 NULL 16 NULL 22.9500

0736 NULL 24 NULL 2.9900

0877 NULL 24 NULL 2.9900

NULL popular_comp NULL NULL NULL

NULL UNDECIDED NULL NULL NULL

NULL psychology NULL 111 7.0000

NULL psychology NULL 375 21.5900

NULL trad_cook NULL 375 20.9500

NULL mod_cook NULL 2032 19.9900

NULL psychology NULL 2045 10.9500

NULL psychology NULL 3336 7.9900

NULL business NULL 3876 11.9500

NULL psychology NULL 4072 19.9900

NULL business NULL 4095 19.9900

NULL popular_comp NULL 4095 20.0000

NULL trad_cook NULL 4095 14.9900

NULL popular_comp NULL 8780 22.9500

NULL trad_cook NULL 15096 11.9500

NULL business NULL 18722 2.9900

NULL mod_cook NULL 22246 2.9900

(184개 행 적용됨)

경고: 집계 또는 다른 집합 연산에 의해 Null 값이 제거되었습니다.

자 이렇게 지금까지 보신 내용이? ROLLUP과 CUBE에 대한 샘플 이었습니다.

조금더 내부적으로 알아 볼까요?

　

ROLLUP / CUBE 연산자

지정된 컬럼들에 대한 다양한 조합을 고려하는 데이타 요약의 필요성이 나타나게 되었다.

        - data warehousing

        - DSS(decision support system)

        - OLAP(online analytic processing)…

계산함수와 함께 사용되어 결과에 추가적인 행을 만든다.

        GROUP BY [ALL] aggregate_free_expression

                        [, aggregate_free_expression…]

                        [WITH{CUBE | ROLLUP}]

ROLLUP 연산자

- GROUP BY 절에 있는 컬럼들의 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고 

   각 그룹에 계산 함수를 적용한다.

- GROUP BY 절의 결과는 누적 계산 결과이다.

CUBE 연산자

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

고려사항

        - 최대 10 그룹의 표현식이 지정될 수 있다

        - 일반적인 GROUP BY 의 경우는 16 그룹의 표현식을 허용한다.

        - 컬럼이나 표현식을 명시적으로 GROUP BY에 지정할 수 없다.

        - GROUP BY 절에 나타나는 컬럼들의 최대 크기는 900바이트 이다.

　

다음은 GROUPING 함수에 대한 이야깁니다.

  

SELECT royalty, SUM(advance) 'total advance', 

GROUPING(royalty) 'grp'

FROM titles

GROUP BY royalty WITH ROLLUP

　

크게 중요한 내용은 아닙니다만..

GROUPING 함수

각 결과 행이 CUBE, ROLLUP 연산자들에 의해 계산된 것인지를 알기 위해 사용된다

GROUPING(column_name)

  - 각 행에 결과 값을 위한 새로운 컬름을 추가하여 어느 것에 의해 계산 되었는지를 나타낸다.

  - GROUP BY 절에 나타나는 컬럼에 적용된다.