SQL 데이터 분석함수 - 집계결과 출력 Rollup

ROLLUP (집계 결과 출력하기)

group by 절에 rollup 을 이용하게 되면 전체 집계값이 아래에 출력이 됩니다.

만약 sum 을 쓰면 sum 값, avg 면 전체 avg 값등 수식에 따라 다르다.

예제 : 직업, 직업별 토탈월급을 출력하는데 맨 아래에 전체토탈월급이 출력 되도록 하시오

select job, sum(sal) 
from emp
group by rollup (job);

→ 맨 아래에 전체 토탈월급(집계값) 이 출력된다.

문제 226 : 부서번호, 부서번호별 평균월급을 출력, 맨아래에 전체평균월급도 출력

select deptno, avg(sal) 
from emp 
group by rollup(deptno);

문제 227 : 나이, 나이별 인원수를 출력하는데 맨 밑에 전체 인원수 출력

select age, count(*)
from emp14 
group by rollup(age) ;

 

문제 228 : 위의 결과가 나이가 asc 하게 나오는데 desc하게 출력으로 바꾸시오

select age, count(*) 
from emp14 
group by rollup(age)
order by 1 desc nulls last ;

• order by 컬럼 에 null 출력 위치를 지정할 수 있다.  asc/desc nulls last/ nulls first

문제 229 : 위의 결과를 다시 출력 null로 출력 되는 자리에 토탈인원이라는 글씨가 출력되도록하시오

select nvl(to_char(age),'토탈인원'), count(*) 
from emp14 
group by rollup(age)
order by 1 desc ;

→이렇게 해야 토탈인원값이 맨 밑으로 출력값이 나온다.

select nvl(to_char(age),'토탈인원'), count(*) 
from emp14 
group by rollup(age)
order by age desc nulls last;

→ order by 절에 select 절의 숫자를 쓰지않고 컬럼명을 써서 실행하면

order by 절까지 실행하고 select 절의 nvl 함수를 실행하는것이고

order by 절에 숫자를 다 쓰게 되면 nvl 함수를 실행하고 나서 order by 절이 실행 됩니다.

그래서 아무리 Nulls last 를 작성해도 age 에는 Null 값이 없기 때문에 null 값이 마지막으로 출력되지 않는다. 

문제 :

select to_char(hiredate, 'RRRR'), sum(sal) 
from emp 
group by rollup (to_char(hiredate, 'RRRR'));

Rollup 함수에 컬럼을 여러개 쓰기

문제 231 : 부서번호, 직업, 부서번호별 직업별 토탈월급을 출력

select deptno, job, sum(sal)
from emp 
group by deptno, job
order by deptno, job;

→ 그동안에 group by 절에 컬럼을 한가지만 작성했었는데, 위와같이 group by 절에 컬럼을 여러개를 사용할 수 있다.

group by 절에 여러개의 컬럼이 있다는건 select 절에도 컬럼이 여러개 있다는 뜻이다.

select 절에 있는 컬럼들 ,그룹컬럼 제외한 컬럼들에 그룹바이 절에 써 주어야 오류가 나지 않는다.

문제 232 : class_type , 나이, 인원수, 인원수가 class_type 별 나이 별 인원수가 출력 되도록 하세요

select class_type, age, count(*) 
from emp14 
group by class_type, age 
order by class_type, age desc;

문제 233 : 위의 결과에서 맨 아래에 전체 인원수가 출력 되도록 하시오

select class_type, age, count(*) 
from emp14 
group by rollup((class_type, age));

→ rollup 에 컬럼을 여러개 쓴 상태에서 맨 아래에 전체집계를 출력하려면

rollup ((컬럼명1, 컬럼명2)) 이렇게 괄호를 두번 묶어주어야한다.

한번만 묶개 되면 그룹핑 한 컬럼별로의 집계와, 총 전체 집계가 출력 된다.

문제 234 : A반의 전체 인원수와, B반의 전체 인원수가 출력이 되게 하고 싶다면

select class_type, age, count(*) 
from emp14 
group by rollup((class_type), (age));

→ 그룹에 컬럼명 () 괄호를 각각 둘러주게 되면, 먼저 class_type 별로 카운트한 결과가 나오고,

그룹핑한 결과는 rollup 안에 쓴 컬럼의 개수 +1 개

select 컬럼이 두개이므로

group by 절에 컬럼 두개를 그룹핑하고+ 전체 총 3가지 나온다.

그룹핑한결과가 3가지

1. Class_type , age
2. class_type
3. 전체

문제 235 번 : 아래의 결과를 출력하세요

select deptno, job, sum(sal) 
from emp 
group by rollup(deptno, job);

