[ 실습 내용 ]
6. 집합 연산자
집합 연산자의 종류
UNION: 중복 행을 제거한 후 양쪽 쿼리에서 행 반환UNION ALL: 모든 중복 행을 포함하여 양쪽 쿼리 행 반환 (그대로 붙이는 것)INTERSECT: 양쪽 쿼리에 공통되는 행 반환MINUS: 선행 쿼리에는 있지만 후속 쿼리에는 없는 행 반환
집합 연산자 사용 규칙
- 두 쿼리 간 SELECT절의 표현식 개수가 일치해야 함 (안 맞으면 NULL로 채움)
- 선행 쿼리 각 열의 데이터 유형 = 후속 쿼리 각 열의 데이터 유형
- 컬럼 이름은 선행 쿼리의 열 이름과 동일하게 출력 (후속 쿼리에 alias 해둬도 소용 ❌)
UNION ALL의 경우를 제외하고 오름차순 출력UNION ALL의 경우를 제외하고 중복 행은 자동 제거
7. ROLLUP과 CUBE
:ROLLUP과 CUBE 모두 그룹 함수(GROUP FUNCTION) 중 하나로
SQL문 하나로 소그룹 간 소계, 다차원적 소계를 계산할 수 있게함
-ROLLUP() : GROUP BY절과 함께 사용되어 소계, 총계를 구해줌
-CUBE() : GROUP BY 항목들 간 모든 경우의 수로 그룹을 생성하게 집계, ROLLUP()보다 더 세세하게 나올 수 있는 모든 소계와 총계를 구해서 반환
ROLLUP의 원리
위의 이미지가 ROLLUP의 원리를 가장 쉽게 설명한 것이라고 생각한다.
수업 시간에 이해가 힘들어서 열심히 구글링하다가 마이자몽님 블로그를 찾았는데, 여기 여러 예시와 함께 설명이 정말 깔끔하게 되어있다. 👍👍👍
ROLLUP은 GROUP BY와 함께 쓰여 소그룹 간 소계(전제 행이 다 인자로 들어있으면 총계도 구함)를 구함
- ROLLUP은 괄호 안에 인자로 들어온 값을 오른쪽에서부터 하나씩 빼서 그룹을 만듦
- 그룹을 기준으로 소계를 구해서 반환함
- 괄호 안이 다 빌 때까지 이를 반복함
- 괄호 안에 괄호를 넣어 두 개 이상 행을 하나로 묶으면
ROLLUP(A,(B,C)), 이를 행 하나로 보고 빠질 때 한 번에 빠짐 - 특정 행을 고정된 기준으로 잡고 싶다면, ROLLUP 인자에서 빼서 GROUP BY 뒤에 배치, 이렇게 하면 전체가 빠진 전체 총계를 구하지 못함
CUBE의 원리
(ROLLUP 원리와 비슷한 방식으로 CUBE 원리 설명하는 이미지 만들어 봄..내가 이해가 잘 안돼서..)
CUBE은 GROUP BY와 함께 쓰여 쿼리에서 나올 수 있는 모든 소그룹 간 소계를 구함
-
CUBE는 입력 받은 인자로 만들 수 있는 모든 소그룹을 만듦
-
그룹을 기준으로 소계를 구해서 반환함
-
모든 소그룹의 소계를 반환할 때까지 이를 반복함
-
괄호 안에 괄호를 넣어 두 개 이상 행을 하나로 묶으면
CUBE(A,(B,C)), 이를 행 하나로 보고 빠질 때 한 번에 빠짐 -
특정 행을 고정된 기준으로 잡고 싶다면, CUBE 인자에서 빼서 GROUP BY 뒤에 배치, 이렇게 하면 전체가 빠진 전체 총계를 구하지 못함
ROLLUP과 CUBE의 차이
✅ ROLLUP 수행 결과 소계 갯수 = (ROLLUP 인자 수) + 1
✅ CUBE 수행 결과 소계 갯수 = 2^(CUBE 인자 수)
[ 복습 문제 풀이 ]
문제 1. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, JOB, SUM(sal)
FROM emp
GROUP BY ROLLUP((TO_CHAR(hiredate, 'YYYY'), JOB), deptno);- HD_YYYY 값과 job 값은 있을 때 같이 있고, 빠질 때 같이 빠짐
= 하나의 행처럼 동작 =((TO_CHAR(hiredate, 'YYYY'), JOB))묶어줌 - deptno 값이 먼저 빠지면서 소계 구해지고, 마지막에 모든 행 빠지니까
ROLLUP((TO_CHAR(hiredate, 'YYYY'), JOB), deptno)
문제 2. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, SUM(sal) SUM_SAL
FROM emp
GROUP BY ROLLUP(deptno, TO_CHAR(hiredate, 'YYYY'));- HD_YYYY 값이 먼저 빠지면서 소계 구해지고 마지막에 모든 행 다 빠지니까
HD_YYYY를 더 오른쪽에 배치해서ROLLUP(deptno, TO_CHAR(hiredate, 'YYYY'))
문제 3. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, JOB, SUM(sal) SUM_SAL
FROM emp
GROUP BY job, ROLLUP((TO_CHAR(hiredate, 'YYYY'), deptno));- deptno 값과 HD_YYYY 값이 함께 있거나 함께 빠짐
= 하나의 행처럼 동작 =((TO_CHAR(hiredate, 'YYYY'), deptno))묶어줌 - job 값은 마지막까지 빠지지 않음
= 고정된 기준 = ROLLUP 인자에서 빼서 GROUP BY 뒤에 배치
문제 4. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, JOB, SUM(sal) SUM_SAL
FROM emp
GROUP BY TO_CHAR(hiredate, 'YYYY'), job, ROLLUP(deptno);- HD_YYYY 값과 job 값은 빠지지 않고 고정되어 있고, deptno 값만 빠졌다 들어왔다함
= HD_YYYY, job은 고정 기준, deptno만 ROLLUP
=GROUP BY TO_CHAR(hiredate, 'YYYY'), job, ROLLUP(deptno)
문제 5. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, SUM(sal) SUM_SAL
FROM emp
GROUP BY CUBE(deptno, TO_CHAR(hiredate, 'YYYY'));- 하나씩 순차적으로 빠지기만 하는게 아니라 2개 행을 가지고 총 4가지 경우 소계를 구함(2^2=4) =
CUBE - 두 행이 각각 빠졌다 들어왔다하므로 더 묶지 않고 CUBE 인자로 입력
=CUBE(deptno, TO_CHAR(hiredate, 'YYYY'))
문제 6. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, JOB, SUM(sal)
FROM emp
GROUP BY job, ROLLUP((deptno, TO_CHAR(hiredate, 'YYYY')));- deptno 값과 HD_YYYY 값이 함께 있거나 함께 빠짐
= 하나의 행처럼 동작 =((deptno, TO_CHAR(hiredate, 'YYYY')))묶음 - job 값은 고정되어 빠지지 않음 = 고정 기준 = GROUP BY 뒤에 배치
문제 7. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, SUM(sal)
FROM emp
GROUP BY deptno, TO_CHAR(hiredate, 'YYYY');- 소계가 하나도 구해지지 않음 = ROLLUP이나 CUBE를 사용하지 않음
문제 8. 아래와 같은 테이블을 ROLLUP 혹은 CUBE를 사용하여 출력하시오
SELECT deptno, TO_CHAR(hiredate, 'YYYY') HD_YYYY, job, SUM(sal)
FROM emp
GROUP BY CUBE(deptno, (TO_CHAR(hiredate, 'YYYY'), job));- HD_YYYY 값과 job 값은 함께 움직임 = 한 행처럼 동작 = 묶어줌
- 두 개 행으로 총 4가지 경우 소계 구함(2^2=4) =
CUBE
