2024.12.03 5일차 수업

■ group by

   - 테이블의 행을 작은 그룹으로 나눌 수 있는 절

 

ex) 부서별 총액 급여를 구해주세요.

select department, sum(salary)
from hr.employees
group by department_id;

출력

▶ department_id(부서별)로 group by를 통해 부서별로 그룹을 지어준다.

▶ F10을 누르면 실행계획에 HASH 알고리즘을 통해 group by가 실행됐다고 알 수 있다.

▶ 9i R1 버전에서는 group by가 sort group by로 수행하기 때문에 정렬된 결과 집합으로 출력된다.

▶ 9i R2 버전부터는 group by가 hash group by로 수행하기 때문에 정렬된 결과 집합으로 출력되지 않는다. (KEY-VALUE)

 

ex) 위에 거를 정렬해서 만들고 싶을 경우

select department_id, sum(salary)
from hr.employees
group by department_id
order by 1;

▶ order by를 통해 정렬해주면 된다.

출력

 

■ 그룹함수를 사용할 때 주의할 점

    - null을 포함하지 않는다. 단 count(*) 일 경우에는 null 포함

    - select절에 그룹함수에 포함되지 않은 개별 컬럼은 하나도 빠짐없이 group by절에 명시해야 한다.

    - group by절에는 열별칭, 위치표기법 사용할 수 없다. (오류발생함)

    - where절에서는 그룹함수의 결과를 제한할 수 없다.

    - 그룹함수를 중첩하게 되면 개별 컬럼을 사용할 수 없다.

 

ex 1) 오류발생 예

select
     department_id, sum(salary)
from hr.employees;

출력

ex 2) 오류발생 예

select
     department_id, job_id, sum(salary)
from hr.employees
group by department_id;

▶ job_id를 group by절에 명시하지 않았기 때문에 오류가 발생한다.

출력

 

ex 3) ex 2를 출력하는 법

select
     department_id, job_id, sum(salary)
from hr.employees
group by department_id, job_id;

출력

 

ex 4) 

select to_char(hire_date, 'yyyy'), sum(salary)
from hr.employees
group by to_char(hire_date, 'yyyy');

출력

ex 5) 그룹함수에서는 열별칭이 되지 않는다.

select to_char(hire_date, 'yyyy') years, sum(salary)
from hr.employees
group by years;

출력

 

ex 6) where절에서는 그룹함수의 결과를 제한할 수 없기 때문에 오류발생

select department_id, sum(salary)
from hr.employees
where sum(salary) >= 10000
group by department_id;

출력

 

---

■ having 절

   - 그룹함수의 결과를 제한하는 절이지 행을 제한하는 절은 아니다.

select department_id, sum(salary)
from hr.employees
group by department_id
having sum(salary) >= 10000;

select department_id, sum(salary)
from hr.employees;
having sum(salary) >= 10000
group by department_id;

출력

 

select department_id, sum(salary), count(*)
from hr.employees
group by department_id
having count(*) >= 5;

▶ 부서별로 5명 이상인 사람들의 부서를 먼저 뽑는다.

▶ 뽑은 부서의 사원들의 salary 값을 더해서 출력해준다.

출력

ex)

select department_id, sum(salary)
from hr.employees
where last_name like '%i%'
group by department_id
having sum(salary) >= 10000
order by 1;

▶실행순서 : from절 실행 -> where절 실행 -> group by절 실행 -> select문에 집계값(department_id, sum(salary) 실행 -> having절 실행 -> order by절 실행

 

ex) select절에 없는 컬럼을 적어도 출력이 가능하다.

select avg(salary)
from hr.employees
group by department_id;

출력

 

ex) 오류 발생

select department_id, max(avg(salary)) -- 오류발생
from hr.employees
group by department_id;

▶ 해결방법은 서브쿼리를 사용하면 된다. (서브쿼리는 다음 시간에 배움)

---

문제 1) department_id가 50인 사람들의 건 수를 출력하시오.

select department_id, count(*) 건수
from hr.employees
where department_id = 50
group by department_id;

출력

 

문제 2) 2008년도에 입사한 사원들의 job_id별 인원수를 구하고 인원수가 많은 순으로 출력하시오.

     - 날짜 형태는 꼭 날짜 형식(to_date)으로 써주기

 

내 답 ) 출력은 같지만 좋은 방법은 아님

select job_id, count(*)
from hr.employees
where hire_date between '2008/01/01' and '2008/12/31'
group by job_id
order by 2 desc;

 

교수님 답)

select job_id, count(*)
from hr.employees
where hire_date >= to_date('2008-01-01', 'yyyy-mm-dd')
and hire_date < to_date('2009-01-01', 'yyyy-mm-dd')
group by job_id
order by 2 desc;

출력

 

문제 3) 월 별 입사한 인원수 출력을 월 별로 오름차순으로 정렬하기

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

출력

 

문제 4) 연도별 입사 인원수를 아래 화면과 같이 출력하시오

