[2022.10.20. 목] 오라클 내장함수 - 다중행함수

 

1. 다중행함수 (그룹함수) 

 

• 조회된 행들의 집합그룹에 적용되어 그룹당 하나의 결과를 생성하는 함수다.
  • 집합그룹 : 테이블 전체 또는 그룹화된 테이블의 행들을 말한다.

• 사용시 주의사항
  • 그룹함수는 where절에서 사용할 수 없다. (단일함수는 where절에서 사용 가능)
  • 그룹함수와 그룹함수가 아닌 표현식을 select절에 같이 적을 수 없다. (select절에 단일함수를 같이 사용 불가) 
  • 그룹함수의 중첩은 한번만 허용된다. (단일함수는 계속 중첩 가능)
  • group by 절에 등장한 표현식은 그룹함수와 같이 select절에 적을 수 있다.

 

1-1. 그룹함수의 종류 

 

• count(*) : 조회된 모든 행의 갯수를 반환한다.
• count(컬럼 혹은 표현식) : 조회된 행에서 지정된 컬럼의 값이 null이 아닌 행의 갯수를 반환한다.
• sum(컬럼 혹은 표현식) : 조회된 행에서 지정된 컬럼의 값의 합계를 반환한다.(null값은 무시된다.)
• avg(컬럼 혹은 표현식) : 조회된 행에서 지정된 컬럼의 값의 평균을 반환한다.(null값은 무시된다.)
• min(컬럼 혹은 표현식) : 조회된 행에서 지정된 컬럼의 값의 최소값을 반환한다.(null값은 무시된다.)
• max(컬럼 혹은 표현식) : 조회된 행에서 지정된 컬럼의 값의 최대값을 반환한다.(null값은 무시된다.)
• variance(컬럼 혹은 표현식) : 조회된 행에서 지정된 컬럼의 값의 분산을 반환한다.(null값은 무시된다.)
• stddev(컬럼 혹은 표현식) : 조회된 행에서 지정된 컬럼의 값의 표준편차를 반환한다.(null값은 무시된다.)

 

-- count(*) : 조회된 행의 개수 반환

-- 직원테이블의 모든 행의 개수 조회하기
select count(*)
from employees;

-- 80번 부서에 근무하는 직원수 조회하기
select count(*)
from employees
where department_id = 80;


-- count(컬럼 혹은 표현식) : 컬럼 혹은 표현식이 null이 아닌 행의 개수를 반환

-- 커미션을 받는 직원의 수 조회하기
select count(commission_pct)
from employees;

select count(*)
from employees
where commission_pct is not null;


-- sum(컬럼 혹은 표현식) : 컬럼 혹은 표현식의 합계 반환 (null 값은 제외)
-- avg(컬럼 혹은 표현식) : 컬럼 혹은 표현식의 평균 반환 (null 값은 제외)
-- min(컬럼 혹은 표현식) : 컬럼 혹은 표현식의 최소값 반환 (null 값은 제외)
-- max(컬럼 혹은 표현식) : 컬럼 혹은 표현식의 최대값 반환 (null 값은 제외)

-- 직원전체에 대해서 최소급여, 최대급여, 급여합계, 평균급여 조회하기
select min(salary), max(salary), sum(salary), trunc(avg(salary))
from employees;

 

 

 

 

1-2. 테이블의 행을 그룹화하기 

 

• group by 절을 사용해서 지정된 컬럼의 값이 같은 값을 가지는 행끼리 그룹화할 수 있다.
• group by절은 행 그룹화 기준을 결정하는 값을 가진 컬럼을 지정한다.
  
  
• 원래 그룹함수가 아닌 표현식을 select절에 못쓰는데, group by 절에 등장한 표현식은 가능
• select 절에서 사용된 count(*) 그룹함수는 group by로 그룹화된 각 그룹마다 한번씩 실행
• department_id 컬럼의 값이 같은 행끼리 그룹이 만들어짐

 

