📚 Study/Oracle
Oracle :: GROUP BY(ROLLUP(), CUBE(), GROUPING SETS())
bono-hye
2023. 10. 29. 21:49
(파일명 20231024_01_scott)
■ GROUP BY
■ GROUP BY ROLLUP()
- 전체 결과도 같이 반환해준다!
- GROUP BY는 그룹지은 데이터들에 대한 결과 조회, ROLLUP 사용하면 그룹지어 나눠진 것 까지 합쳐서 결과 조회!
--○ TBL_EMP 테이블을 대상으로 부서별 급여합 조회
SELECT DEPTNO"부서번호", SUM(SAL)"부서별급여합"
FROM TBL_EMP
GROUP BY DEPTNO
ORDER BY 1;
--==>>
/*
부서번호 부서별급여합
10 8750
20 10875
30 9400
(null) 8700 -- 부서번호가 NULL인 사원들의 급여합!
*/
SELECT DEPTNO"부서번호", SUM(SAL)"부서별급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
8700 -- 부서번호가 NULL인 사원들의 급여합!
37725 -- 전체합
*/
■ 문제 풀이 1
-- 이렇게 조회하고 싶은데~~------
/*
10 8750
20 10875
30 9400
인턴 8700
*/
/*
10 8750
20 10875
30 9400
인턴 8700
모든부서 37725
*/
--------------------------------
SELECT DEPTNO "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
29025
*/
/*
10 8750
20 10875
30 9400
모든부서 29025 이렇게 나오게 하고 싶은데~!
*/
-- EMP 테이블을 대상으로 위와 같이 조회되도록 쿼리문을 구성한다.
-- ①
SELECT NVL(TO_CHAR(DEPTNO), '모든부서') "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);
-- ②
SELECT CASE WHEN TO_CHAR(T.부서번호) IS NULL THEN '모든부서'
ELSE TO_CHAR(T.부서번호)
END "부서번호"
, T.급여합
FROM
(
SELECT DEPTNO"부서번호", SUM(SAL)"급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO)
)T;
-- ③
SELECT DECODE(DEPTNO, NULL, '모든부서', TO_CHAR(DEPTNO)) "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);
-- 같이!
SELECT NVL(DEPTNO, '모든부서') "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);
--==>> 에러 발생
-- (ORA-01722: invalid number)
-- ①
SELECT NVL(TO_CHAR(DEPTNO), '모든부서') "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
모든부서 29025
*/
-- ②
SELECT NVL2(DEPTNO, TO_CHAR(DEPTNO), '모든부서') "부서번호", SUM(SAL) "급여합"
FROM EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
모든부서 29025
*/
■ 문제 풀이 2 (GROUPING)
-- 인턴까지 뽑아보자
SELECT NVL(TO_CHAR(DEPTNO), '모든부서') "부서번호", SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>> 인턴으로 뽑고 싶은데 그거까지 모든부서로 나옴ㅠㅠ
/*
10 8750
20 10875
30 9400
모든부서 8700
모든부서 37725
*/
--GROUPING()
SELECT GROUPING(DEPTNO), DEPTNO"부서번호", SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY DEPTNO;
--==>>
/*
0 30 9400
0 (null) 8700
0 20 10875
0 10 8750
*/
SELECT GROUPING(DEPTNO), DEPTNO"부서번호", SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
GROUPING(DEPTNO) 부서번호 급여합
---------------- --------- --------
0 10 8750
0 20 10875
0 30 9400
0 (null) 8700
1 (null) 37725
*/
--○ 위에서 조회한 해당 내용을
/*
부서번호 급여합
-------- --------
10 8750
20 10875
30 9400
인턴 8700
모든부서 37725
*/
-- 이와 같이 조회되도록 쿼리문을 구성한다.
--※ 힌트
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN '단일부서' ELSE '모든부서' END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
-- ①
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO), '인턴') ELSE '모든부서' END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
-- ②
SELECT CASE WHEN GROUPING(DEPTNO) = 0 AND DEPTNO IS NULL THEN '인턴'
WHEN GROUPING(DEPTNO) = 1 AND DEPTNO IS NULL THEN '모든부서'
ELSE TO_CHAR(DEPTNO)
END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
-- 같이 풀어~~
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN DEPTNO ELSE '모든부서' END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>> 에러 발생
-- (ORA-00932: inconsistent datatypes: expected NUMBER got CHAR)
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN TO_CHAR(DEPTNO) ELSE '모든부서' END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
(null) 8700
모든부서 37725
*/
SELECT CASE GROUPING(DEPTNO) WHEN 0 THEN NVL(TO_CHAR(DEPTNO),'인턴') ELSE '모든부서' END "부서번호"
, SUM(SAL) "급여합"
FROM TBL_EMP
GROUP BY ROLLUP(DEPTNO);
--==>>
/*
10 8750
20 10875
30 9400
인턴 8700
모든부서 37725
*/
■ 문제 풀이 3
--○ TBL_SAWON 테이블을 대상으로
-- 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
--------- ---------
성별 급여합
--------- --------
남 XXX
여 XXXX
모든사원 XXXXX
*/
--내가 풀었어용~!
SELECT *
FROM TBL_SAWON;
SELECT NVL(T.성별, '모든사원') "성별", SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성'
WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성'
ELSE '성별판별불가'
END "성별"
,SAL "급여"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.성별);
/*
남성 16800
여성 20300
모든사원 37100
*/
-- 같이 풀었어용~!!
--①
SELECT NVL(T.성별, '모든사원') "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성'
WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성'
ELSE '성별판별불가'
END "성별"
,SAL "급여"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.성별);
--==>>
/*
--------- ---------
성별 급여합
--------- --------
남 16800
여 20300
모든사원 37100
*/
-- ②
SELECT CASE WHEN THEN ELSE END "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성'
WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성'
ELSE '성별판별불가'
END "성별"
,SAL "급여"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.성별);
SELECT CASE GROUPING(T.성별) WHEN 0 THEN T.성별
ELSE '모든사원'
END "성별"
, SUM(T.급여) "급여합"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','3') THEN '남성'
WHEN SUBSTR(JUBUN,7,1) IN ('2','4') THEN '여성'
ELSE '성별판별불가'
END "성별"
,SAL "급여"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.성별);
SELECT *
FROM VIEW_SAWON;
■ 문제 풀이 4
--○ TBL_SAWON 테이블을 대상으로
-- 다음과 같이 조회될 수 있도록 쿼리문을 구성한다.
/*
------- -------
연령대 인원수
------- -------
10 X
20 X
50 X
전체 XX
------- -------
*/
-- 내가 풀어용~
--①
SELECT NVL(T2.연령대, '나이') "연령대", COUNT(T2.연령대) "인원수"
FROM
(
SELECT CASE SUBSTR(T.현재나이,1,1) WHEN '1' THEN '10대'
WHEN '2' THEN '20대'
WHEN '3' THEN '30대'
WHEN '4' THEN '40대'
WHEN '5' THEN '50대'
ELSE SUBSTR(T.현재나이,1,1) || '0대'
END "연령대"
FROM
(
SELECT EXTRACT(YEAR FROM SYSDATE)
- (CASE WHEN SUBSTR(JUBUN,1,1) = '0' THEN TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999
ELSE TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899
END) "현재나이"
FROM TBL_SAWON
)T
)T2
GROUP BY ROLLUP(T2.연령대);
--②
SELECT NVL(TO_CHAR(T.연령대), '전체') "연령대"
, COUNT(T.연령대) "인원수"
FROM
(
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2') THEN TRUNC((EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)), -1)
WHEN SUBSTR(JUBUN,7,1) IN ('3','4') THEN TRUNC((EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)), -1)
ELSE -1
END "연령대"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.연령대);
-- 같이 풀어용~!~!
-- 방법 1. → INLINE VIEW 를 두 번 중첩
SELECT NVL(TO_CHAR(T2.연령대), '전체') "연령대"
, COUNT(*) "인원수"
FROM
(
-- 연령대
SELECT CASE WHEN T1.나이 >= 50 THEN 50
WHEN T1.나이 >= 40 THEN 40
WHEN T1.나이 >= 30 THEN 30
WHEN T1.나이 >= 20 THEN 20
WHEN T1.나이 >= 10 THEN 10
ELSE 0
END "연령대"
FROM
(
-- 나이
SELECT CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)
WHEN SUBSTR(JUBUN,7,1) IN ('3','4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)
ELSE -1
END "나이"
FROM TBL_SAWON
)T1
)T2
GROUP BY ROLLUP(T2.연령대);
--==>>
/*
10 4
20 7
50 4
전체 15
*/
-- 방법 2. → INLINE VIEW 를 한 번만 사용
-- 연령대
SELECT CASE GROUPING(T.연령대) WHEN 0 THEN TO_CHAR(T.연령대)
ELSE '전체'
END "연령대"
, COUNT(*) "인원수"
FROM
(
SELECT TRUNC(CASE WHEN SUBSTR(JUBUN,7,1) IN ('1','2')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1899)
WHEN SUBSTR(JUBUN,7,1) IN ('3','4')
THEN EXTRACT(YEAR FROM SYSDATE) - (TO_NUMBER(SUBSTR(JUBUN,1,2)) + 1999)
ELSE -1
END, -1) "연령대"
FROM TBL_SAWON
)T
GROUP BY ROLLUP(T.연령대);
--==>>
/*
10 4
20 7
50 4
전체 15
*/
■ CUBE()
- ROLLUP() 보다 더 자세한 결과 반환
- ROLLUP과 CUBE는 그룹을 묶어주는 방식이 다름!
- EX) ROLLUP(A,B,C) → (A,B,C) / (A,B) / (A) / ()
- EX) ROLLUP(A,B,C) → (A,B,C) / (A,B) / (A,C) / (B,C) / (A) / (B) / (C) / ()
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1, 2;
--==>>
/*
10 CLERK 1300 -- 10번 부서 CLERK 직종의 급여합
10 MANAGER 2450 -- 10번 부서 MANAGER 직종의 급여합
10 PRESIDENT 5000 -- 10번 부서 PRESIDENT 직종의 급여합
10 (null) 8750 -- 10번 부서 모든 직종의 급여합
20 ANALYST 6000 -- 20번 부서 ANALYST 직종의 급여합
20 CLERK 1900 -- 20번 부서 CLERK 직종의 급여합
20 MANAGER 2975 -- 20번 부서 MANAGER 직종의 급여합
20 (null) 10875 -- 20번 부서 모든 직종의 급여합
30 CLERK 950 -- 30번 부서 CLERK 직종의 급여합
30 MANAGER 2850 -- 30번 부서 MANAGER 직종의 급여합
30 SALESMAN 5600 -- 30번 부서 SALESMAN 직종의 급여합
30 (null) 9400 -- 30번 부서 모든 직종의 급여합
(null) (null) 29025 -- 모든 부서 모든 직종의 급여합
*/
--○ CUBE() → ROLLUP() 보다 더 자세한 결과를 반환받는다.
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY 1, 2;
--==>>
/*
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 (null) 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 (null) 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 (null) 9400
(null) ANALYST 6000 -- 모든 부서 ANALYST 직종의 급여합 -- 추가
(null) CLERK 4150 -- 모든 부서 CLERK 직종의 급여합 -- 추가
(null) MANAGER 8275 -- 모든 부서 MANAGER 직종의 급여합 -- 추가
(null) PRESIDENT 5000 -- 모든 부서 PRESIDENT 직종의 급여합 -- 추가
(null) SALESMAN 5600 -- 모든 부서 SALESMAN 직종의 급여합 -- 추가
(null) (null) 29025
*/
■ 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 ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
--==>>
/*
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
*/
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 CUBE(DEPTNO, JOB)
ORDER BY 1,2;
--==>>
/*
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
전체부서 ANALYST 6000
전체부서 CLERK 7650
전체부서 MANAGER 8275
전체부서 PRESIDENT 5000
전체부서 SALESMAN 10800
전체부서 전체직종 37725
*/
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), (JOB), ())
ORDER BY 1,2;
--==>> CUBE()를 사용한 결과와 같은 조회 결과 반환
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() 을 사용한 결과와 같은 조회 결과 반환
■ 문제풀이 5
--○ TBL_EMP 테이블을 대상으로
-- 입사년도별 인원수를 조회한다.
-- 내가 했어용~ 근데 이렇게 간단한데 이걸 의도한게 맞을까,,,?
SELECT T.입사년도
, COUNT(*) "인원수"
FROM
(
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
FROM TBL_EMP
)T
GROUP BY T.입사년도
ORDER BY T.입사년도;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
*/
-- 같이~!
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY EXTRACT(YEAR FROM HIREDATE)
ORDER BY 1;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
*/
SELECT TO_CHAR(HIREDATE, 'YYYY') "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY')
ORDER BY 1;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
*/
SELECT EXTRACT(YEAR FROM HIREDATE) "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY TO_CHAR(HIREDATE, 'YYYY')
ORDER BY 1;
--==>> 에러 발생
-- (ORA-00979: not a GROUP BY expression)
SELECT TO_CHAR(HIREDATE, 'YYYY') "입사년도"
, COUNT(*) "인원수"
FROM TBL_EMP
GROUP BY EXTRACT(YEAR FROM HIREDATE)
ORDER BY 1;
--==>> 에러 발생
-- (ORA-00979: not a GROUP BY expression) -- 근데 사실은 SELECT 문에서 오류가 난거다! 왜냐? 파싱순서로 GROUP BY 먼저 처리하니까!
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;
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;
--==>>
/*
1980 1
1981 10
1982 1
1987 2
2023 5
전체 19
*/