[SQL] UNION, 연산자, ROLLUP, GROUPING

null 값을 사용할때

where comm = null; 이 아닌 where comm is null; 이라고 하는것이 맞다.

where comm is not null은 반대의 의미이다.

 

 

집합연산자 = UNION

출력할 열의 개수와 자료형이 다를때는 오류가 난다.

 

UNION ALL은 중복되어도 보여주는 것이다.

MINUS는 차집합에 대한 것이다.

INTERSECT는 교집합에 대한 것이다.

 

UPPER(문자열) 은 괄호안 문자데이터를 모두 대문자로

LOWER(문자열) 은 괄호안 문자데이터를 모두 소문자로

INITCAP(문자열) 은 괄호안 문자데이터를 첫자만 대문자, 나머지는 소문자로 변환한다.

select UPPER('aPpLE'), LOWER('aPpLE'), INITCAP('aPpLE') from DUAL

dual 테이블은 테이블을 생성하지않고 바로바로 실행해보기 위해서 많이 쓰인다.

테스트 테이블이라고 생각하면된다.

 

 

length는 문자열의 길이를 구하는 함수이다.

select 직책, LENGTH(직책) from EMP;

substr는 문자열의 일부를 추출하는 함수이다.

select job, substr(job, 1, 2), substr(job, 3, 2) from emp;

job의 첫번째 글짜부터 2개, job의 3번째 글자부터 2개를 출력한다.

 

 

REPLACE는 특정 문자를 다른 문자로 바꾸는 함수이다.

select '010-1234-5678' as replace_before,
    REPLACE('010-1234-5678', '-', ' ') as repalce1,
    REPLACE('010-1234-5678', '-')as repalce2
from dual;

 

RPAD는 데이터의 빈공간을 특정문자로 채우는 함수이다.

select 
        RPAD('971225-', 14, '*') as RPAD_JMNO,
        RPAD('010-1234-', 13, '*') as RPAD_PHONE
    from dual;

 

CONCAT은 문자열의 데이터를 합치는 함수이다.

select  CONCAT(사원번호, 직책),
        CONCAT(사원번호, CONCAT(':', 직책))
    from EMP
   where 직책 = '대리';

 

ROUND는 특정위치에서 반올림한 값

select round(1234.4) as round,
       round(1234.5) as round2,
       round(1234.5678, 2) as round3,
       round(1234.5678, -2) as round4,
       round(1235.5678, -2) as round5
from dual;

 

TRUNC는 특정위치에서 버림한 값

select trunc(1234.4) as round,
       trunc(1234.5) as round2,
       trunc(1234.5678, 2) as round3,
       trunc(1234.5678, -2) as round4,
       trunc(1235.5678, -2) as round5
from dual;

 

CEIL은 소숫점 값을 채우고

FLOOR는 소숫점을 버린다.

select ceil(3.14) as ceil,
       floor(3.14) as floor,
       ceil(-3.14) as ceil2,
       floor(-3.14) as floor2
from dual;

 

floor는 몫을 mod는 나머지를 구하는 함수이다.

select 
        floor (7/3), 
        mod (7, 3)
from dual

 

sysdate는 날짜를 출력해주는 함수이다.

select sysdate from dual

add_months 3개월 더해준 날짜 구하기

select sysdate, add_months(sysdate, 3) from dual

months_between 두날짜간의 개월수 차이 구하는 함수

 

next_day([날짜 데이터], [요일문자]) 입력한 날짜 데이터에서 돌아오는 요일의 날짜를 반환하는 함수

last_day([날짜 데이터]) 특정 날짜가 속한 달의 마지막 날짜를 출력

select sysdate, 
        next_day(sysdate, '금요일'),
        last_day(sysdate)
    from dual

 

NVL은 첫번째 입력 데이터가 NULL이 아니면 그 데이터를 그대로 반환하고 NULL이라면 두번째 입력 데이터에 지정한 값으로 출력해준다. (값이 바뀌는 것이 아니라 출력값만 그렇게 나오는 것이다.)

select 10 + NVL(null, 10) from dual;

NVL2(부서번호, 'O', 'X') 이면 부서번호가 null값이면 O를 출력 null값이 아니면 X를 출력한다.

 

 

DB에서만 성립하는 조건인데 문자열 + 숫자열을 하면 숫자열이 문자열로 바로 붙을 수 있다.

 

 

DECODE는 switch-case문과 비슷하다고 생각하면된다.

 

 

SUM, COUNT, MAX, MIN, AVG는 집계함수라고 한다.

