그룹 함수-rollup, rollup orderby, grouping

GROUP BY로 그룹핑한 로우들의 갯수나, 합계 등을 구해주는 기능이있다.

oracle에서는 ROLLUP인데 mysql에서는WITH ROLLUP을 사용한다.

1. oracle

select count(*) 'total empl', sum(SAL) 'total sal'
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by rollup (DNAME, JOB) -----> rollup 사용

2. mysql

SELECT COUNT(IDX),IDX,WRITER,CONTENTS
FROM TB_BOARD_COMMON_BJS
GROUP BY  WRITER WITH ROLLUP --> rollup 사용

가장 중요한 것은 SELECT 절에 집계함수를 사용 해야 한다. 그래야 결과가 바르게 나옴

2번 같은 경우 결과는 이런식으로 나온다.

실제 테이블을 보면 WRITER는 2가지 종류고 NULL은 없다.

여기서 마지막 행은 null로 나오며 count(IDX)의 결과의 총 갯수의 합을 나타낸 것이다.

그래서 IDX도 2,3행이 겹치는 것을 볼 수 있다.

-------------------------------------------------------------------------------------------------------------

order by 추가

부서별, 업무별로 group by 하고, group by 한 것의 총 갯수와 총 합계를 구함. 그리고 orderby로 정리해서 보여줌

select DNAME, JOB,
	count(*) 'total empl',
        sum(SAL) 'total sal'
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by rollup (DNAME, JOB) 
order by DNAME,JOB;      ---> DNAME : 부서 JOB:업무

결과를 보면 알 수 있듯이 부서별로 먼저 정렬이 되고, 이후에 JOB 별로 정렬이 되서 total empl과 total sal 값이 나오는 것을 볼 수 있다.

------------------------------------------------------------------------------------------------------------

grouping 함수

ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가되었음.

ROLLLUP이나 CUBE에 의한 소계가 계산된 결돠에는 GROUPING(컬럼명) = 1이 표시되고,

그외의 결과에는 GROUPING(컬럼명) = 0 이 표시된다.

 

이것을 활용해서 합계와 소계의 명칭을 정해 줄 수 있다. 쿼리를 작성하면..

select 
   	case GROUPING(DNAME) 	when 1 then "All Departments" else DNAME end as DNAME,
   	case GROUPING(JOB) 	when 1 then "All Jobs" 	   else JOB 	 end as JOB,
   	count(*) 'total empl',
        sum(SAL) 'total sal'
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by rollup (DNAME, JOB) 
order by DNAME,JOB;      

이런식으로 <참고> 작성 or if문이나 decode(오라클)문을 사용하면 case보다 짧아진다.

위에 orderby에서 빈 부분(null) 부분에 case로 작성한 글씨가 들어가게 된다.

 

만약 rollup조건을 JOB에만 준다면?

select 
   	case GROUPING(DNAME) 	when 1 then "All Departments" else DNAME end as DNAME,
   	case GROUPING(JOB) 	when 1 then "All Jobs" 	   else JOB 	 end as JOB,
   	count(*) 'total empl',
        sum(SAL) 'total sal'
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by DNAME, rollup (JOB)  -----> rollup을 JOB에만 줌
order by DNAME,JOB;      

위의 결과에서 ALL Departments 만 삭제가 되며, 나머지는 동일하게 출력 된다.

-------------------------------------------------------------------------------------------------------

변형된 rollup

select DNAME,JOB, MGR, SUM(SAL) as "total Sal"
from EMP, DEPT
where DEPT.DEPTNO = EMP.DEPTNO
group by rollup (DNAME, (JOB,MGR))    ----> rollup괄호 안에 JOB,MGR을 결합해서 사용

ROLLUP 함수 사용시 괄호로 묶은 JOB과 MGR의 경우 하나의 집합(JOB+MGR) 칼럼으로 간주하여 괄호 내 각 칼럼 별 집계를 구하지 않는다.

이렇게도 사용이 가능하다.