TOTAL	2001년	2002년	2003년
107	1	7	6

1. decode로 푼 경우

select count(*) total,
count(decode(to_char(hire_date, 'yyyy'), '2001', 1)) "2001년",
count(decode(to_char(hire_date, 'yyyy'), '2002', 1)) "2002년",
count(decode(to_char(hire_date, 'yyyy'), '2003', 1)) "2003년"
from hr.employees;

2. case로 푼 경우

select count(*) total,
count(case when to_char(hire_date, 'yyyy') = '2001' then 1 end) "2001년",
count(case when to_char(hire_date, 'yyyy') = '2002' then 1 end) "2002년",
count(case when to_char(hire_date, 'yyyy') = '2003' then 1 end) "2003년"
from hr.employees;

▶ 먼저 연도를 추출해야 되기때문에 to_char를 통해서 년도를 추출한다.

▶ 만약 입사 연도가 2001년도면 1을 결과값으로 주고 count를 통해 2001년도 입사한 사람들이 들어올 때마다 더해준다.

▶ 만약 2002년도가 들어오면 똑같이 1을 결과값을 주고 count를 해준다.

▶ 컬럼에 별칭을 주면 위에 표처럼 결과가 나올 수 있다.

출력

---

■ JOIN 

   - 두 개 이상의 테이블에서 내가 원하는 데이터를 추출하는 방법

 

 1. cartesian product

• 조인 조건이 생략된 경우
• 조인 조건이 잘못 생성된 경우
• 첫 번째 테이블 행의 수와 두 번째 테이블의 행수가 곱해진다.
• where department_id = department_id; : 조인조건 술어

 

  2. equi join, inner join, simple join, 등가조인

     - 조인키값이 일치하는 데이터만 추출하는 조인

 

ex) 

select employee_id, department_name
from hr.employees, hr.departments
where department_id = department_id;

출력

▶ 출력 결과를 보면 양쪽 테이블의 동일한 이름의 컬럼을 사용해서 조인조건 술어를 만들게 되면 컬럼의 정의가 모호하다는 오류가 발생할 수 있다.

 

해결방법)

select e.employee_id, d.department_name
from hr.employees e, hr.departments d
where e.department_id = d.department_id;

▶ 테이블의 별칭을 달아서 오류를 해결해 준다.

출력

select e.employee_id, d.department_name
from hr.employees , hr.departments 
where employees.department_id = departments.department_id;

▶ 이렇게 해도 출력은 되지만 코드도 길어지고 메모리 사용량도 늘어나기 때문에 쓰지 않는 게 좋다.

 

  1) equi join : 키 값이 일치하는 것만 추출한다.

select e.employee_id, d.department_name
from hr.employees e, hr.departments d
where e.department_id = d.department_id;  -- 조인조건 술어

              ----------------------      ---------------------

     m쪽 집합(107)-중복을 가짐   1쪽 집합(27)- 유일한 값을 가짐

   = m쪽 집합 건수(107) - 아무리 많이 나와도 최대 107건이 나와야 함, 더 적게 나올 수 있음

 

# 100번 사원의 부서 정보까지 출력하고 싶은 경우

select e.*, d.*
from hr.employees e, hr.departments d -- 조인조건 술어
where e.department_id = d.department_id -- 비조인조건 술어(전체조건이 아닌 특정 조건의 데이터가 필요할 경우)
and e.employee_id = 100;

▶ employees 테이블에 있는 department_id 100번이 누군지 알아낸다.

▶그 100번 사원의 employees 테이블 안의 department_id 번호와 departments 테이블의 번호가 일치한 것을 다 출력하라는 코드이다.

출력 1

출력 2

 

※ n개의 테이블을 조인하려면 조인조건 술어는 n -1개의 조인조건 술어를 작성해야 한다.

 

  문제 1) 현재 사원들의 근무 도시를 출력해라

select e.employee_id, l.city
from hr.employees e, hr.locations l, hr.departments d
where e.department_id = d.department_id

출력

 

문제 2) 사원들의 employee_id, country_name 출력하시오.

select e.employee_id, c.country_name 
from hr.employees e, hr.departments d, hr.locations l, hr.countries c
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.country_id = c.country_id;

▶ 사원들의 employee_id와 country_name을 출력하려면 서로 연결고리부터 파악을 하는 게 중요하다.

▶ 하나씩 차근차근 비교하기 위해선 2개를 먼저 비교하고 그 결과값이랑 나머지랑 비교하는 걸 잊으면 안 된다.

▶ 먼저 countries 테이블까지 연결하기 위해선 employees테이블의 department_id와 departments 테이블의 department_id를 연결해 준다.

▶ 그다음엔 departments 테이블하고 locations 테이블의 연결고리를 찾으면 departments 테이블의 location_id와 locations 테이블의 location_id를 연결시켜 준다.

▶마지막으로 locations테이블과 countries 테이블의 연결고리인 country_id를 연결시켜주고 select 문에 출력할 컬럼을 적으면 된다.