DQL) HAVING 구문(rollup, cube, grouping 함수)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

HAVING

- 조건절, 그룹핑된 결과 행에 대해서 결과 집합 포함 여부를 처리하는 구문

 

인턴 사원을 제외하고, 부서 별 평균 급여가 300만원 이상인 부서만 조회 (부서코드, 평균급여)

select
    dept_code 부서,
    trunc(avg(salary)) 평균급여
from
    employee
where
    dept_code is not null
group by
    dept_code
having
    trunc(avg(salary)) >= 3000000;

 

부서 별 인원 수가 3명 이상인 부서만 조회 (인원수 내림차순)

select
    dept_code 부서,
    count(*) 인원수
from
    employee
group by
    dept_code
having
    count(*) >= 3
order by
    인원수 desc;

 

관리하는 직원이 2명 이상인 관리자 사원의 사번과 관리 사원 수를 조회

select
    manager_id 관리자사번,
    count(*) 관리사원수
from
    employee
--where
--    manger_id is not null
group by
    manager_id
having
--    count(*) >= 2 and manager_id is not null;
    count(manager_id) >= 2;

 

---

☞ rollup(), cube()

- 그룹 별 산출한 결과 값의 소계를 제공하는 함수

- group by절에서만 사용 가능

- rollup() : 지정한 컬럼에 대해서 단방향 소계

- cube() : 지정한 컬럼에 대해서 양방향 소계

select
    dept_code,
    count(*)
from
    employee
group by
    rollup(dept_code)
order by
    dept_code;

전체 사원 수를 마지막에 추가하고 싶어서 rollup()함수를 이용해 산출한 결과 값의 소계를 나타내었습니다.

7번 행의 null 값은 dept_code에서의 null 값이며,

8번 행의 null 값은 rollup()함수에 의한 새로 생겨난 null 값입니다.

 

이를 구분해주기 위하여 grouping 함수를 사용해줍니다.

 

☞ grouping(value)

- 실제 데이터(0 반환)인 지, 집계 과정 생성된 데이터(1 반환)인 지 구분

select
    dept_code,
    grouping(dept_code),
    count(*)
from
    employee
group by
    rollup(dept_code)
order by
    dept_code;

이처럼 실제 데이터들은 0을 반환하고 집계 과정 생성된 데이터는 1을 반환하게 됩니다.

 

이를 이용하여 7번 행(실제 데이터)의 null 값은 '인턴', 8번 행(집계 데이터)의 null 값은 '전체'로 리턴되게 해보겠습니다.

select
--    dept_code,
    decode(grouping(dept_code), 0, nvl(dept_code, '인턴'), 1, '전체') dept_code,
    count(*) cnt
from
    employee
group by
    rollup(dept_code)
order by
    dept_code;

 

직급 코드 별 인원 수와 소계 조회

select
--    decode(grouping(job_code), 0, job_code, 1, '소계'),
    nvl(job_code, '소계') 직급,
    count(*) cnt
from
    employee
group by
    rollup(job_code);

job_code에는 실제 데이터에 null 값이 없기 때문에 nvl()함수를 이용해서 구분해주었습니다.

 

부서 별, 직급 별 인원 수 조회 (소계 정보 포함)

select
    decode(grouping(dept_code), 0, nvl(dept_code, 'D0'), 1, '총계') 부서,
    decode(grouping(job_code), 0, job_code, 1, '소계') 직급,
    count(*) 인원수
from
    employee
group by
    rollup(dept_code, job_code);