소계 함수
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는 자신이 원하는 조합의 결과 도출을 원할 때
사용하는것이 바람직해 보입니다.
혹여나 틀린 개념이 잡혀있다면 댓글을 통해 지적해주시면 감사하겠습니다!
'SQL > 추가 공부' 카테고리의 다른 글
SQLD_중요 개념 총정리(feat.노랭이(개정 후)) (0) | 2024.05.21 |
---|