RollUp 과 Cube의 차이
--※ROLLUP() 과 CUBE() 는
-- 그룹을 묶어주는 방식이 다르다. (차이)
--ex.
--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) / ()
--==>>위의 과정은(ROLLUP()) 묶음 방식이 다소 모자랄 떄가 있고
-- 아래 과정은(CUBE()) 묶음 방식이 다소 지나칠 떄가 있기 때문에
-- 다음과 같은 방식의 쿼리를 더 많이 사용하게 된다.
-- 다음 작성하는 쿼리는 조회하고자 하는 그룹만
-- GROUPING SETS 를 이용하여 묶어주는 방식이다.Grouping Set
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;
--ROLLUP을 사용한 결과와 같은 조회 결과 반환
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 전체직종 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 전체직종 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 전체직종 9400
인턴 CLERK 3500
인턴 SALESMAN 5200
인턴 전체직종 8700
전체부서 전체직종 37725
*/Group by 예제
--○TBL_EMP 테이블을 대상으로 입사년도별 인원수를 조회한다.
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
SELECT TO_CHAR(HIREDATE, 'YYYY') "입사년도"
,COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY ROLLUP(TO_CHAR(HIREDATE,'YYYY'));유의점
ROLLUP에서 TO_CHAR 로 묶었으면 TO_CHAR로 SELECT
EXTRACT 로 묶었으면 EXTRACT 로 SELECT
즉 그룹으로 묶은 데이터형과 select로 출력하는 데이터 형은 일치되어야 한다. 이는 group by와 같이 case when을 쓸때도 마찬가지 then이 숫자고 else가 문자면 에러가 나게되어있다.
SELECT CASE GROUPING(EXTRACT(YEAR FROM HIREDATE)) WHEN 0
THEN EXTRACT(YEAR FROM HIREDATE)
ELSE '전체'
END "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
--CASE 에서 EXTRACT는 숫자인데 ELSE 는 문자라서 에러
SELECT CASE GROUPING(TO_CHAR(HIREDATE,'YYYY')) WHEN 0
THEN EXTRACT(YEAR FROM HIREDATE)
ELSE '전체'
END "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
-- GROUPING(TO_CHAR(HIREDATE,'YYYY')) WHEN 0
-- 여전히 숫자 THEN EXTRACT(YEAR FROM HIREDATE)
-- 문자 ELSE '전체'
--이기때문에 에러발생
SELECT CASE GROUPING(TO_CHAR(HIREDATE,'YYYY')) WHEN 0
THEN TO_CHAR(HIREDATE,'YYYY')
ELSE '전체'
END "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--ORA-00979: not a GROUP BY expression
--에러가 달라졌다.
--GROUP BY로 묶을떄는 숫자로 묶꼬 GROUPING에서는 TO_CHAR로 문자형을 썻다.
SELECT CASE GROUPING(EXTRACT(YEAR FROM HIREDATE)) WHEN 0
THEN TO_CHAR(HIREDATE,'YYYY')
ELSE '전체'
END "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
--ORA-00979: not a GROUP BY expression
SELECT CASE GROUPING(TO_CHAR(HIREDATE,'YYYY')) WHEN 0
THEN TO_CHAR(HIREDATE,'YYYY')
ELSE '전체'
END "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(TO_CHAR(HIREDATE,'YYYY'))
ORDER BY 1;
--혹은
SELECT CASE GROUPING(EXTRACT(YEAR FROM HIREDATE)) WHEN 0
THEN EXTRACT(YEAR FROM HIREDATE)
ELSE -1
END "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY CUBE(EXTRACT(YEAR FROM HIREDATE))
ORDER BY 1;
/*
-1 19
1980 1
1981 10
1982 1
1987 2
2022 5
*/
--숫자면 숫자로 맞추고
⛏️Having절
--○EMP 테이블에서 부서번호가 20,30인 부서를 대상으로
-- 부서의 총 급여가 10000보다 적을 경우만 부서별 총 급여를 조회한다.
SELECT DEPTNO "부서번호"
, SUM(SAL) "부서 총 급여"
FROM EMP
WHERE DEPTNO IN (20,30)
AND SUM(SAL) <10000 --ORA-00934: group function is not allowed here 그룹함수인 SUM은 여기서 쓸수 없다.
GROUP BY DEPTNO;
SELECT DEPTNO "부서번호"
, SUM(SAL) "부서 총 급여"
FROM EMP
WHERE DEPTNO IN (20,30)
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000;
즉 Having 절에서는 그룹함수를 사용할수있다.
주의점
SELECT DEPTNO "부서번호"
, SUM(SAL) "부서 총 급여"
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) < 10000
AND DEPTNO IN (20,30);
--30 9400 되긴 되지만
--AND DEPTNO IN (20,30) 이 구문은 WHERE에 있는게 바람직하다 왜냐면
--FROM 으로 테이블을 조회하고 WHERE 절에 만족하는 것만 메모리에 퍼올리기때문
--오라클 입장에서는 EMP 테이블을 싹다 올려서 그 안에서 데이터 정리 VS EMP테이블중 필요한 메모리만 올려서 데이터를 정리하기
당연히 후자가 좋은방법🧐서브상관 쿼리
-
※서브 상관 쿼리(상관 서브 쿼리)
- 메인 쿼리가 있는 테이블의 컬럼이
- 서브 쿼리의 조건절(WHERE절, HAVING절)에 사용되는 경우
- 서브 상관 쿼리(상관 서브 쿼리) 라고 부른다.
--서브쿼리를 통해 급여등수 구하기
SELECT ENAME "사원명" , E.SAL "급여" , (SELECT COUNT(*)+1
FROM EMP
WHERE SAL > E.SAL) "급여등수"
FROM EMP E
ORDER BY 3;
--즉 SELECT로 출력되는 급여가 EMP테이블의 전체 SAL 중에서 몇등인지 알수있게한 쿼리문예제 1
--EMP 테이블을 대상으로
-- 사원명, 급여, 부서번호, 부서내 급여등수, 전체급여 등수 항목을 조회한다.
-- 단 서브 상관 쿼리를 활용할 수 있도록 한다.
SELECT E.ENAME "사원명" , E.SAL"급여", E.DEPTNO"부서번호"
, (1) "부서내 급여등수"
, (1)"전체급여등수"
FROM EMP E
ORDER BY 5;
--답
SELECT ENAME "사원명", E.SAL "급여", DEPTNO
,(SELECT COUNT(*)+1 FROM EMP WHERE SAL > E.SAL) "전체급여등수"
,(SELECT COUNT(*)+1 FROM EMP WHERE SAL > E.SAL AND DEPTNO = E.DEPTNO) "부서내 급여등수"
FROM EMP E
ORDER BY 3,5;예제 2
--○EMP 테이블을 대상으로 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
-각 부서내에서 입사일자별로 누적된 급여의 합
---------------------------------------------------------------
사원명 부서번호 입사일 급여 부서내입사별급여누적
-------------------------------------------------------------
SMITH 20 1980-12-17 800 800
JONES 20 1981-04-02 2975 3775
FORD 20 1981-12-03 3000 6775
:
*/
SELECT E.HIREDATE "입사일",(SELECT SUM(SAL) FROM EMP WHERE DEPTNO = E. DEPTNO AND HIREDATE <= E.HIREDATE) "부서내 입사별 누적"
FROM EMP E;
SELECT ENAME "사원명", DEPTNO "부서번호", HIREDATE "입사일", SAL "급여", (SELECT SUM(SAL) FROM EMP WHERE DEPTNO = E. DEPTNO AND HIREDATE <= E.HIREDATE) "부서내 입사별 누적"
FROM EMP E;
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여", ( SELECT SUM(E2.SAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO) "부서내 입사별누적"
FROM SCOTT.EMP E1
ORDER BY 2, 3;
SELECT E1.ENAME "사원명", E1.DEPTNO "부서번호", E1.HIREDATE "입사일", E1.SAL "급여", ( SELECT SUM(E2.SAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO AND E2.HIREDATE <= E1.HIREDATE) "부서내 입사별누적"
FROM SCOTT.EMP E1 --SCPTT의 EMP테이블을 E1이라는 명칭을 붙여서 쓰기
ORDER BY 2, 3;
예제 3
--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'));
--방법 2
SELECT T1.입사년월, T1.인원수
FROM
(
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
)T1
WHERE T1.인원수 = ( SELECT MAX(T2.인원수)
FROM (
SELECT TO_CHAR(HIREDATE, 'YYYY-MM') "입사년월"
, COUNT(*) "인원수"
FROM EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY-MM')
)T2
)
ORDER BY 1;
/*
1981-02 2
1981-09 2
1981-12 2
1987-07 2
*/