하나씩만 쓰일때는 문제 없다.

여러개가 같이 쓰일때 어려워진다.

SELECT  SUM(급여),
        COUNT(급여),
        MAX(급여),
        MIN(급여),
        AVG(급여)
FROM EMP

---

 

GROUP BY

 

우선 tab01에 그룹을 생성해주었다.

 

1~24										ID
A 산업부(7명)			B산업부 (8명)			C산업부 (9명)				DEPT
홍길동	홍길동	홍길동	이순신	이순신	이순신	안중근	김탁구	김구	강감찬	JOB
1, 2	3, 4, 5	6, 7	1, 2, 3, 4	5, 6, 7	8	1, 2, 3	4	5, 6, 7	8, 9	NAME

위의 표를 테이블로 만들어준다.

DROP TABLE TAB01;
CREATE TABLE TAB01(
    ID      NUMBER,
    DEPT    VARCHAR2(20),
    JOB     VARCHAR2(20),
    NAME    VARCHAR2(20),
    SAL     number
)

INSERT INTO TAB01 VALUES(1, 'A사업부', '개발', '홍길동', 300);
INSERT INTO TAB01 VALUES(2, 'A사업부', '개발', '홍길동', 540);
INSERT INTO TAB01 VALUES(3, 'A사업부', '생산', '홍길동', 153);
INSERT INTO TAB01 VALUES(4, 'A사업부', '생산', '홍길동', 553);
INSERT INTO TAB01 VALUES(5, 'A사업부', '생산', '홍길동', 323);
INSERT INTO TAB01 VALUES(6, 'A사업부', '생산', '홍길동', 246);
INSERT INTO TAB01 VALUES(7, 'A사업부', '지원', '홍길동', 522);

INSERT INTO TAB01 VALUES(8, 'B사업부', '연구', '이순신', 368);
INSERT INTO TAB01 VALUES(9, 'B사업부', '연구','이순신', 375);  
INSERT INTO TAB01 VALUES(10, 'B사업부', '연구', '이순신', 456);
INSERT INTO TAB01 VALUES(11, 'B사업부', '연구', '이순신', 568); 
INSERT INTO TAB01 VALUES(12, 'B사업부', '생산', '이순신', 685);
INSERT INTO TAB01 VALUES(13, 'B사업부', '생산', '이순신', 742);
INSERT INTO TAB01 VALUES(14, 'B사업부', '생산', '이순신', 153); 
INSERT INTO TAB01 VALUES(15, 'B사업부', '지원', '이순신', 153);

INSERT INTO TAB01 VALUES(16, 'C사업부', '연구', '안중근', 322); 
INSERT INTO TAB01 VALUES(17, 'C사업부', '연구', '안중근', 352); 
INSERT INTO TAB01 VALUES(18, 'C사업부', '연구', '안중근', 125);
INSERT INTO TAB01 VALUES(19, 'C사업부', '개발', '김탁구', 155);
INSERT INTO TAB01 VALUES(20, 'C사업부', '생산', '김구', 533);
INSERT INTO TAB01 VALUES(21, 'C사업부', '생산', '김구', 455); 
INSERT INTO TAB01 VALUES(22, 'C사업부', '생산', '김구', 111);
INSERT INTO TAB01 VALUES(23, 'C사업부', '지원', '강감찬', 832);
INSERT INTO TAB01 VALUES(24, 'C사업부', '지원', '강감찬', 186);

 

사업부별 인원수 카운드를 해주었다.

select dept, count(*) from tab01
    group by dept;

 

sal을 추가해줘서 급여를 추가해준다.

부서번호 및 직책별 평균 급여를 정렬하였다.

select dept, job, avg(sal) from tab01
    group by dept, job
    order by dept, job;

 

group by에 HAVING절을 사용하면 출력 그룹을 제한해서 출력할 수 있다.

ex) 그룹에서 연봉이 2000이상인 사람만 출력하기

 

where절과 having절의 차이는

where절은 select를 제한하는 것이고

having절은 grouping된 결과를 제한하는 것이다.

select dept, job, avg(sal)
    from tab01
    where sal <= 500		--select를 제어
    group by dept, job
        having avg(sal) >= 200		--group by를 제어		
    order by dept, job;

 

rollup을 할때의 조건이 여러개로 되어있다.

select dept, job, sum(sal) 
	from tab01 
    group by rollup(dept, job);  -- 부서총계 업무총계가 출력

select dept, sum(sal) 
	from tab01 
    group by dept, rollup(job);  -- 부서별로 업무총계 출력