-- 부서별 사원수 조회하기
select department_id, count(*)
from employees 
group by department_id;

 

 

 

 

-- 직종별 사원수 조회하기
select job_id, count(*) cnt
from employees
group by job_id
order by cnt desc;

 

 

-- 직종별 최소급여, 최대급여, 평균급여
select job_id, min(salary), max(salary), avg(salary)
from employees
group by job_id
order by 4;      -- 4번째 열 기준으로 정렬

 

 

 

 

1-3. 그룹함수 실행결과를 필터링하기 (그룹함수에 조건을 걸기) 

 

• having절은 group by 절을 사용해서 행을 그룹화하고, 각 그룹에 그룹함수를 실행한 결과를 필터링할 때 사용한다.
  • 일반적으로 행을 필터링할 때는 where절을 사용한다.
• where과 having
  • where
    • 행을 필터링한다.
    • 그룹함수를 조건식에 사용할 수 없다.
  • having
    • 그룹화된 그룹에 그룹함수 적용한 후에 계산된 결과로 필터링을 한다.
    • 그룹함수를 조건식에 사용할 수 있다.

 

-- 입사연도별 사원수를 조회했을 때 20명 이상 입사한 해와 그 해에 입사한 사원수 조회하기
-- group by 에 적은 표현식 그대로를 select절에 적어야 함
select to_char(hire_date, 'yyyy') year, count(*) cnt
from employees
group by to_char(hire_date, 'yyyy')
having count(*) >= 20
order by year;

 

 

-- 30, 50, 80번 부서에 소속된 직원들을 입사년도 별로 집계했을 때 10명 이상 입사한 해와 그 해에 입사한 사원수 조회하기
select to_char(hire_date, 'yyyy') year, count(*) cnt
from employees
where department_id in (30, 50, 80)
group by to_char(hire_date, 'yyyy')
having count(*) >= 10 
order by year;

 

 

 

 

1) where

그룹핑할 대상을 먼저 필터링한다. (그룹핑할 대상이 감소되고, 쿼리 성능이 향상된다)

 

2) group by 그룹핑

 

3) having 

그룹핑한 결과를 필터링한다

조건식에 그룹함수를 사용할 수 있다. 

 

 

 

 

* SELECT문의 실행결과는 언제나 테이블 ! 

즉, 테이블처럼 사용가능

 

 

 

-- 급여 등급별 사원수 조회하기
select g.salary_grade, count(*)
from employees e, salary_grades g
where e.salary >= g.min_salary and e.salary <= g.max_salary
group by g.salary_grade

 

 

이 실행결과를 하나의 테이블로 보고,

이 select문의 실행결과인 테이블과 다른 테이블을 조인하기

 

- with as 이용 

-- 급여 등급별 사원수 조회하기 (급여등급, 최소급여, 최대급여, 사원수 모두 조회하기)
-- with as 이용 
with salary_grade_cnt       --임시 테이블명(위의 실행결과를 하나의 테이블로 봄)
as (
    select g.salary_grade, count(*) cnt
    from employees e, salary_grades g
    where e.salary >= g.min_salary and e.salary <= g.max_salary
    group by g.salary_grade
)
select c.salary_grade, g.min_salary, g.max_salary, c.cnt
from salary_grade_cnt c, salary_grades g
where c.salary_grade = g.salary_grade
order by c.salary_grade asc;

-인라인뷰 이용 

-- 급여 등급별 사원수 조회하기 (급여등급, 최소급여, 최대급여, 사원수 모두 조회하기)
-- inline view (SQL문 안에서 생성한 가상의 테이블)을 이용
select c.salary_grade, g.min_salary, g.max_salary, c.cnt
from (select g.salary_grade, count(*) cnt               -- 실행결과인 테이블 자체를 여기에 넣어버림
      from employees e, salary_grades g
      where e.salary >= g.min_salary and e.salary <= g.max_salary
      group by g.salary_grade) c, salary_grades g
where c.salary_grade = g.salary_grade
order by c.salary_grade asc;

 

 

 

 

