소계 함수(ROLLUP, CUBE)_(feat. 노랭이 p.94 82번)[개정 후]

SQLD

소계 함수

 SQLD 공부중 SQL 활용 부분에서 ROLLUP과 CUBE가 이해가 잘 안가서 작성합니다.
손으로는 뭔가 잘 와닿지 않아서 Chat GPT와 함께 합니다.

ROLLUP

ROLLUP은 간단한 예를 들면,
ROLLUP(A, B)라는 구문이 나오면, A별, (A,B)별 총 합계를 나타낸다. 사실 이말은 예시가 없으면 이해가 잘 안되기 때문에 GPT와 함께 간단한 예시를 살펴보며 이해를 하겠습니다.

우선 테이블을 하나 만들어주겠습니다.

-- DEPT 테이블 예시
CREATE TABLE DEPT (
    DEPTNO INT,
    DNAME VARCHAR(50)
);

INSERT INTO DEPT (DEPTNO, DNAME) VALUES
(10, 'ACCOUNTING'),
(20, 'RESEARCH'),
(30, 'SALES'),
(40, 'OPERATIONS');

-- EMP 테이블 예시
CREATE TABLE EMP (
    EMPNO INT,
    ENAME VARCHAR(50),
    JOB VARCHAR(50),
    MGR INT,
    HIREDATE DATE,
    SAL DECIMAL(10, 2),
    COMM DECIMAL(10, 2),
    DEPTNO INT
);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000.00, NULL, 20),
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10),
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30),
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20),
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);

테이블을 완성하고, 이후 결과를 출력하기 위해 ROLLUP을 활용한 SQL 구문을 작성합니다.

SELECT DNAME, JOB, COUNT(EMPNO) AS TOTAL_EMP, SUM(SAL) AS TOTAL_SAL
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP(DNAME, JOB);

결과를 보기전에 예상해보면
DEPT 테이블의 DEPTNO와 EMP 테이블의 DEPTNO가 동일한 조건하에
DNAME에 대한 합계를, DNAME과 JOB을 연관지은 합계를 출력하게 될 것 같습니다.
실제 출력 결과를 보면

DNAME	JOB	TOTAL_EMP	TOTAL_SAL
ACCOUNTING	CLERK	1	1300.00
ACCOUNTING	MANAGER	1	2450.00
ACCOUNTING	PRESIDENT	1	5000.00
ACCOUNTING	NULL	3	8750.00
RESEARCH	ANALYST	2	6000.00
RESEARCH	CLERK	1	800.00
RESEARCH	MANAGER	1	2975.00
RESEARCH	NULL	4	9775.00
SALES	CLERK	1	950.00
SALES	MANAGER	1	2850.00
SALES	SALESMAN	4	5850.00
SALES	NULL	6	9650.00
NULL	NULL	13	28175.00

이렇게 결과 테이블이 생성됩니다.
각 행마다 설명을 하자면 아래와 같습니다.

• DNAME과 JOB이 모두 명시된 행 (예: ACCOUNTING, CLERK): 해당 부서의 특정 직무에 대한 직원 수와 총 급여.
  • 예: ACCOUNTING, CLERK => 직원 수 1명, 총 급여 1300.00
• JOB이 NULL인 행 (예: ACCOUNTING, NULL): 해당 부서의 모든 직무에 대한 직원 수와 총 급여.
  • 예: ACCOUNTING, NULL => 직원 수 3명, 총 급여 8750.00
• DNAME이 NULL인 행 (예: NULL, NULL): 모든 부서와 모든 직무에 대한 전체 직원 수와 총 급여.
  • 예: NULL, NULL => 직원 수 13명, 총 급여 28175.00

즉, ROLLUP(A, B)는 (A,B)에 대한 합계를 제시하고 A에 대한 합계를 제시합니다. 그리고 마지막에 모든 그룹에 대한 연산 결과를 나타냅니다.

 

CUBE

CUBE(A, B)는 A에 대한, B에 대한, (A,B)에 대한, 그룹모두에 대한 연산결과를 출력합니다.
이해를 위해 위 테이블에서 ROLLUP을 CUBE로만 바꿔서 실행시켜 보겠습니다.

SELECT DNAME, JOB, COUNT(EMPNO) AS TOTAL_EMP, SUM(SAL) AS TOTAL_SAL
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE(DNAME, JOB);

결과 출력

DNAME	JOB	TOTAL_EMP	TOTAL_SAL
ACCOUNTING	CLERK	1	1300.00
ACCOUNTING	MANAGER	1	2450.00
ACCOUNTING	PRESIDENT	1	5000.00
ACCOUNTING	NULL	3	8750.00
RESEARCH	ANALYST	2	6000.00
RESEARCH	CLERK	1	800.00
RESEARCH	MANAGER	1	2975.00
RESEARCH	NULL	4	9775.00
SALES	CLERK	1	950.00
SALES	MANAGER	1	2850.00
SALES	SALESMAN	4	5850.00
SALES	NULL	6	9650.00
NULL	CLERK	3	3050.00
NULL	MANAGER	3	8275.00
NULL	PRESIDENT	1	5000.00
NULL	ANALYST	2	6000.00
NULL	SALESMAN	4	5850.00
NULL	NULL	13	28175.00

각각의 행별 설명을 붙이면,

• DNAME과 JOB이 모두 명시된 행 (예: ACCOUNTING, CLERK): 해당 부서의 특정 직무에 대한 직원 수와 총 급여.
  
  • 예: ACCOUNTING, CLERK => 직원 수 1명, 총 급여 1300.00
• JOB이 NULL인 행 (예: ACCOUNTING, NULL): 해당 부서의 모든 직무에 대한 직원 수와 총 급여.
  • 예: ACCOUNTING, NULL => 직원 수 3명, 총 급여 8750.00
• DNAME이 NULL인 행 (예: NULL, CLERK): 모든 부서의 특정 직무에 대한 직원 수와 총 급여.
  • 예: NULL, CLERK => 직원 수 3명, 총 급여 3050.00
• DNAME과 JOB이 모두 NULL인 행 (예: NULL, NULL): 모든 부서와 모든 직무에 대한 전체 직원 수와 총 급여.
  • 예: NULL, NULL => 직원 수 13명, 총 급여 28175.00

즉, CUBE는 모든 가능한 조합에 대해 연산 결과를 도출합니다.

 

GROUPING SETS

GROUPING SETS는 ROLLUP과 CUBE 보다 좀 더 유연하고, 명확한 그룹화 집계를 도와줍니다.
만약, 위 CUBE 예시와 같게 도출하려 하면,

SELECT DNAME, JOB, COUNT(EMPNO) AS TOTAL_EMP, SUM(SAL) AS TOTAL_SAL
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (
    (DNAME, JOB),
    (DNAME),
    (JOB),
    ()
);

이와 같이 세부적으로 나눠서 처리하면 됩니다.

 

결론

결국 원하는 값에 따라서 함수를 잘 선택해서 쓰는 것이 중요합니다.
제가 이해한대로 요약하면,
ROLLUP(A, B)는 A, (A,B), 모든 에 대한 집계 결과를,
CUBE는 발생 가능한 모든 조합의 집계 결과를,
GROUPING SETS는 자신이 원하는 조합의 결과 도출을 원할 때
사용하는것이 바람직해 보입니다. 

 

혹여나 틀린 개념이 잡혀있다면 댓글을 통해 지적해주시면 감사하겠습니다!