select dept, sum(sal) 
	from tab01 
    group by job, rollup(dept);  -- 업무별로 부서총계 출력

부서를 통계해준것을 마지막에 업무통계로 나타내준다.

결국 A사업부 null 부분은 부서통계부분이고

null null부분은 전체부서의 전체업무통계를 나타낸것이다.

select dept, job, sum(sal) 
	from tab01 
    group by rollup(dept, job);  -- 부서총계, 업무총계

 

rollup에서 나올수 있는 모든 경우를 한번에 출력하는것이 cube이다.

select dept, job, sum(sal)
    from tab01
    group by cube(dept, job)
    order by dept, job

 

dept와 job을 union시키려면 필드값을 맞춰주기위해 각각의 위치에 null값을 넣어줘야한다.

select dept, null, count(*)
    from tab01
    group by dept
    union
select null, job, count(*)
    from tab01
    group by job;

위의 코드는 아래와같이 grouping sets으로 줄여서 표현할 수 있다.

select dept, job, count(*)
    from tab01
    group by grouping sets(dept, job)

GROUPING : 그룹화 된 것을0, 그룹화 안된 것을1으로 나타낸다

select dept,
        decode(
          grouping(job),
          0,job,
          1, '총   계'
        ),count(*)
    from tab01
group by rollup(dept, job)
order by dept

select dept, job,
        grouping(dept),
        grouping(job)
    from tab01
group by rollup(dept, job)
order by dept, job;

select dept,
          decode(
          grouping(dept)+grouping(job),
          0,job,
          1, '부서 총 인원',
          2, '전체 총 인원'
          ), count(*)
    from tab01
group by rollup(dept, job)
order by dept

SELECT DEPT, job,
    DECODE(
    GROUPING_ID(DEPT, JOB),
    0, JOB,          -- 둘 다 그룹화
    1, '부서 총 인원', -- GROUP_ID에 인수로 들어간 1번값이 그룹화
    2, '영업 총 인원', -- GROUP_ID에 인수로 들어간 2번값이 그룹화
    3, '전체 총 인원'  -- 그룹화 없음
), COUNT(*), GROUPING_ID(DEPT,JOB)
FROM tab01
GROUP BY cube(DEPT,JOB);

그루핑+그루핑은 각각 조건에 따라 2가 나오지만,

그루핑 아이디(DEPT,JOB) 이 둘 다 포함되면 3이 나온다.

그 이유는 

    둘 다 그룹화되면 0,

    첫번째 인수가 그룹화되면 1,

    두번째 인수가 그룹화되면 2,

    그룹화가 없으면 3

으로 구분해주기 위함이다.

 

여기서 중요한점은 grouping은 컬럼값을 하나를 받는 속성이라는 것이고(그래서 0과 1만 존재한다.),

grouping_id는 여러 컬럼값을 넣어서 경우의 수 만큼의 정수를 출력한다는 것이다.

(컬럼을 3개 넣을경우 경우의 수가 2^3 해서 8가지가 되기 때문에 0~7까지의 값이 출력될 수 있을 것이다.)

---

LISTAGG

그대로 복사해서 가져다 써도된다.

select dept,
        listagg(name, ',')
        within group(order by dept) as 이름
        from tab01
        group by dept

---

PIVOT

기존 테이블 행을 열로 바꾼다.

즉, 행에 있는 데이터들을 따로 나열해서 출력해주고 싶을때 사용한다.

 

새로운 테이블을 생성해주겠다.

drop table tab10;
create table tab10(
    dept    varchar2(20),
    job     varchar2(20),
    sal     number
)

안에 값을 넣어준다.

insert into tab10 values('A사업부', '생산', 100);
insert into tab10 values('A사업부', '지원', 200);
insert into tab10 values('A사업부', '생산', 300);
insert into tab10 values('B사업부', '지원', 400);
insert into tab10 values('B사업부', '생산', 500);
insert into tab10 values('B사업부', '지원', 600);
insert into tab10 values('C사업부', '생산', 700);
insert into tab10 values('C사업부', '지원', 800);
insert into tab10 values('C사업부', '생산', 900);

 

각 사업부의 부서별 월급의 통계를 나타낸다.

select * from tab10
    pivot(
        sum(sal)             --통계 함수 
        for job              --컬럼
        in ('생산', '지원')   --컬럼의 값
        --FOR에 들어간 컬럼값을 IN에 넣어주면, 그 값에대한 통계함수 결과를 보여준다.
)