231206 DB - 그룹 함수와 그룹화 2, 서브쿼리 1

그룹 함수와 그룹화

그룹화(group by절)

group by절과 그룹 함수 사용 시 지켜야하는 규칙

→ select절의 컬럼 리스트 중 그룹함수에 포함된 컬럼과 그룹함수에 포함되지 않은 컬럼이 함께 출력되려면

    그룹함수에 포함되지 않은 컬럼은 반드시 group by절에 포함되어 있어야 문법 오류가 나지 않음

ex) SELECT department_id, AVG(salary)
      FROM employees
      GROUP BY department_id
      ORDER BY department_id;

 

ex1)

select department_id, avg(salary)
from employees;

그림 1. ex1 결과

결과가 나오지만 오류임. 

 

ex2)select department_id, job_id, avg(salary)
from employees
group by department_id;

그림 2. ex2 결과

ex2 수정)select department_id, job_id, avg(salary)
from employees
group by department_id, job_id;

그림 2. ex2 수정 결과

 

예제)select department_id, job_id, SUM(salary)
from employees
where department_id > 40
group by department_id, job_id
order by department_id;

그림 3. 예제 결과

 

오류 수정하기 1) 부서별 사원의 수 출력

select department_id, count(last_name)
from employees;

↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓

select department_id, count(last_name)
from employees
group by department_id;

 

오류 수정하기 2) 부서 내 업무별 사원의 수 출력

select department_id, job_id, count(last_name)
from employees
group by department_id;

↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓

select department_id, job_id, count(last_name)
from employees
group by department_id, job_id
order by department_id;

 

그룹화 조건문(having절)

SQL> select 컬럼명, 그룹함수(컬럼)
           from 테이블명
           [where 조건문]
           [group by 컬럼명]
           [having 조건문]
           [order by 컬럼명]

where절과 having절은 둘 다 조건을 작성하는 조건절

where절은 행 제한 조건절이며, having절은 행그룹 제한 조건절(그룹함수가 포함된 조건문)

 

having절 예시)

select job_id, sum(salary) payroll
from employees
where job_id not like '%REP%'
group by job_id
having sum(salary) > 13000
order by sum(salary);

그림 4. having절 예시

---

연습문제 1

employees 테이블로부터 전체 사원들의 커미션 평균을 출력하는 구문을 작성 (단, 소수점 둘째자리까지 반올림해서 출력)

select round(avg(ifnull(commission_pct,0)),2)  as avg_comm
from employees;

 

연습문제2
employees 테이블로부터 업무(job_id)별 최대 급여(maximum), 최소급여(minimum), 급여의 합계(sum),

평균 급여(average)를 출력

select job_id, 
max(salary) as Maximum, 
min(salary) as Minimum, 
sum(salary) as Sum, 
round(avg(salary),0) as Average
from employees
group by job_id;

연습문제3
employees 테이블로부터 동일 업무(job_id)를 수행하는 직원 수를 출력하는 구문을 작성
select job_id, count(*)
from employees
group by job_id;

연습문제4
employees 테이블로부터 매니저를 알 수 없는 사원은 제외하고 매니저별로 그룹화하여 매니저별 최소 급여를 출력하되 최소 급여가 $6000 이상인 그룹만 출력 또한 최소 급여를 기준으로 내림차순으로 정렬

select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >=6000
order by min(salary) desc;

연습문제5
employees 테이블에서 최고 급여와 최저 급여의 차이를 출력하는 구문을 작성
select (max(salary)-min(salary)) as DIFFERENCE
from employees;

연습문제6
employees 테이블로부터 사원의 총 수와 1995년, 1996년, 1997년, 1998년에 채용된 사원의 수를 표시하는 구문을 작성

select count(*) TOTAL,

count(if(year(hire_date)=1995,1,null)) "1995",

count(if(year(hire_date)=1996,1,null)) "1996",

count(if(year(hire_date)=1997,1,null)) "1997"

count(if(year(hire_date)=1998,1,null)) "1998"

from employees;

또는

select count(*) TOTAL, sum(if(year(hire_date)=1995, 1, 0)) "1995",
sum(if(year(hire_date)=1996, 1, 0)) "1996",
sum(if(year(hire_date)=1997, 1, 0)) "1997",
sum(if(year(hire_date)=1998, 1, 0)) "1998"
from employees;

---

Subquery

employees 테이블에서 last_name이 Abel인 직원보다 더 많은 급여를 받는 사원을 출력
Abel의 급여 : select salary from employees where last_name = 'Abel';
이후
select employee_id, last_name, salary, department_id
from employees
where salary > 11000;
2번의 구문을 작성하는 불편함이 있음. 

