SQL) ROLLUP, CUBE 함수에 대한 이해와 고찰

# SQL 전문가 가이드만으로는 미천한 나의 머리가 따라가지 못했다. 

# ROLLUP 과 CUBE 함수의 작동원리가 너무 궁금한 나머지 구글링과 직접실험을 하여 얻은 지식을 여기에 공유하고자 한다. 

 

1. ROLLUP 작동원리

 

      

출처 : https://myjamong.tistory.com/191

 위 그림 한장이면 충분하다고 본다. 그래도 이해가 되지않으면 밑의 CUBE 함수 작동원리 예제를 보며, 출력 값을 자세히 들여다보고, 실제로 ROLLUP 함수를 DB에서 돌려본다면 이해가 빠를것이다.

 

출처에 표기된 사이트의 포스팅도 참고하면 이해에 도움이 될 것이다.

 

 

---

 

 

2. CUBE 함수 작동원리 

 - SQL 전문가 가이드에서는 칼럼의 수가 N이라고 할때 2의 N제곱 LEVEL의 Subtotal을 생성한다고 한다.

 - 책에서 등장한 LEVEL 에 대한 예시와 설명은 단 한번 등장한다. 

   ( L1, L2, L3 을 각각 GROUPBY 표준 집계, SUBTOTAL, GRAND TOTAL 로 표현되어진 예시 밖에없다)

 - 책의 설명을 그대로 흡수가능하다면 좋겠지만, 미천한 나는 그리하지 못하여 직접 실험을 해보았다. 

 - 아래는 sql을 무료로 돌려볼 수 있는 웹사이트에서 실험한 예시 sql 문이다.

# https://sqltest.net/ 에서 작성한 SQL문입니다. (Oracle 11g 사용)

# 테이블생성 및 데이터 삽입
SQL>CREATE TABLE sql_test_a 
( 
    ID         VARCHAR2(4000 BYTE), 
    FIRST_NAME VARCHAR2(200 BYTE), 
    LAST_NAME  VARCHAR2(200 BYTE),
    X_TEST     VARCHAR2(200 BYTE)
); 

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME, X_TEST) VALUES ('1', 'R', '100', 'CO'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME, X_TEST) VALUES ('1', 'R', '300', 'CO'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME, X_TEST) VALUES ('3', 'P', '400', 'CO'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME, X_TEST) VALUES ('3', 'P', '500', 'CO'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME, X_TEST) VALUES ('5', 'P', '600', 'PO'); 
INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME, X_TEST) VALUES ('6', 'Q', '700', 'PO');

# CUBE의 작동방식을 보기 위한 SQL문
SQL>SELECT DECODE(GROUPING(A.ID),1,'ID GROUP',A.ID) AS ID
       ,DECODE(GROUPING(A.FIRST_NAME),1,'FIRST_N GROUP',A.FIRST_NAME) AS FRIST_NAME
       ,DECODE(GROUPING(A.LAST_NAME),1,'LAST_N GROUP', A.LAST_NAME) AS LAST_NAME
       ,DECODE(GROUPING(X_TEST),1,'X_TEST GROUP',A.X_TEST) AS X_TEST
       ,SUM(A.LAST_NAME) 
       FROM sql_test_a A
       GROUP BY CUBE(A.ID,A.FIRST_NAME,A.LAST_NAME,A.X_TEST)
       ORDER BY A.ID, A.FIRST_NAME;
 

 

참고) GROUPING 함수는 CUBE나 ROLLUP, GROUPING SETS 함수의 각 그룹에대한 Subtotal 이 계산된 결과에 1을 반환한다.

 

위 SQL 문의 출력결과 일부

출력을 살펴보자

 

SUM 칼럼을 제외한 나머지 4개의 칼럼이 있다.

(실제 CUBE 함수에 사용된 칼럼은 SUM칼럼을 제외한 4개이다.)

 

DECODE  함수를 사용하여 문자열이 출력되는 경우를 1, 문자열이 출력되지 않는경우를 0 이라 가정하자.

이에 맞춰, 1번째행을 표현해본다면 0000

              2번째행을 표현해본다면 0001 

              5번째행을 표현해본다면 0010

              6번째행을 표현해본다면 0011

 

이렇게 총 경우의 수는 2의 n제곱 가지 인 것이다.

 

책에서 Grand Total 과 Sub Total 을 나눠놓고 설명하고, '2의 N제곱 LEVEL의 Subtotal을 생성한다고 한다' 라고 집필되어 있기에 나처럼 혼란이 온 사람들이 이 포스팅을 읽고 Grand Total과  GROUP BY 표준집계 또한 SubTotal에 속한다고 이해하여 혼란에서 조금이나마 벗어나는데 도움이 되었길 바란다.

 

이해안되면 파고드는 성격을 좀 고쳐야 생활이 좀 편해질텐데... 다들  SQLD 시험 파이팅하시길..!

 

 

참고) ROLLUP, CUBE 함수는 테이블의 액세스 횟수를 줄일 수 있어 자원 사용률 개선에 도움이 된다.

       (SQL 전문가 가이드 319p 참조)