[SQL 입문] 그룹함수의 집합연산자; union, union all, intersect, minus

- 집합연산자 선언 위치

SELECT 컬럼
FROM 테이블
집합연산자
SELECT 컬럼
FROM 테이블
[ORDER BY 정렬]

: SELECT문과 SELECT문 사이에 선언

- 집합연산자를 선언하는 select문

• 컬럼 개수와 컬럼 타입이 일치해야함
• order by 절은 마지막 select 문에서만 선언할 수 있음
• 결과 컬럼 > 첫번째 select문에 선언된 컬럼명 또는 alias

 

- 집합연산자의 종류

• union (합집합) : select의 결과집합(resultset)에서 첫번째 컬럼으로 정렬수행 후 비교해서 중복된 레코드를 제거해서 결과집합에 한번만 포함시킴 (cost가 비싼 연산 수행)
• union all (합집합) : 정렬 없이 첫번째 결과집합 뒤에 두번째 select의 결과집합을 append > 중복제거X
• intersect (교집합) : select의 결과집합(resultset)에서 첫번째 컬럼으로 정렬수행 후 중복된 레코드를 결과 집합에 한번만 포함 > 교집합만 출력 (cost가 비싼 연산 수행)
• minus (차집합) : select의 결과집합(resultset)에서 첫번째 컬럼으로 정렬 후 비교해서 첫번째 결과집합에서 두번째 결과집합에 속하는 레코드 제외 후 반환 >> 즉, 중복값 전체 제외 수 반환 >> 교집합도 제외 (cost가 비싼 연산 수행)

 

conn c##scott/oracle
desc job_history --과거 근무 이력(부서번호, 직무)
select * from job_history;

-- union 과 union all의 차이

select employee_id, job_id, department_id
from employees --20건
union all
select employee_id, job_id, department_id 
from job_history; --10건   >>총 30건
--------
select employee_id, job_id, department_id 
from employees --20건
union 
select employee_id, job_id, department_id 
from job_history;  -- 총 29rows(현재 직무, 부서를 이전에도 동일부서의 직무로 근무 이력이 존재하는 사원 1명)

 

Q. 현재 직무 와 부서를 과거에도 동일부서에서 동일직무 담당했던 사원 조회

select employee_id, job_id, department_id 
from employees --20건
intersect
select employee_id, job_id, department_id
from job_history;  --전체 사원에서 history내역에 있는 사원만 추출(교집합)

 

Q. 입사 이후에 한번도 직무와 부서를 변경하지 않은 사원 조회

select employee_id 
from employees   
minus
select employee_id 
from job_history;


select employee_id 
from employees 
order by 1   --정렬은 가장 마지막에만 
intersect
select employee_id 
from job_history
order by 1;  --error

 

---

- group by와 함께 사용하는 연산자 종류

• 대상 테이블에 1번  access해서 subtotal, grand total, cross tabulation 등의 결과 집합 생성
• rollup
  : 참여하면 0, 안하면 1
  : subtotal, grand tota
  : 보고서를 작성할 때 집합에서 통계 및 요약정도를 추출하는데 사용
  : groupong columns의 수를 N이라고 했을 N+1 level의 subtotal 생성
  

[규칙]
group by rolllup (A, B)  --1 -> group by (A, B) -> group by (A) -> group by ()	group by rolllup (A, B, C) --2 -> group by (A, B, C) -> group by (A, B) -> group by (A) -> group by ()

• cube
  : subtotal, grand total, cross tabulation
  : 인수들에 대한 계층별 집계를 구할 수 있음
  : group by 절에 N개의 열이 있을 경우 집계 조회수 = 2ⁿ개
  : cross tabulation 결과 집합을 산출하는데 사용

[규칙]
group by rolllup (A, B)  --1 -> group by (A, B) -> group by (A) -> group by (B) -> group by ()	group by rolllup (A, B, C) --2 -> group by (A, B, C) -> group by (A, B) -> group by (A, C) -> group by (B, C) -> group by (A) -> group by (B) -> group by (C) -> group by ()

• grouping sets
  : GROUP BY 절의 확장 기능으로 데이터를 여러 개로 그룹화 하도록 지정가능 > 그룹핑 조합 나열
  : GROUP BY SQL문장을 여러번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있음
  : ROLLPU과 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과 동일

 

-- rollup

Q. 전체사원의 급여평균, 부서별 사원들의 급여평균, 부서와 직무별 그룹핑한 사원들의 급여 평균을 단일 결과 집합으로 출력 (3개의 select문을 집합연산자를 사용하여 단일 결과 집합 생성)

SELECT to_number(null), ' ', round(avg(sal), 2)
FROM emp
union all --중복된 레코드가 없음 >> 정렬을 할 필요가 없음
SELECT deptno, ' ',round(avg(sal), 2)
FROM emp
GROUP BY deptno
union all
SELECT deptno, job, round(avg(sal), 2)
FROM emp
GROUP BY deptno, job; --홍길동 사원때문에 null이 나옴

-- 동일한 테이블을 3번 읽음 >> 비효율적

SELECT deptno, job, avg(sal), grouping(deptno), grouping(job)
FROM emp
GROUP BY rollup(deptno, job);

--테이블을 1번 읽어서 위와 동일한 결과 집한 생성

 

--cube

Q. 전체 사원들의 급여 평균, 부서별 사원들의 급여 평균, 직무별 사원들의 평균급여, 부서와 직무별 그룹핑한 사원들의 급여 평균을 단일 결과 집합으로 출력(4개의 select 문을 집합 연산자를 사용하여 단일 결과 집합 생성)

SELECT to_number(null), ' ', round(avg(sal), 2) 
FROM emp
union all
SELECT deptno, ' ', round(avg(sal), 2) 
FROM emp GROUP BY deptno
union all
SELECT to_number(null), job, round(avg(sal), 2) 
FROM emp GROUP BY job 
union all
SELECT deptno, job, round(avg(sal), 2) 
FROM emp GROUP BY deptno, job;

select deptno, job, avg(sal)
from emp
GROUP BY cube(deptno, job);

--grouping sets 

Q. 전체 사원들의 평균급여 , 부서와 직무별 사원들의 평균급여, 관리자와 직무별 사원들의 평균급여를 하나의 결과집합으로 출력하시오. 

SELECT deptno, job, avg(sal)
FROM EMP
GROUP BY deptno, job
union all
SELECT mgr, job, avg(sal)
FROM EMP
GROUP BY mgr, job
union all
SELECT to_number(null), to_char(null), avg(sal)
FROM EMP;

-- grouping sets 
SELECT deptno, job, mgr, avg(sal)
FROM EMP
GROUP BY grouping sets((deptno, job), (mgr, job), ());

 

* 내용참고&출처 : 태그의 수업을 복습 목적으로 정리한 내용입니다.