2024.12.09 9일차 수업

복습 )

   1. GROUP BY 절에는 SELECT 절에 있는 컬럼 그대로 써야 오류가 안 나온다.

 

 

ex) 요일별로 사원 인원수를 알고 싶을 경우

select to_char(hire_date, 'day'), count(*)
from hr.employees
group by to_char(hire_date, 'day') 
order by 1;

출력

▶order by 1을 하게 되면 첫 번째 컬럼을 오름차순 하게 되는데 출력 결과 보다시피 한글이라 ㄱㄴㄷ 순으로 출력된 걸 볼 수 있다.

 

ex) 위에 거를 가로로 만들고 싶을 때 (pivot 함수 사용)

select *
from(select to_char(hire_date, 'dy') day
from hr.employees)
pivot(count(*) for day in ('월' 월,'화' 화,'수' 수,'목' 목,'금' 금,'토' 토,'일' 일));

▶ 카운트 함수 안에 employee_id를 적으면 오류 뜬다. 그 이유는 pivot 연산은 기본적으로 각 day 값에 대해 행(row)을 열(column)로 변환하는 방식이다. count(employee_id)는 employee_id 컬럼의 값이 null이 아닌 경우의 개수를 세려는 집계 함수인데, pivot에서는 이를 직접적으로 처리할 수 없고 행을 열로 변환할 때 명시적인 그룹화가 필요하고 employee_id까지 출력하고 싶을 땐 인라인뷰 안에 employee_id 컬럼을 만들면 해결이 된다.

출력

 

ex) 위에 거를 세로로 바꾸고 싶을 때 (unpivot 함수 사용)

    - PIVOT한 결과 집합을 다시 인라인 뷰로 묶고 하면 된다.

select *
from(select *
from(select to_char(hire_date, 'dy') day
from hr.employees)
pivot(count(*) for day in ('월' 월,'화' 화,'수' 수,'목' 목,'금' 금,'토' 토,'일' 일)))
unpivot(인원수 for 요일 in ("월","화","수","목","금","토","일"));

출력

 

ex) 응용 버전 (회사에서 쓸 경우도 많음)

select *
from(select to_char(hire_date, 'yyyy') year, 
            sum(salary) 총급여, 
            count(*) 인원수
            from hr.employees
group by to_char(hire_date, 'yyyy'))
unpivot(값 for 속성 in (총급여, 인원수))
order by 1,2;

▶ unpivot은 데이터를 행(row) 형태로 변환하는 작업이다.

▶ 위에 코드는 총 급여와 인원수 열을 속성 값으로 변환하여 새로운 행 형태로 만든다.

▶ unpivot 연산으로 인해 원래 열(총급여, 인원수)이 속성이라는 새로운 열로 변환해서 각각의 값이 값이라는 열에 매핑 

출력

---

문제 1) 요일별 인원수를 출력해 주세요

     - 단 pivot, unpivot 함수를 사용하지 않는다.

select
    decode(day, 1, '월',
                2, '화',
                3, '수',
                4, '목',
                5, '금',
                6, '토',
                7, '일') 요일, cnt 인원수
from(select to_char(hire_date -1, 'd') day, count(*) cnt
from hr.employees
group by to_char(hire_date -1, 'd')
order by 1);

▶ to_char를 통해 요일을 숫자별로 뽑아낸다.

▶ 일요일이 1이기 때문에 hire_date -1을 해줘서 월 ~ 일까지 정렬해 준다.

▶ 하지만 우리가 원하는 건 문자로 나타내야 하기 때문에 인라인 뷰처럼 묶어준다.

▶ decode함수를 통해 1이 들어오면 월, 2가 들어오면 화  이런 식으로 적고 컬럼 이름을 요일별로 해주면 끝!

출력

 

문제 2) 분기별 총액 급여를 출력해 주세요. (pivot 함수 사용)

 

     1. 첫 번째 방법

select *
from(select to_char(hire_date, 'q') quarter, salary
from hr.employees)
pivot(sum(salary) for quarter in ('1' "1분기", '2' "2분기", '3' "3분기", '4' "4분기"));

 

     2. 두 번째 방법

