ORACLE :: ROLLUP, CUBE, GROUPING SETS, HAVING절

 

ROLLUP ( )

 

ROLLUP 함수는 GROUP BY 절과 같이 사용 되며, 

GROUP BY 절에 의해서 그룹 지어진 집합 결과에 대해서

좀 더 상세한 정보를 반환하는 기능을 수행 한다. (합계, 소계 값까지)

 

 

SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);

 

위의 쿼리문은 ROLLUP 함수를 사용하여 모든 부서의 합도 구하고 있다. 결과는 아래와 같다.

 

/*
부서번호       급여합
10	                 8750
20	               10875
30	                 9400
(NULL)		        29025
*/

SELECT NVL(TO_CHAR(DEPTNO), '모든부서') "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);

 

NVL( ) 를 통해 NULL 이 있던 자리에 '모든부서' 값을 넣어주면 아래와 같은 결과가 나온다.

 

/*
10	            8750
20	            10875
30	            9400
모든부서		29025
*/

 

 

GROUPING( )

 

GROUPING 함수는 해당 열이 GROUP BY 에 의해서 산출된 열인 경우에는 0을 반환하고, 

ROLLUP( )이나 CUBE( )에 의해서 산출된 열인 경우에는 1을 반환하게 된다.

 

SELECT GROUPING(DEPTNO) "GROUPING", DEPTNO "부서번호", SUM(SAL) "급여"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
/*
	GROUPING       부서번호         급여
	---------- 		---------- 		----------
         0                   10       8750
         0                   20      10875
         0                   30       9400
         0                              8700         -- 인턴
         1                              37725       -- 모든 부서

*/

 

이러한 반환값을 사용하여 (WHERE절 사용) 부서번호 값이 (NULL)인 곳에 값을 넣어줄 수 있다.

 

 

SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO), '인턴')
                                                         WHEN 1 THEN NVL(TO_CHAR(DEPTNO), '모든부서')
                                                         ELSE '알 수 없음'
                                                         END "부서번호"
            , SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
부서번호                                     급여합
-----------------            ----------
10                                              8750
20                                            10875
30                                              9400
인턴                                            8700
모든부서                                    37725
*/

이런 식으로 !! 

 

 

CUBE( )

 

다음은 CUBE( ) 이다. 이 함수는 ROLLUP( ) 보다 더 자세한 결과를 반환한다.

 

ROLLUP( ) 과 CUBE( ) 는 그룹을 묶어주는 방식에서 차이가 있다.

 

-- ROLLUP(A, B, C)
-- → (A, B, C) / (A, B) / (A) / ( )

-- CUBE(A, B, C)
-- → (A, B, C) / (A, B) / (A, C) / (B, C) / (A) / (B) / (C) / ( )

 

GROUPING SETS( )

 

위에서 사용한 것(ROLLUP( ))은 묶음 방식이 다소 모자라고
아래에서 사용한 것(CUBE( ))은 묶음 방식이 다소 지나치기 때문에
다음과 같은 방식의 쿼리 형태를 더 많이 사용한다.
다음 작성하는 쿼리는 조회하고자 하는 그룹만 『GROUPING SETS』를 이용하여 묶어주는 방식이다.

 

 

SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO), '인턴')
                        	 ELSE '전체부서' END "부서번호"
     , CASE GROUPING(JOB) WHEN 0 THEN JOB
                          ELSE '전체직종' END "직종"
     , SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY GROUPING SETS((DEPTNO, JOB), (DEPTNO), ())
ORDER BY 1, 2;

 

 

위와 같이 GROUPING SETE( )를 사용하면 원하는 것끼리 그룹을 묶을 수 있다!!

 

 

 

주의할 점

 

GROUP을 만들 때 주의해야 할 점이 있다!!

쿼리문의 진행 순서는 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 순서로 진행된다.

GROUP BY를 한 뒤에 SELECT 가 수행되므로 GROUP BY 에서 사용한 자료형을 SELECT절에서도 사용해야 한다.

 

 

SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
     , COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE, 'YYYY'))