그룹핑 된걸과가 3가지 나온다.

rollupp 에 쓴 컬럼이 2개니까 +1 해서 3가지

1. deptno, job
2. deptno
3. 전체

문제 : 사원번호, 이름, 월급이 출력 되는데 월급 맨 밑에 토탈월급이 출력 되도록 출력하세요

select empno, ename, sum(sal)
from emp 
group by rollup((empno,ename));

문제 238 : class_type, 통신사, 인원수 출력 하는데, 아래와 같이 집계 된 결과가 출력 되게 하시오

select class_type, 
      decode(lower(telecom), 'skt','sk',lower(telecom)),
      count(*)
from emp14 
group by rollup(class_type, decode(lower(telecom),'skt','sk',lower(telecom)));

---

오라클 버전 마다. 정렬을 다르게 해준다.

SQL ——→ 옵티마이저 ———→ 실행계획

---

문제 239 : 부서번호, 직업, 부서번호별 직업별 토탈월급 출력 (asc 하게)

select deptno, job, sum(sal) 
from emp 
group by deptno, job
order by deptno, job;

문제 240 : 위의 결과를 다시 출력하고, rollup 을 사용하여 각각의 부서번호별 토탈월급과 전체 토탈월급이 출력 되도록하시오

select deptno, job, sum(sal) 
from emp 
group by rollup(deptno, job) 
order by deptno, job;

select job, deptno, sum(sal) 
from emp
group by rollup(job, deptno) 
order by job, deptno;

→ 두개의 결과는 다르다,

deptno , job 의 컬럼의 출력 위치에 따라서 어떻게 집계되서 토탈월급이 다르게 나오는지 확인가능하다.

→ deptno가 먼저 나오게 되면, 부서번호별 토탈월급

-> job이 먼저 나오게 되면 직업별 토탈월급이 나온다.

rollup 을 사용하는 현업사례

힌트 : grouping 함수를 이용해서 아래의 SQL 을 실행하고 grouping 함수를 이해하시오

decode를 사용하여 푸시오 or case 문

---

• *decode 이용

select deptno,
       decode(grouping(deptno)+grouping(job), 2 , '전체토탈:' ,
                                              1 , '부서토탈:' , job ) as job, 
       sum(sal)
from emp
group by rollup(deptno,job);

 

→ 결과값이 숫자가 나오면 더해도 된다.

따라서 grouping (deptno ): 1 이고 grouping (job) : 1 이기때문에

이 두개를 더한값이 2 이면 '전체토탈' 이고 1이면 부서토탈 로 뽑는다. 라는 식으로 작성 가능하다. 

---

select deptno,
       decode(grouping(deptno),1,'전체토탈:', nvl(job,'부서토탈:')) as JOB, 
       sum(sal)
from emp
group by rollup(deptno,job);

→decode 식 풀이 

grouping(deptno) 의 값이 1 이면 전체토탈이라고 출력하고, job의 컬럼 null 값에 '부서토탈' 이라고 Nvl 치환해서 출력해라. 

---

select nvl(to_char(deptno),'')as부서번호,
       decode(grouping(deptno),1,'전체토탈:',nvl(job,'부서토탈:')) as 직업, 
       sum(sal)
from emp
group by rollup(deptno,job) ;

→ 위의 식과 다른점은 코딩실행하면 알 수 있다. 그다지 중요한것은 아님 

---

 

• *case when 이용

---

 

Select deptno ,case when deptno is null then '전체토탈'                
                    when job is null then '부서토탈'
                    else job 
                    end as "job",
       sum(sal)
from emp
group by rollup(deptno, job);

 

select deptno, case when grouping(deptno)+grouping(job) = 2 then '전체토탈'
                    when grouping(deptno)+grouping(job) = 1 then '부서토탈'
                    else job end as job2, 
       sum(sal)
from emp
group by rollup(deptno, job);

 

select deptno, case when grouping(deptno)=1 and grouping(job)=1 then '전체토탈'
                    when grouping(deptno)=0 and grouping(job)=1 then '부서토탈'
                    else job end as job, 
       sum(sal)
from emp
group by rollup(deptno, job);

 

 

---

→ deptno 에서 전체집계값을 출력하다보니 컬럼 job 에 null 이 생기게 되고 grouping (job) 컬럼의 null 의 갯수 1이 생긴다.

설명 : grouping(deptno)와 grouping(job)은 집계결과를 보기위해서 어쩔 수 없이 null로 나오는 부분을 1로 출력하고 데이터가 있는 부분은 0으로 출력합니다.

---

 rollup 과 같은 레포팅성(집계결과) 결과를 출력하는 함수

1. rollup
2. cube
3. grouping sets
4. grouping