220706 TIL

국비교육 26일차 Oracle : 요약값(rollup, cube, grouping sets, grouping), having 그룹함수조건절, 누적(누계)

1. 요약값 보여주기

rollup, cube, grouping sets, grouping

• 예시는 hr 테이블 사용

1) rollup

• 소계와 합계 구하기

-- employees 테이블에서 부서번호별로 인원수를 나타내면서 동시에 전체 인원수도 나타내세요
select department_id as 부서번호       
     , count(*) as 인원수
from employees 
group by rollup(department_id)

• 이렇게 나타내면 오름차순으로 정렬되는데 null 값이 2개가 나온다
• 하나는 그룹지어진 것이 아닌 전체 인원 수를 합친 107명의 null 이고, 다른 하나는 부서 번호가 없는 킴벌리의 null 이다.

2) grouping

• group으로 만든 데이터인지 아닌지 구분하기 위해서 사용한다.
• NVL을 사용해서 NULL 값을 구분해준다.

select department_id as 부서번호    
     , grouping(department_id)  as grouping여부
     , count(*) as 인원수 
from employees 
group by rollup(department_id);

• rollup(department_id) 에서 grouping을 썼을 때 grouping(department_id) 은 결과값이 오로지 2개만 나온다. 0 또는 1이 나오는데, 0이라함은 department_id 컬럼의 값으로 그룹을 지었다는 말이고, 1이라 함은 그룹을 안지었다는 말이다.

select decode( grouping(department_id), 0, NVL( to_char(department_id), '인턴'), '전체')  as 부서번호
                                            -- to_char(department_id) 여기서 null 값이 있다면 인턴을 주고
                                            -- 전체 더한 값인 null은 전체를 준다
     , count(*) as 인원수
from employees 
group by rollup(department_id)

3) rollup, cube, grouping sets의 공통점과 차이점

• rollup(a,b,c) 은 grouping sets( (a,b,c),(a,b),(a),() ) 와 같다.
• cube(a,b,c) 은 grouping sets( (a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),() ) 와 같다.
  3개 -> 2개씩 묶을 수 있는 모든 경우의 수 -> 1개로 나올 수 있는 모든 경우의 수
  cube는 좀 더 상세하게 보여준다.
• grouping sets를 통하여서 내가 보고싶은 그룹 조합만 뽑아서 보여줄 수 있다.

2. having 그룹함수조건절

group by 절을 사용하여 그룹함수의 값을 나타내었을때,
그룹함수의 값이 특정 조건에 해당하는 것만 추출하고자 할때는
where 절을 사용하는 것이 아니라 having 그룹함수조건절 을 사용해야 한다.
having에는 그룹함수(sum, avg 등)만 조건으로 올 수 있다.

--- employees 테이블에서 사원이 10명이상 근무하는 부서번호와 그 인원수를 나타내세요
select department_id as 부서번호
     , count(*) as 인원수
from employees
group by department_id
having count(*) >= 10
order by 2;

3. 누적(누계)

- sum(누적되어야할 컬럼명) over(order by 누적되어질 기준이 되는 컬럼명 asc[desc] )
- sum(누적되어야할 컬럼명) over(partition by 그룹화 되어질 컬럼명 
                         order by 누적되어질 기준이 되는 컬럼명 asc[desc] )