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)이 사용되고 있다!!
예전에 데이터베이스 이론을 잠깐 배웠었는데...
직접 실습을 해 보니 너무너무너무~ 어렵다!
배운 당일 날 정리하는 습관을 들이는 것이 정말 중요한 것 같다..
꾸준히 작성하도록 하자 !!♥