이를 한 번에 작성해보면
select employee_id, last_name, salary, department_id
from employees
where salary > (select salary from employees where last_name='Abel');

쿼리 구문 안에 쿼리가 있는 것을 서브쿼리, 이너 쿼리라고 하며,

큰 쿼리를 메인 쿼리 또는 아우터 쿼리라고 함.

 

서브쿼리(subquery)는 쿼리 구문 안에 또 다시 쿼리 구문이 들어가 있는 형태.

서브쿼리가 먼저 실행되고 그 결과값을 활용해서 메인쿼리가 실행됨.

SQL> select 컬럼명1, 컬럼명2, ...
           from 테이블명
           where 컬럼명(좌변) = (select 컬럼명
                                                from 테이블명
                                                where 조건문)
           group by 컬럼명
           having 조건문
           order by 컬럼명;

서브쿼리는 괄호로 묶어서 작성함.

서브쿼리 사용 가능한 곳 : select절, from절, where절, having절, order by절, DML, DDL 등 (단, group by절 제외)

서브쿼리 유형 : 단일행 서브쿼리, 다중행 서브쿼리

 

단일행 서브쿼리(Single-row subquery)

서브쿼리로부터 단일행(단일값)이 반환되는 유형

메인쿼리에 단일행 비교연산자가 필요함

단일행 비교연산자 : =, >, <, ..., <>, !=

 

단일행 서브쿼리 ex1) employees 테이블에서 141번과 동일한 업무를 담당하는 사원들을 출력

select last_name, job_id
from employees
where job_id = (select job_id
                          from employees
                          where employee_id = 141);

그림 5. 단일행 서브쿼리 ex1 결과

 

단일행 서브 쿼리 ex2) employees 테이블에서 최소급여를 받는 사원의 last_name, job_id, salary를 출력
select last_name, job_id, salary
from employees
where salary = (select min(salary)
                          from employees);

그림 6. 단일행 서브쿼리 ex2 결과

 

단일행 서브 쿼리 ex3) employees 테이블로부터 lee와 동일한 업무를 담당하면서 급여는 더 많이 받는 직원들을 출력

select last_name, job_id, salary
from employees
where job_id = (select job_id
                       from employees
                        where last_name = 'lee')
and salary > (select salary
                    from employees
                    where last_name = 'lee');

그림 7. 단일행 서브쿼리 ex3 결과

 

단일행 서브 쿼리 ex4) employees 테이블로부터 부서아이디가 30인 최소 급여보다 큰 부서만 출력
select department_id, MIN(salary)
from employees
where department_id is not null
group by department_id
having MIN(salary) > (select MIN(salary)
                                  from employees
                                  where department_id = 30);

그림 8. 단일행 서쿼리 ex4 결과

 

단일행 서브쿼리 ex5)

select employee_id, last_name
from employees
where salary = (select min(salary)
                         from employees
                        group by department_id);

그림 9. 단일행 서브쿼리 ex5 결과

단일행 서브쿼리에 여러 값이 반환이 되었기 때문에 오류가 발생
단일행 서브쿼리 ex5 수정)
select employee_id, last_name
from employees
where salary in (select min(salary)
                          from employees
                          group by department_id);

그림 10 .단일행 서브쿼리 ex5 수정 결과

 

단일행 서브쿼리 ex6)

select last_name, job_id
from employees
where job_id = (select job_id
                          from employees
                          where last_name = 'Haas');

결과가 나오지 않음.

이유는 Haas라는 직원이 없어 Null 값이 넘어감.

즉 단일행 서브쿼리로부터 Null값이 반환되는 경우 메인 쿼리 결과도 Null

 

다중행 서브쿼리(Multiple-row subquery)

서브쿼리로부터 메인쿼리로 다중행(다중값)이 반환되는 유형

메인쿼리에는 우변에 값리스트가 올 수 있는 다중행 비교연산자가 필요

다중행 비교연산자 종류 : in(=, OR), not in(<>, AND), *any(OR), *all(AND)

*any와 all은 논리연산자의 성격만 가지고 있어서 비교연산자와 함께 사용

=any (=, or) (==) in
>any (>,or)
>=any (>=, or)
<any (<,or)
<=any (<=, or)
<>any (<>, or)

=all (=, and)
>all (>, and)
>=all (>=, and)
<all (<, and)
<=all (<=,and)
<>all (<>, and) (==) not in

 

다중행 서브쿼리 ex1)

select employee_id, last_name, manager_id, department_id
from employees
where manager_id in (select manager_id
                                  from employees
                                  where employee_id in (174, 141))
and department_id in (select department_id
                                   from employees
                                   where employee_id in (174,141))
and employee_id not in (174, 141);

그림 11. 다중행 서브쿼리 ex1 결과