본문 바로가기

sql

10. 집합연산자

728x90
반응형

 

집합연산자

 "조인 서로 다른 테이블에 있는

컬럼들을 으로 붙여서

출력하는 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;

 

728x90
반응형

'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