select *
from(select to_char(hire_date, 'q') quarter, sum(salay) sum_sal
from hr.employees
group by to_char(hire_date, 'q'))
pivot(max(sum_sal) for quarter in ('1' "1분기", '2' "2분기", '3' "3분기", '4' "4분기"));

출력

 

문제 3) 년도, 분기별 총액급여를 출력해주세요.

 

     1. DECODE 이용

select
            to_char(hire_date, 'yyyy') 년도,
            sum(decode(to_char(hire_date,'q'), '1', salary)) "1분기",
            sum(decode(to_char(hire_date,'q'), '2', salary)) "2분기",
            sum(decode(to_char(hire_date,'q'), '3', salary)) "3분기",
            sum(decode(to_char(hire_date,'q'), '4', salary)) "4분기"
from hr.employees
group by to_char(hire_date, 'yyyy')
order by 1;

 

     2. PIVOT 이용

select *
from(select 
            to_char(hire_date, 'yyyy') 년도,
            to_char(hire_date,'q') quarter,
            sum(salary) sum_sal
from hr.employees
group by to_char(hire_date,'yyyy'), to_char(hire_date, 'q'))
pivot(max(sum_sal) for quarter in('1' "1분기", '2' "2분기", '3' "3분기", '4' "4분기"))
order by 1;

출력

         # 세로로 만들고 싶을 경우 (unpivot 사용)

           - unpivot 사용할 땐 큰 따옴표로 꼭 묶는 거 잊지 말기!

select*
from(select *
from(select 
            to_char(hire_date, 'yyyy') 년도,
            to_char(hire_date,'q') quarter,
            sum(salary) sum_sal
from hr.employees
group by to_char(hire_date, 'yyyy'), to_char(hire_date,'q'))
pivot(max(sum_sal) for quarter in('1' "1분기", '2' "2분기", '3' "3분기", '4' "4분기"))
order by 1)
unpivot(급여총액 for 분기 in("1분기","2분기","3분기","4분기"));

▶ 인라인 뷰를 한 번도 묶어서 진행하면 된다.

출력

       # 만약 데이터가 없는 분기까지 다 출력하고 싶을 경우 (include nulls 사용)

         - unpivot include nulls : 데이터 값이 없는 값을 null로 출력해 준다.

select*
from(select *
from(select 
            to_char(hire_date, 'yyyy') 년도,
            to_char(hire_date,'q') quarter,
            sum(salary) sum_sal
from hr.employees
group by to_char(hire_date, 'yyyy'), to_char(hire_date,'q'))
pivot(max(sum_sal) for quarter in('1' "1분기", '2' "2분기", '3' "3분기", '4' "4분기"))
order by 1)
unpivot include nulls(급여총액 for 분기 in("1분기","2분기","3분기","4분기"));

출력

---

■ 다중열 서브쿼리

 

    1. 비쌍비교 방식 (교차로 하는 방식)

select *
from hr.employees
where manager_id in (select manager_id
			from hr.employees
			where first_name = 'John')
and department_id in (select department_id
			from hr.employees
			where first_name = 'John');

출력

 

     2. 쌍비교 방식 (세트로 묶어서 비교)

select *
from hr.employees
where (manager_id, department_id) in (select manager_id, department_id
							from hr.employees
							where first_name = 'John');

출력

 

▶ 둘 다 얼핏 보면 내용은 같아 보이지만 결과는 정말 다르게 나온다.

▶ 그 이유는 1,2번이 서로 비교방식이 다르기 때문이다. 

▶ 첫 번째는 manager_id값 하나를 department_id에 다 비교하고 없으면 다음 manager_id값으로 다시 department_id에 다 비교하는 방식이다.

▶ 두 번째는 John이라는 first_name의 manager_id, department_id를 같이 묶어서 한꺼번에 비교한다. (주의!!)

 