ORDER BY 1;

 

위의 코드를 보면 GROUP BY 절에서는 문자형으로 묶어주고 있고,

SELECT 절에서는 숫자형으로 조회하고 있기 때문에 에러가 나버린다.

 

SELECT TO_CHAR(HIREDATE, 'YYYY') "입사년도"
     , COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE, 'YYYY'))
ORDER BY 1;

 

따라서 위와 같이 자료형을 통일해줘야 한다. 

 

 

 

HAVING절

 

HAVING 절은 GROUP BY 로 집계된 값 중 WHERE 절 처럼 특정 조건을 추가할 때 사용한다.

집계함수는 WHERE 절에서 사용할 수 없기 때문에 HAVING 절을 통해 조건을 추가한다.

 

 

SELECT DEPTNO "부서번호", SUM(SAL) "부서별총급여"
FROM EMP
WHERE DEPTNO IN (20, 30)
      AND SUM(SAL) < 10000
GROUP BY DEPTNO;

 

위의 코드에서 SUM( )은 집계(그룹)함수이다. 

그런데 WHERE절에서 사용하고 있으므로 에러가 난다.

 

 

SELECT DEPTNO "부서번호", SUM(SAL) "부서별총급여"
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000;
-- 부서번호가 20이나 30인 사람들 중 
-- 부서별총급여가 10000 이상인 부서의 부서번호와 부서별총급여를 조회한다.

 

따라서 위와 같이 HAVING 절을 사용하여 조건을 추가한다.

 

 

 

중첩 그룹함수 / 분석함수

 

그룹 함수는 2 LEVEL 까지 중첩해서 사용할 수 있다.

 

SELECT MAX(SUM(SAL))
FROM EMP
GROUP BY DEPTNO;

 

위의 코드는 2 LEVEL의 그룹 함수를 사용하였다.

먼저 SUM( ) 1레벨,

또 그 합의 최대값을 구하는 MAX( ) 2레벨

이제 더 이상 집계함수는 사용할 수 없다!

 

 

RANK( ) / DENSE RANK( ) 사용하지 않고 순위 구하기 (서브 상관 쿼리)

 

 

하위 버전에서는 RANK( ) 나 DENSE_RANK( ) 를 사용할 수 없기 때문에
예를 들어 급여 순위를 구하고자 한다면 해당 상원의 급여보다 더 큰 값이 몇 개인지 확인한 후
확인한 값에 +1 을 추가 연산해 주면 그 값이 곧 해당 사원의 급여 등수가 된다.

 

 

 

※ 서브 쿼리 (상관 서브 쿼리)

 

- 메인 쿼리가 있는 테이블의 컬럼이

  서브 쿼리의 조건절에 사용되는 경우

  우리는 이 쿼리문을 서브 상관 쿼리라고 부른다.

 

SELECT ENAME "사원명", SAL "급여"
     , (SELECT COUNT(*) + 1 
        FROM EMP 
        WHERE SAL > E.SAL) "급여등수"
FROM EMP E
ORDER BY "급여등수";

 

위의 쿼리문을 보면 서브 쿼리의 조건절 안에 메인 쿼리에 있는 테이블(E)의 컬럼(E.SAL)이 사용되고 있다!!

 

 

--○ EMP 테이블을 대상으로
--    입사한 사원의 수가 가장 많았을 때의
--    입사년월과 인원수를 조회할 수 있도록 쿼리문을 구성한다.

SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월", COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
HAVING COUNT(*) = (  SELECT MAX(COUNT(*))
                     FROM EMP
                     GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM'
                   ));

 

또 위의 쿼리문을 보면 HAVING(조건)절에 메인 쿼리에 있는 테이블의 컬럼(HIREDATE)이 사용되고 있다!!

 

 

---

예전에 데이터베이스 이론을 잠깐 배웠었는데...

직접 실습을 해 보니 너무너무너무~ 어렵다!

배운 당일 날 정리하는 습관을 들이는 것이 정말 중요한 것 같다..

꾸준히 작성하도록 하자 !!♥