ROLLUP의 ORDER BY(정렬)

가. ROLLUP의 정렬방법

ROLLUP 기술을 사용할 때 사용자가 ROLLUP에 의해 생성되는 SUB/ALL TOTAL값들을 먼저 보고싶어하는 경우가 종종 있습니다. 예를들어 부서가 100개 정도 되는데 SUB/ALL TOTAL을 중간과 끝을 일일히 확인하는게 시간도 오래걸리고 힘들기 때문입니다.

그래서 ALL/SUB TOTLA을 먼저 보는것이 시간도 절약하고 빨리 데이터를 파악할수 있기때문입니다. 100개 팀을 2차원 ROLLUP으로 데이타를 조회해도 최소 수천~수만개의 데이터가 나오는데 당연히 먼저 보고 싶어하지 않겠는가?

나. ROLLUP의 정렬원리

아래의 예를 봅니다.
SUB/ALL TOTAL을 먼저 보여주는 원리는 간단합니다. GROUPING 혹은 GRUPING_ID함수를 이용해서 조회되는 값들을 더하여 보여주면 됩니다. 아래 값에서 AA 와 BB로 표기된 칼럼을 보면 ALL TOTAL값은 AA와 BB 칼럼값이 각각 1, 1입니다 이를 더하면 2가 되므로 가장 큰 값이 됩니다.
이 값을 DESC 형태로 조회하면 가장 먼저 데이터가 위치하게 됩니다. 이런 원리로 나머지 SUB TOTAL들도 그 다음에 위치 시킬수 있습니다.

다. ROLLUP의 정렬시 bug발생 회피방법

여기서 주의 할 것은 원래 ORDER BY 절에 (GROUPING(dname) + GROUPING(job)) DESC 이런 형태로 사용하게 되면 ORA-00979 에러를 만나게 됩니다.

이를 메타링크에서 찾아서 문서에 나온 방법대로 해도 이 에러가 계속 나타난다. 그러므로 ROLLUP의 ORDER BY절을 사용할때는 SELECT 절에 GROUPING(dname), GROUPING(job)를 적어주고 그 별칭을 ORDER BY 절에 적어서 사용하면 이 에러를 비켜갈수 있습니다.
ORA-00979는 어떤 경우는 나타나고 어떤경우는 안 나타나므로 만일 에러가 발생하면 위에서 이야기한대로 별칭을 ORDER BY절에 넣어서 처리하기 바랍니다.

라. ROLLUP의 정렬 실전예제

SELECT     DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,
            DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
            COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal" ,
            GROUPING(dname) AA, GROUPING(job) BB
FROM  EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY (AA+BB) DESC , DNAME DESC, JOB ASC;

DNAME	JOB	TOTAL	Average Sal	AA	BB
All Departments	All Jobs	14	23250	1	1
SALES	All Jobs	6	18800	0	1
RESEARCH	All Jobs	5	21540	0	1
ACCOUNTING	All Jobs	3	35000	0	1
SALES	CLERK	1	11400	0	0
SALES	MANAGER	1	34200	0	0
SALES	SALESMAN	4	16800	0	0
RESEARCH	ANALYST	2	24600	0	0
RESEARCH	CLERK	2	11400	0	0
RESEARCH	MANAGER	1	35700	0	0
ACCOUNTING	CLERK	1	15600	0	0
ACCOUNTING	MANAGER	1	29400	0	0
ACCOUNTING	PRESIDENT	1	60000	0	0

아래의 SQL은 에러가 발생합니다.

SELECT     DECODE(GROUPING(dname), 1, 'All Departments',dname) AS dname,
            DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
            COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal" 
FROM  EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY (GROUPING(dname) + GROUPING(job)) DESC , DNAME DESC, JOB ASC;

ORA-00979 GROUP BY 표현식이 아닙니다.

아래의 SQL로 실습을 한번 해봅니다.

SELECT    GUBUN, AREA_NM, DEPT_NM, SUM(SAL), <br/>GROUPING(GUBUN) AA, GROUPING(AREA_NM) BB
FROM  ROLL
GROUP BY ROLLUP (GUBUN, (AREA_NM, DEPT_NM, CORP_NM))
ORDER BY (AA+BB) DESC , GUBUN DESC, AREA_NM ASC;

=>에러없이 잘 동작함을 알수있습니다