■ 집합연산자

  - select 절의 컬럼의 개수가 일치해야 한다.

  - 첫 번째 select 절의 컬럼의 대응되는 두 번째 select절 컬럼의 데이터 타입이 일치해야 한다.

  - 첫 번째 select절 컬럼 이름이 결과 집합의 컬럼으로 출력된다.

  - 별칭을 쓸 때도 첫 번째 select절에 쓰면 된다.

  - union, interset, minus 연산자는 중복을 제거하고, 정렬이 발생한다.

  - order by 절은 마지막에 기술해야 한다.

  - order by 절에는 첫 번째 select문의 컬럼이름, 별칭, 위치표기법을 사용한다.

 

   1. 합집합

      - union : 중복 제거 (중복 여부를 체크해야 하므로 내부적으로 SORT 발생) -> 이거 때문에 성능이 떨어질 수 있기 때문에 주의해서 써야 한다.

      - union all : 중복 포함

ex 1)

select employee_id, job_id, salary
  from hr.employees;

  select employee_id, job_id
  from hr.job_history;

 

ex 2) 위에 거를 하나로 합칠 때

select employee_id, job_id, salary
from hr.employees
union 
select employee_id, job_id, 'zero'
from hr.job_history;

출력

▶ 오류 뜨는 이유는 위에 select절에 salary는 숫자형인데 밑에 select절에 'zero' 문자형으로 되어있기 때문이다.

 

  # 위에 오류를 해결하고 싶을 경우 ( 형변환 시켜주면 된다.)

select employee_id, job_id, to_char(salary)
from hr.employees
union 
select employee_id, job_id, 'zero'
from hr.job_history;

출력

     ex 3) 

select employee_id 사번, job_id 직무, salary 급여
from hr.employees
union 
select employee_id, job_id, 0
from hr.job_history;

▶ 0 대신 null로 해도 상관없다.

출력

ex 4) union all 사용

select employee_id 사번, job_id 직무, salary 급여
from hr.employees
union all
select employee_id, job_id, 0
from hr.job_history;

출력

▶ union all은 중복을 포함하기 때문에 결과가 더 많이 출력된다.

 

       2. 교집합(intersect) - 정렬발생

 

  ex 1) 직무를 바꾸고 싶어서 바꿨는데 다시 옛날 직무가 그리워서 다시 돌아간 사람들을 출력하고 싶은 경우

select employee_id, job_id
from hr.employees
intersect
select employee_id, job_id
from hr.job_history;

출력

▶ 하지만 위에 코드는 성능적으로 안 좋다.

▶ 그럴 땐 exists 사용한다.

 

select employee_id, job_id
from hr.employees e
where exists (select 'x'
                from hr.job_history 
                where employee_id = e.employee_id
                and job_id = e.job_id);

출력

ex 2) 컬럼 하나만 쓸 경우

select employee_id
from hr.employees
intersect
select employee_id
from hr.job_history;

출력

ex 3) job_id를 한 번이라도 바꾼 사원을 찾고 싶은 경우

select * 
from hr.employees e
where exists(select 'x'
            from hr.job_history
            where employee_id = e.employee_id);

출력

 

     3. 차집합 (minus - 정렬발생)

 

ex) 개발자들이 쓰는 코드

select employee_id
from hr.employees 
minus
select employee_id
from hr.job_history;

출력

   ▶ 위에 코드는 sort가 내부적으로 발생하면서 악성 프로그램 원인이 될 수 있으므로 최대한 exists나 not exists 쓰는 습관을 가져야 한다.

select employee_id
from hr.employees e
where not exists(select 'x'
                    from hr.job_history
                    where employee_id = e.employee_id);

▶ 이렇게 쓰는 게 좋다.

 

문제)  부서가 소재하지 않은 나라 리스트가 필요하다.

 

      1. 집합연산자

select country_id, country_name
from hr.countries
minus
select c.coutry_id, c.country_name
from hr.departments d, hr.countries c, hr.locations l
where d.location_id = l.location_id
and l.country_id = c.country_id;

 

     2. not exists

select country_id, country_name
from hr.countries c
where not exists(select 'x'
from hr.departments d, hr.locations l
where d.location_id = l.location_id
and l.country_id = c.country_id);

출력