* group by 절에 2개 이상의 컬럼 혹은 표현식이 있으면,

첫번째 식을 기준으로 그룹을 먼저 나누고, 그 안에서 두번째 식을 기준으로 다시 그룹을 나눔 

 

 

-- 부서별, 직종별 사원수 조회하기
select department_id, job_id, count(*)
from employees
group by department_id, job_id
order by department_id asc, job_id asc;

 

 

 

 

* roll up : 부분합 계산

 

 

-- 부서별, 직종별 사원수 조회하기
-- roll up (부분합)
select department_id, job_id, count(*)
from employees
group by rollup(department_id, job_id)
order by department_id asc, job_id asc;

 

 

 

 

 

 

 

--------------------------------------------------------------------------------
-- 퀴즈
--------------------------------------------------------------------------------
-- 1. 직종별 사원수를 조회하기
select job_id, count(*)
from employees
group by job_id;

-- 2. 직종별 사원수를 조회했을 때, 사원수가 10명 이상인 것만 조회하기
select job_id, count(*)
from employees
group by job_id
having count(*) >= 10;

-- 3. 관리자별 사원수를 조회하기, 관리자아이디, 관리자 이름, 사원수를 조회한다.(인라인뷰를 활용하세요)
select c.manager_id, m.first_name, c.cnt
from (select manager_id, count(*) cnt
      from employees
      where manager_id is not null
      group by manager_id) c, employees m
where c.manager_id = m.employee_id;

select e.manager_id, m.first_name, count(*)
from employees e, employees m
where e.manager_id = m.employee_id
group by e.manager_id, m.first_name;

-- 4. 커미션을 받지 못하는 사원들을 급여별로 조회했을 때, 급여별 사원수를 조회하기
-- 조회 예시
-- 급여 사원수
----------------
-- 2000 10
-- 3000 7
-- 4000 12
-- 5000 21
select trunc(salary, -3) sal, count(*)
from employees
where commission_pct is null
group by trunc(salary, -3)
order by sal asc;

-- 5. 부서별 평균급여를 조회했을 때, 해당 부서의 평균급여보다 적은 급여를 받는 사원의 아이디, 이름을 조회하기(인라인뷰를 활용하세요)
select e.employee_id, e.first_name, e.department_id, e.salary
from (select department_id, avg(salary) avg_salary
      from employees
      group by department_id) d, employees e
where d.department_id = e.department_id
and d.avg_salary > e.salary;

-- 6. 자신의 관리자와 같은 해에 입사한 직원의 아이디, 이름, 입사일을 조회하기
select e.employee_id, e.first_name, e.hire_date, e.manager_id, m.employee_id, m.first_name, m.hire_date
from employees e, employees m
where e.manager_id = m.employee_id
and to_char(e.hire_date, 'yyyy') = to_char(m.hire_date, 'yyyy')
order by e.employee_id;

-- 7. 부서별 평균급여를 조회했을 때, 부서별 평균급여에 대한 급여 등급을 조회하기
select s.dep_id, s.avg_salary, g.salary_grade
from (select department_id dep_id, trunc(avg(salary)) avg_salary
      from employees
      group by department_id) s, salary_grades g
where s.avg_salary >= g.min_salary and s.avg_salary <= g.max_salary
order by 1;


-- 8. 자신의 관리자와 급여등급이 같은 직원의 아이디, 이름을 조회하기
select e.employee_id 직원아이디,
       e.first_name 직원이름, 
       e.salary 직원급여,
       eg.salary_grade 직원급여등급,
       m.employee_id 관리자아이디,
       m.first_name 관리자이름,
       m.salary 관리자급여, 
       mg.salary_grade 관리자급여등급
from employees e, employees m, salary_grades eg, salary_grades mg
where e.manager_id = m.employee_id 
and e.salary >= eg.min_salary and e.salary <= eg.max_salary
and m.salary >= mg.min_salary and m.salary <= mg.max_salary 
and eg.salary_grade = mg.salary_grade;