■ 집합연산자
"조인이 서로 다른 테이블에 있는
컬럼들을 양 옆으로 붙여서
출력하는 sql 문법이었다면
집합 연산자는 다른 테이블에 있는
컬럼들을 위 아래로 붙여서
출력하는 sql 문법 이다.
■ 집합 연산자의 종류
1. union : 합집합 연산자
2. union all : 합집합 연산자
3. intersect : 교집합 연산자
4. minus : 차집합 연산자
문제264.
직업, 직업별 토탈월급을 출력하시오
select job, sum(sal)
from emp
group by job;
문제265.
사원테이블의 토탈월급을 출력하시오
select sum(sal)
from emp;
화면 캡처: 2018-10-24 오전 9:58
■UNION ALL
2개의 컬럼을 위아래로 붙여서 출력한다.
이 때 2개 칼럼의 중복값을 허용한다.
문제266.
위의 두개의 결과를 아래와 같이
하나로 합쳐서 출력하시오
* union all 사용
select job, sum(sal)
from emp
group by job
union all
select to_char(null) as job, sum(sal)
from emp;
튜닝후
select job, sum(sal)
from emp
group by rollup(job);
※집합 연산자를 사용할 때 주의사항 2가지
1. 위아래의 컬럼의 갯수와
데이터 타입이 같아야 한다
2. order by 절은 맨 아래의 쿼리에만
사용할 수 있다.
(쿼리 2개여도 맨 아래 한번)
문제267.
위의 sql결과를 아래와 같이 출력하시오
튜닝후
select job, sum(sal)
from emp
group by cube(job);
튜닝전(유니온 올+ 오더바이)
select job, sum(sal)
from emp
group by job
UNION all
select null job, sum(sal)
from emp
ORDER BY job nulls first ;
order by와 nulls first 를 이용해서
토탈값을 위로 끌어올림.
문제268.
통신사, 통신사별 인원수를 출력하는데
맨 아래쪽에 전체 인원수를 출력하시오
에러
select lower(telecom), count(*)
from emp2
group by lower(telecom)
union all
select to_char(null) as lower(telecom), count(*)
from emp2;
***별칭지정에 함수 쓰면 안됩니다
답
select lower(telecom), count(*)
from emp2
group by lower(telecom)
union all
select to_char(null) as telecom, count(*)
from emp2;
문제269.
위의 결과를 아래와 같이 통신사를
abcd 순으로 출력하시오
왜 에러?
select lower(telecom), count(*)
from emp2
group by lower(telecom)
union all
select to_char(null) as telecom, count(*)
from emp2
order by telecom asc;
***별칭을 telecom 으로 하면
원래 있는 컬럼명이랑 일치해서
컴퓨터 에러뜸
수정>>>>>>
select lower(telecom) LT, count(*)
from emp2
group by lower(telecom)
union all
select to_char(null) as LT, count(*)
from emp2
order by LT asc;
문제270.
아래 SQL을 튜닝하시오
select lower(telecom), count(*)
from emp2
group by lower(telecom)
union all
select to_char(null) as telecom, count(*)
from emp2
order by telecom asc;
select lower(telecom), count(*)
from emp2
group by rollup(lower(telecom))
order by lower(telecom) asc;
문제271.
부서번호, 부서번호별 평균월급을 출력하는데
맨 아래쪽에 전체 평균월급도 출력하시오
select deptno, round(avg(sal))
from emp
group by deptno
union all
select to_number(null) as deptno, round(avg(sal))
from emp;
deptno 가 숫자니까
데이터타입 맞춰줘야 한다
문제272.
위의 결과를 아래와 같이 나오게
다시 출력하시오
select deptno, round(avg(sal))
from emp
group by deptno
union all
select to_number(null) as deptno, round(avg(sal))
from emp
order by deptno asc;
문제273.
아래의 SQL 을 튜닝하시오
select deptno, round(avg(sal))
from emp
group by deptno
union all
select to_number(null) as deptno, round(avg(sal))
from emp
order by deptno asc;
튜닝후
select deptno, round(avg(sal))
from emp
group by rollup(deptno)
order by deptno asc;
■UNION
"union all 과 다른점은
데이터의 중복을 제거하면서
데이터가 정렬이 되어서 출력된다"
create table emp2_backup
as
select *
from emp2;
select * from emp2_backup;
지워지거나
수정되지 못하게
백업하는 코딩
■union all 과 union 비교
select telecom
from emp2
union all
select telecom
from emp2_backup;
※ 데이터가 중복이 제거가 되었고 정렬도 되었다.
예:
select deptno, sum(sal)
from emp
group by deptno
union all
select to_number(null) as deptno, sum(sal)
from emp;
select deptno, sum(sal)
from emp
group by deptno
union
select to_number(null) as deptno, sum(sal)
from emp;
union 은 데이터중복도 제거하고
오름차순으로 정렬되어있다.
근데 union은 정렬하느라 메모리 먹어치워서
union all 이 성능이 더 좋다고 말할 수 있다.
정렬할 필요가 없다면 안 하는게 좋다
※위의 두 개의 SQL 중에
위에 쓴 union all 이 더 성능이 좋다.
왜냐면 정렬작업 때문이다.
정렬 작업을 할 때는 데이터의 양만큼
cpu와 메모리를 사용한다.
■MINUS
예:
emp2 테이블에는 존재하는데
emp2_backup 테이블에는
존재하지 않는
학생들의 이름을 출력하시오
select ename
from emp2
minus
select ename
from emp2_backup;
(백업 테이블에서 삭제한 lg 학생수만큼이
나온다)
문제274.
부서테이블에는 존재하는 부서번호인데
사원 테이블에는 존재하지 않는
부서번호를 출력하시오
select deptno
from dept
minus
select deptno
from emp;
gate 에서 에프7 을 누르면
실행순서를 볼 수 있다.
정렬하고->중복제거하고->마이너스 했다
(아래에서 위로 올라감)
dept - emp
10 10
20 10 ------->정렬
30 10
40 10
.
.
.
↓
dept - emp
10 10
20 20 ------------>중복제거
30 30
40
↓
dept - emp = 40
문제275.
사원 테이블에는 존재하는 부서번호인데
부서 테이블에는 존재하지 않는
부서번호를 출력하시오
select deptno
from emp
minus
select deptno
from dept;
빼고 남는 값이 없어서
알 수 없음으로 뜸.
■INTERSECT
교집합
예:
사원 테이블과 부서 테이블 간의
부서번호가 공통된 것이 무엇인가
select deptno
from emp
intersect
select deptno
from dept;
■레포팅 함수
1. rollup
2. cube
3. grouping sets
-GROUPING SETS
여러개의 컬럼을 하나의 ROW에 그룹화 시켜주는 함수다.
예: 아래의 결과를 grouping sets 로
구현하시오
select job, sum(sal)
from emp
group by grouping sets( job, () );
grouping sets ( job , () )
job 에 해당하는 sal 값을 그룹화해서
토탈값을 뽑아라
sal은 sum으로 그룹화 되있어서
그냥 ( ) 표현.
문제276.(점심시간 문제)
아래의 grouping sets 의 결과를
union all 로 구현하시오
select deptno, job, sum(sal)
from emp
group by
grouping sets((deptno, job), (deptno), () );
select deptno, job, sum(sal) l
from emp
group by deptno, job
union all
select deptno, null, sum(sal) l
from emp
group by deptno
union all
select null, null, sum(sal) l
from emp
order by deptno, l;
sum(sal) 별칭지정 해줘야 하는이유
>>계산 함수니까.
위의 sum(Sal) 을 끌어오라는 건지
새로이 sum(sal) 하라는 건지
컴퓨터는 알 수 없음.
문제277.
부서번호, 부서번호별 토탈월급을 출력하는데
맨 아래쪽에 전체 토탈월급을 출력하시오
(grouping sets 를 활용)
select deptno, sum(sal)
from emp
group by grouping sets( (deptno),() );
괄호로 묶으면 따로따로 그루핑 하는거.
토탈값 빼고 출력하면
deptno 그룹화 해서 나온것 처럼 나옴.
select deptno, sum(sal)
from emp
group by grouping sets( (deptno) );
※grouping sets 가 rollup 과의 다른점은
grouping 된 결과를 마음데로 지정할 수
있다라는 것이다.
문제278.
부서번호, 직업, 부서번호별 직업별
토탈월급을 출력하시오
select deptno, job, sum(sal)
from emp
group by grouping sets( (deptno, job) )
=
select deptno, job, sum(sal)
from emp
group by (deptno, job)
order by deptno, job;
문제279. 위의 결과를 grouping sets 로 나오게 하시오
select deptno, job, sum(sal)
from emp
group by grouping sets( (deptno, job) )
order by deptno, job;
문제280.
위의 결과에서 맨 아래쪽에 전체 토탈월급을 하나 출력하시오.
select deptno, job, sum(sal)
from emp
group by grouping sets( (deptno, job), () )
order by deptno, job;
문제281.
아래와 같이 결과를 출력하시오
select ename, sal, sum(sal)
from emp
group by grouping sets((ename, sal), () )
order by ename, sal;
**동명이인이 없으니까
이름 그루핑해도
각각 연봉이나 합계연봉이나 같다.
문제282.
위의 결과를 레포팅함수 쓰지 말고 union all 을 써서 출력하시오
select ename, sum(sal)
from emp
group by ename
union all
select null, sum(sal)
from emp
order by ename;
grouping sets와 union all 중 뭐가 더 성능이 좋은가?
grouping sets 가 좀 더 좋다.
문제283.
아래의 union all 의 결과를 grouping sets 로 구현하시오
select deptno, job, sum(sal)
from emp
group by deptno, job
union all
select deptno, to_char(null) as job, sum(sal)
from emp
group by deptno
order by deptno, job;
select deptno, job, sum(sal)
from emp
group by grouping sets( (deptno,job), (deptno) )
order by deptno, job;
문제284.
아래의 sql 결과를 union all 로 해보시오
select deptno, job, sum(sal)
from emp
group by
grouping sets( (deptno, job), (deptno), ( ) );
select deptno, job, sum(sal)
from emp
group by deptno, job
union all
select deptno, null, sum(sal)
from emp
group by deptno
union all
select null, null, sum(sal)
from emp
order by deptno, job;
에러나옴 왜죠?
**************
지금 그룹으로 묶인게
(deptno, job), deptno, sum(sal) 이다.
order by로 그룹은 그룹끼리 써야한다.
deptno, job 을 동시에 썼는데도 왜 안되냐면
order by절에서 쌍으로 먹인적은 본 적없다.
(안되는걸로 알고있음 속편함)
그래서 그룹으로 안 묶인 job 이 오면
order by가 안먹힌다.
select deptno, job, sum(sal) l
from emp
group by deptno, job
union all
select deptno, null, sum(sal) l
from emp
group by deptno
union all
select null, null, sum(sal) l
from emp
order by deptno, l;
SELECT deptno,job,SUM(sal)
FROM EMP
GROUP BY deptno,job
UNION ALL
SELECT deptno,TO_CHAR(NULL) AS job, SUM(sal)
FROM emp
GROUP BY DEPTNO
UNION ALL
SELECT TO_number(null) AS deptno,
TO_CHAR(null) AS job,
SUM(sal)
FROM emp
ORDER BY deptno, job;
**************
이게 되는 이유는
하나의 값으로 지정해준 걸
끌어와서 order by 해주는거라 됨.
문제285.
아래와 sql 과 같은 결과를 grouping sets 로 출력하시오
select deptno, to_char(null) as job, sum(sal)
from emp
group by deptno
union all
select to_number(null)as deptno , job, sum(sal)
from emp
group by job;
select deptno, job, sum(sal)
from emp
group by
grouping sets( (deptno), (job) );
문제286.
아래의 결과를 출력하시오
select ename, job, sum(sal) S
from emp
group by grouping sets((ename,job), () )
order by ename, S;
■ rollup 으로 위의 결과를 구현
예:
select deptno, sum(sal)
from emp
group by rollup(deptno);
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job);
롤업 출력 값
1. deptno, job
2. deptno
3. total
rollup(deptno, job) 에서 출력되는게
deptno, job
deptno
( )
뒤에서 하나씩 없어지고
최종적으로 토탈값 나온다고 생각한다.
즉, roullup 값은 지정2개 하면 3개나오고
지정3개 하면 4개 나온다.
출력순서값은
뒤에서부터 하나씩 사라지고
맨 마지막이 토탈값이다.
문제287.
아래의 결과를 출력하시오
(grouping sets 써서)
rollup(deptno, job) 해서 나온 값
위 표를 아래표처럼↓
select deptno,
decode( deptno, null, '전체토탈',
nvl(job,'부서토탈') ) as job ,
sum(sal)
from emp
group by
grouping sets((deptno,job),(deptno),());
SELECT deptno,
DECODE(job, null,DECODE(deptno,NULL, '전체토탈','부서토탈'),job) job, SUM(sal)
FROM emp
GROUP BY ROLLUP ( deptno, job);
SELECT deptno, job, SUM(sal)
FROM EMP
GROUP BY deptno, job
UNION ALL
SELECT deptno,
NVL(TO_CHAR(NULL), '부서토탈') job,
SUM(sal)
FROM EMP
GROUP BY deptno
UNION ALL
SELECT TO_NUMBER(null) deptno, NVL(TO_CHAR(NULL), '전체토탈') job, SUM(sal)
FROM EMP
ORDER BY deptno, job;
'sql' 카테고리의 다른 글
11-1. 데이터 조작 언어(TCL문) (0) | 2019.03.30 |
---|---|
11. 데이터 조작 언어(DML문) (0) | 2019.03.30 |
9. SUBQUERY (0) | 2019.03.30 |
8. JOIN (0) | 2019.03.30 |
7. DATA 분석함수(rank, dens_rank, listagg, ntile, lead, lag) (0) | 2019.03.30 |