그룹 함수

1. ROLLUP

 

ROLLUP이란 GROUP BY를 한 칼람에 대하여 Subsum을 해준다.

ROLLUP은 여러개의 컬럼에도 적용이 가능하다.

그리고 적용한 대상에는 null 값이 들어가는데 아래와 같이 활용이 가능하다.

SELECT DECODE(DEPTNO, NULL, 'DEPT_SUM',DEPTNO),
	   SUM
  FROM EMP
GROUP BY ROLLUP(DEPTNO)

 위 쿼리는 EMP테이블에 DEPTNO 별 Subsum이 포함이 되는 SUM을 보여준다.(물론 sum 함수를 써야함)

 

2개 이상을 활용할 경우

SELECT DECODE(DEPTNO, NULL, 'Allsum', DECODE(GRADE, NULL, 'Subsum', DEPTNO)), 
	   GRADE,
       SUM(SAL)
  FROM EMP
GROUP BY ROLLUP(DEPTNO, GRADE)

위와 같은 쿼리가 되는데

① DEPTNO 그룹별 GRADE 그룹의 subsum과

② DEPTNO 그룹별 subsum

③ 전체 sum이 나오게 된다.

그리고 DEPTNO 컬럼이 null일 경우 Allsum(3번), DEPTNO 컬럼이 null 이고 GRADE가 null일 경우 subsum(2번)이 나온다.

 

2. GROUPING 함수

 

GROUPING 함수는 ROLLUP, GROUPING SETS, CUBE에서 생성되는 값들을 구분하기 위해서 만들어진 함수다.

위에서는 내가 decode를 활용해서 구분을 하였는데 grouping을 활용하게 되면 유용하게 사용할 수 있다.

소계, 합계 등이 계산되면 GROUPING 함수는 1을 반환하고 그렇지 않으면 0을 반환한다.

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

위 쿼리가 실행되면 아래와 같은 결과가 나온다.

이를 또 decode를 활용하면서 보기 이쁘게 만들어보자.

select decode(grouping(deptno), 1, 'Allsum', decode(grouping(job), 1, '부서합계', deptno)) as gubun,
       job,
       sum(sal)
from emp
group by rollup (deptno, job);

통상 통계화면을 보여주면 이런식으로 많이 보여주기 때문에 이렇게 만들어봤다.

3. GROUPING SETS은

GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있다.

deptno와 job을 각각 컬럼으로 주면 각각 null 이 생긴다.

아래와 같은 쿼리로 또 decode를 써서 null이 안보이게끔 해준다. 깔끔하게~ 직무별 합계와 부서별 합계~

select decode(deptno, null, job, deptno), sum(sal)
from emp
group by grouping sets ( deptno, job );

 

 

4. CUBE

CUBE는 걸어준 컬럼에 대해 결합 가능한 모든 집계를 산출한다.

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

위 결과를 보면 1차적으로 (부서, 직무)별 합계, 부서별 합계, 직무별 합계, 전체 합계가 나온다.

 

좀 더 이쁘게 통계스럽게 도출하고싶다?

Select decode(deptno, null, decode(job, null, '전체합계', '직무별'), decode(job, null, '부서별', deptno)), job, sum(sal)
from emp
group by cube (deptno, job)
order by deptno, job;

이쁘게 나오지 않는가? ㅎㅎ