학원 day32. 그룹함수(다중행 함수)

다중행 함수(그룹함수)

조회된 행들의 집합그룹에 적용되어 그룹당 하나의 결과를 생성하는 함수다.

   - 집합그룹이란, 테이블 전체 또는 그룹화된 테이블의 행들을 말한다.

   - 집합그룹의 예

        - 사원테이블전체 혹은 부서별 평균임금

        - 직종별 전체 급여

        - 관리자별 관리직원수

        - 입사년도별 입사한 사원수

        - 급여등급별 사원수

사용시 주의사항

   - 그룹함수는 where절에서 사용할 수 없다.

   - 그룹함수와 그룹함수가 아닌 표현식을 select절에 같이 적을 수 없다.

   - 그룹함수의 중첩은 한번만 허용된다.

   - group by 절에 등장한 표현식은 그룹함수와 같이 select절에 적을 수 있다.

 

다중행 함수(그룹함수)의 종류

count(*)

조회된 모든 행의 갯수를 반환한다. (null도 포함)

count(컬럼 혹은 표현식)

조회된 행에서 지정된 컬럼의 값이 null이 아닌 행의 갯수를 반환한다.

sum(컬럼 혹은 표현식)

조회된 행에서 지정된 컬럼의 값의 합계를 반환한다.(null값은 무시된다.)

avg(컬럼 혹은 표현식)

조회된 행에서 지정된 컬럼의 값의 평균을 반환한다.(null값은 무시된다.)

min(컬럼 혹은 표현식)

조회된 행에서 지정된 컬럼의 값의 최소값을 반환한다.(null값은 무시된다.)

max(컬럼 혹은 표현식)

조회된 행에서 지정된 컬럼의 값의 최대값을 반환한다.(null값은 무시된다.)

variance(컬럼 혹은 표현식)

조회된 행에서 지정된 컬럼의 값의 분산을 반환한다.(null값은 무시된다.)

stddev(컬럼 혹은 표현식)

조회된 행에서 지정된 컬럼의 값의 표준편차를 반환한다.(null값은 무시된다.)

 

테이블의 행을 그룹화하기

group by 절을 사용해서 지정된 컬럼의 값이 같은 값을 가지는 행끼리 그룹화할 수 있다.

group by절의 작성 규칙

  select 컬럼혹은표현식, 그룹함수(), 그룹함수()
  from 테이블명
  [where 조건식]
  [group by 컬럼혹은표현식]
  [order by 컬럼명]

group by절은 행 그룹화 기준을 결정하는 값을 가진 컬럼을 지정한다.

group by절에 지정한 컬럼은 select절에서 그룹함수와 같이 사용할 수 있다.

select 절에서 사용된 그룹함수는 group by로 그룹화된 각각의 그룹마다 실행된다.

그룹함수 실행결과를 필터링하기

- having절은 group by 절을 사용해서 행을 그룹화하고, 각 그룹에 그룹함수를 실행한 결과를 필터링할 때 사용한다.

    - 일반적으로 행을 필터링할 때는 where절을 사용한다.

- where과 having

    - where

         -행을 필터링한다.

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

    - having

         -그룹화된 그룹에 그룹함수 적용한 후에 계산된 결과로 필터링을 한다. (group by 절 이후에 올 수 있다.)

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

- having절의 작성규칙

  select column, 그룹함수
  from table
  [where  조건식]
  [group by 컬럼혹은표현식]
  [having 그룹함수적용결과를 필터링하는 조건식]
  [order by 컬럼]

  -- 소속부서별로 사원수를 집계했을 때, 사원수가 5명 미만인 부서의 아이디와 사원수를 조회하기
  select department_id, count(*)
  from employees 
  group by department_id
  having count(*) < 5

--------------------------------------------------------------------------------
-- HAVING
--------------------------------------------------------------------------------
-- 입사년도별 사원수 조회했을 때 20명 이상 입사한 해와 그 행에 입사한 사원수를 조회하기 
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;

 

인라인-뷰(Inline-View)

뷰란, 가상의 테이블로써 인라인-뷰는 SQL문 안에서 생성한 가상의 테이블을 뜻한다.

--------------------------------------------------------------------------------
-- INLINE VIEW
--------------------------------------------------------------------------------
-- 급여 등급별 사원수를 조회하기 (급여 등급, 최소급여, 최대급여, 사원수를 조회하기)
방법1.
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;

방법2.
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;

ROLL UP

부분합을 계산한다.

--------------------------------------------------------------------------------
-- 두 번 그룹화하기, ROLLUP (부분합 & 전체합도 함께 나옴)
--------------------------------------------------------------------------------
-- 두 번 그룹화하기 
-- 부서별, 직종별 사원수를 조회하기 (1)
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID ASC, JOB_ID ASC;

-- ROLLUP
-- 부서별, 직종별 사원수를 조회하기 (2)
SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY ROLLUP (DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID ASC, JOB_ID ASC;

(1) 결과 

(2) 결과