오라클 데이터베이스에서 그룹함수를 이용한 조회 방법에 대해 안내해드리겠습니다. 그룹함수는 특정 컬럼을 기준으로 그룹화 하여 SUM, AVG, MIN, MAX, COUNT 등의 집계에 특화되어 있는데요, 그만큼 사용빈도도 높기에 실무에서 즉시 활용할 수 있도록 알아두시는 편이 도움이 많이 될 것입니다! 목차는 다음과 같이 진행해보겠습니다~ 😘
- GROUP BY
- GROUPING SETS
- ROLLUP
- CUBE
- GROUPING_ID
- GROUPING
Group By
Group By는 일반적으로 사용하는 가장 기본이 되는 그룹함수입니다. SUM 과 같은 그룹함수 'SELECT + GROUP BY' 절을 이용하시는 것은 익히 알고 계실 거예요!
'job', 'deptno' 그룹을 어떻게 활용할 수 있는지 다음 장부터 살펴보겠습니다. 'job'과 'deptno'로 그룹화 하여 각 직업별, 부서별 샐러리의 합계를 조회하는 쿼리는 다음과 같습니다.
SELECT JOB, DEPTNO, SUM (SAL)
FROM EMP
GROUP BY JOB, DEPTNO
ORDER BY 1, 2;
JOB DEPTNO SUM(SAL)
------------------ ---------- ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
9 rows selected.각 'job'별, 그리고 'deptno'별 'sal'의 합계를 확인할 수 있습니다.
Grouping Sets
Grouping Sets은 열거된 칼럼으로 가능한 그룹 함수를 각각 보여드립니다. 'job'과 'deptno'로 그룹을 시행하였으므로 'job'에 대한 그룹함수, 'deptno'에 대한 그룹함수를 각각 확인할 수 있습니다. grouping sets(job, deptno)은 'group by job' 더하기 'group by deptno'을 의미합니다.
SELECT JOB, DEPTNO, SUM (SAL)
FROM EMP
GROUP BY GROUPING SETS (JOB, DEPTNO)
ORDER BY 1, 2;
JOB DEPTNO SUM(SAL)
------------------ ---------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
10 8750
20 10875
30 9400
8 rows selected.쿼리의 결과를 보면 순서대로 'job'별 'sal'의 합계, 그리고 'deptno'별 합계를 확인할 수 있어요!
그리고 그루핑을 세트로 하였기에 두 조합의 합계는 일치하는 것도 확인할 수 있습니다. 'job'별 합계는 '6000+4150+8275+5000+5600'을 계산하여 29,025가 나오고요, 'deptno'별 합계는 '8750+10875+9400' 하여 29,025가 나온답니다. 😋 집합연산자나 뷰를 따로 이용하지 않고도 그루핑을 이용하여 간단하게 표현하는 경우도 있으니 참고해 주세요! 실제로 사용하지는 않더라도 '이런 것도 있구나' 하고 알고 가면 좋을 거예요.
Rollup
Rollup에 대하여 제가 열심히 그려봤는데 혹시 구분이 가시나요? 'rollup'은 문자 그대로 '말아 올리다' 또는 '감아 올리다'라는 느낌으로 해석할 수 있습니다. Group By Rollup(job, deptno)은 'job' 및 'deptno'에 대한 소계를 말아 올리며, 마지막으로 총계를 구하는 구조를 가지고 있답니다. 😉
SELECT JOB, DEPTNO, SUM (SAL)
FROM EMP
GROUP BY ROLLUP (JOB, DEPTNO)
ORDER BY 1, 2;
JOB DEPTNO SUM(SAL)
------------------ ---------- ----------
ANALYST 20 6000
ANALYST 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275
PRESIDENT 10 5000
PRESIDENT 5000
SALESMAN 30 5600
SALESMAN 5600
29025
15 rows selected.Rollup에서 'job'이 CLERK 인 직원들을 예로 들어볼게요. 부서코드가 '10'인 직원의 샐러리 합계는 '1300'이며, '20'인 직원은 '1900', 그리고 '30'인 직원은 '950'이고요, 'job'이 CLERK인 직원 모두의 합계는 '4150'인 것을 알 수 있습니다.
Rollup 총계 구하는 방법
Rollup을 활용하면 간단히 총계를 확인할 수도 있습니다. 'job'과 'deptno'를 괄호로 묶어 하나의 컬럼으로 롤업을 하면 총계만 볼 수 있답니다. 롤업 대상을 괄호로 묶어 하나로 표현하는 방법, 간단한 방법으로 총계를 쉽게 알 수 있다는 것을 잊지 마세요~ 이 기회에 알고 가시면 도움이 많이 될 거예요. 😘
SELECT JOB, DEPTNO, SUM (SAL)
FROM EMP
GROUP BY ROLLUP ((JOB, DEPTNO))
ORDER BY 1, 2;
JOB DEPTNO SUM(SAL)
------------------ ---------- ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
29025
10 rows selected.롤업을 활용하여 총계 29,025를 바로 확인할 수 있습니다.
Rollup 소계 (subtotal)
앞서 ROLLUP(())을 통한 총계를 확인하는 방법을 소개했는데요, JOB, ROLLUP (DEPTNO) 이런 식으로 사용하면 소계만 조회할 수도 있습니다.
SELECT JOB, DEPTNO, SUM (SAL)
FROM EMP
GROUP BY JOB, ROLLUP (DEPTNO)
ORDER BY 1, 2;
JOB DEPTNO SUM(SAL)
------------------ ---------- ----------
ANALYST 20 6000
ANALYST 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275
PRESIDENT 10 5000
PRESIDENT 5000
SALESMAN 30 5600
SALESMAN 5600
14 rows selected.ROLLUP으로 말아올리는 컬럼을 'deptno' 하나로 지정하여 소계만 구해본 예제입니다.
Cube
'cube'는 '정육면체', '입방체', '세제곱근' 등을 뜻하는 단어지요? Cube 함수는 그룹에 나열된 소계 및 총계를 산출합니다. 'job'과 'deptno'로 큐브를 진행하여 각각의 소계를 확인할 수 있답니다.
JOB DEPTNO SUM(SAL)
------------------ ---------- ----------
ANALYST 20 6000
ANALYST 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275
PRESIDENT 10 5000
PRESIDENT 5000
SALESMAN 30 5600
SALESMAN 5600
10 8750
20 10875
30 9400
29025
18 rows selected.'job'과 'deptno'를 그룹하여 표현하면 각 직업별, 부서별 합계를 확인할 수 있고, 마지막 총계에서 모든 'job'의 합계를 확인할 수 있는데요, 더 나아가 "그러면 각 'deptno'별 합계는 얼마냐?" 하는 궁금증이 있기도 하겠지요? 이에 대한 결과가 cube 함수랍니다. 그루핑 셋을 확장한 개념으로 보아도 돼요~
Grouping_Id , Grouping
pseudo column, 수도(슈도) 컬럼, 의사(擬似, 가짜) 컬럼(비길 의 + 닮을 사) 이라고 들어보셨나요?
grouping_id, grouping 컬럼은 그룹함수에서 제공하는 가상의 컬럼입니다. 소계, 총계 부분에서 'job'과 'deptno'가 'null'로 처리가 되는데요, 이 부분이 실제로 데이터가 'null'인 건지, 그룹함수의 소계에 의한 'null'인지 구분하기 위한 컬럼이랍니다.
- 'grouping'은 매개변수를 1개로 하며 해당 컬럼이 그룹함수여부인지를 0과 1로 확인합니다. 1이면 그룹함수에 의한 수도컬럼인 것을 뜻합니다.
- 'grouping_id'는 복수의 매개변수가 가능하며 해당 컬럼조합의 그룹함수 아이디를 확인합니다. 수도컬럼이 아니면 0 값을 가집니다.
SELECT JOB,
DEPTNO,
SUM (SAL),
GROUPING_ID (JOB, DEPTNO),
GROUPING (JOB),
GROUPING (DEPTNO)
FROM EMP
GROUP BY ROLLUP (JOB, DEPTNO)
ORDER BY 1, 2;
JOB DEPTNO SUM(SAL) GROUPING_ID(JOB,DEPTNO) GROUPING(JOB) GROUPING(DEPTNO)
------------------ ---------- ---------- ----------------------- ------------- ----------------
ANALYST 20 6000 0 0 0
ANALYST 6000 1 0 1
CLERK 10 1300 0 0 0
CLERK 20 1900 0 0 0
CLERK 30 950 0 0 0
CLERK 4150 1 0 1
MANAGER 10 2450 0 0 0
MANAGER 20 2975 0 0 0
MANAGER 30 2850 0 0 0
MANAGER 8275 1 0 1
PRESIDENT 10 5000 0 0 0
PRESIDENT 5000 1 0 1
SALESMAN 30 5600 0 0 0
SALESMAN 5600 1 0 1
29025 3 1 1
15 rows selected.'job' 별 소계는 그루핑 아이디가 1, 총계는 3인 것을 확인할 수 있습니다.
SELECT JOB,
DEPTNO,
SUM (SAL),
GROUPING_ID (JOB, DEPTNO),
GROUPING (JOB),
GROUPING (DEPTNO)
FROM EMP
GROUP BY CUBE (JOB, DEPTNO)
ORDER BY 1, 2;
JOB DEPTNO SUM(SAL) GROUPING_ID(JOB,DEPTNO) GROUPING(JOB) GROUPING(DEPTNO)
------------------ ---------- ---------- ----------------------- ------------- ----------------
ANALYST 20 6000 0 0 0
ANALYST 6000 1 0 1
CLERK 10 1300 0 0 0
CLERK 20 1900 0 0 0
CLERK 30 950 0 0 0
CLERK 4150 1 0 1
MANAGER 10 2450 0 0 0
MANAGER 20 2975 0 0 0
MANAGER 30 2850 0 0 0
MANAGER 8275 1 0 1
PRESIDENT 10 5000 0 0 0
PRESIDENT 5000 1 0 1
SALESMAN 30 5600 0 0 0
SALESMAN 5600 1 0 1
10 8750 2 1 0
20 10875 2 1 0
30 9400 2 1 0
29025 3 1 1
18 rows selected.큐브로 설정한 값인데요, 'deptno'에 의한 GROUPING_ID(JOB,DEPTNO) 수도 컬럼의 값이 2인 것을 확인할 수 있답니다. 롤업의 총계 수도컬럼이 3이고, 큐브의 총계 수도컬럼이 3인 것을 알 수 있죠? 아마 오라클 내부적으로 큐브를 먼저 구하거나, 롤업과 큐브의 조인을 염두에 두어 값을 맞추어 놓은 것으로 추측합니다.
ROLLUP 함수를 통해 소계, 총계 구해보기
롤업에 대한 글을 작성한 것을 깜빡하고 중복 게시물을 올리는 바람에 이 글에 통합하도록 하겠습니다. 한 번 더 학습하는 느낌으로 가보겠습니다. 😉
ROLLUP 함수에 대해 한 번 더 알아보도록 합시다~ 먼저, 롤업의 문법은 다음과 같습니다.
--[syntax]
--N개의 인수에 대해 다음과 같은 문법으로 롤업을 작성합니다.
ROLLUP(expr1, expr2, ..., expr(N-1), exprN)ROLLUP은 (n-1)표현의 소계와 총계를 구할 때 사용합니다. 어떻게 활용할 수 있는지 아래의 예시를 통해 보여드리겠습니다.
롤업으로 소계와 총계를 구해볼 테스트 테이블은 위와 같습니다.
직업별(job), 관리자별(mgr) 최고 급여(Max sal)를 확인해보겠습니다. 쿼리를 작성하면 GROUP BY JOB, MGR 을 통해 MAX(SAL) 을 통해 쉽게 확인할 수 있을 것입니다.
SELECT JOB,
MGR,
GROUPING (JOB),
GROUPING (MGR),
MAX (SAL)
FROM EMP
GROUP BY JOB, MGR
ORDER BY JOB,
GROUPING (JOB),
GROUPING (MGR),
MGR;
JOB MGR GROUPING(JOB) GROUPING(MGR) MAX(SAL)
------------------ ---------- ------------- ------------- ----------
ANALYST 7566 0 0 3000
CLERK 7698 0 0 950
CLERK 7782 0 0 1300
CLERK 7788 0 0 1100
CLERK 7902 0 0 800
MANAGER 7839 0 0 2975
PRESIDENT 0 0 5000
SALESMAN 7698 0 0 1600
8 rows selected.이때 그룹 집단별 소계, 총계를 확인하고 싶다면 ROLLUP 함수를 활용할 수 있는데, 어떻게 사용하는 것인지 다음과 같이 보여드리겠습니다. 보통 소계는 SUM 함수와 함께 표현하는데, 예제를 다르게 만들려다가 MAX함수를 사용하였습니다. 소계를 합계 정도로 생각하고 봐주세요. (다음부터는 SUM 함수로 예제를 만드는 것으로... 괜히 이렇게 한 것 같습니다. 😭)
SELECT JOB,
MGR,
GROUPING (JOB),
GROUPING (MGR),
MAX (SAL)
FROM EMP
GROUP BY ROLLUP (JOB, MGR)
ORDER BY JOB,
GROUPING (JOB),
GROUPING (MGR),
MGR;
JOB MGR GROUPING(JOB) GROUPING(MGR) MAX(SAL)
------------------ ---------- ------------- ------------- ----------
ANALYST 7566 0 0 3000
ANALYST 0 1 3000
CLERK 7698 0 0 950
CLERK 7782 0 0 1300
CLERK 7788 0 0 1100
CLERK 7902 0 0 800
CLERK 0 1 1300
MANAGER 7839 0 0 2975
MANAGER 0 1 2975
PRESIDENT 0 0 5000
PRESIDENT 0 1 5000
SALESMAN 7698 0 0 1600
SALESMAN 0 1 1600
1 1 5000
14 rows selected.상기의 자료에서 집단별 소계와 집계를 알고 싶을 때 ROLLUP 함수를 활용할 수 있습니다.
MGR 컬럼이 null인 값이 소계를 뜻합니다. (sub total)
JOB, MGR 컬럼이 모두 null인 값이 총계를 뜻합니다. (grand total)
JOB 컬럼이 'PRESIDENT'인 사장은 관리자(매니저)가 없으니, MGR 컬럼이 null인 로우가 2개가 보이는 것을 확인할 수 있습니다. 실제 소계에 해당하는 로우는 grouping(mgr)이 1인 행입니다. 이와 같이 ROLLUP 그룹함수에 대한 특정 결과 로우와 실제 로우를 구분하고자, 제공하는 grouping 함수 열을 슈도컬럼(의사컬럼/pseudo column)이라고 칭합니다.
JOB MGR GROUPING(JOB) GROUPING(MGR) MAX(SAL)
------------------ ---------- ------------- ------------- ----------
PRESIDENT 0 0 5000
PRESIDENT 0 1 5000
ROLLUP(expr1, expr2, ...) 구문은 매개변수를 괄호로 묶을 수 있으며, GROUP BY ROLLUP ( (JOB, MGR)) 을 통해 총계만 표시할 수도 있습니다. 그리고 별도로 작성하지는 않았으나, GROUP BY 에 해당하는 모든 열을 ROLLUP으로 묶을 필요는 없답니다! 필요에 따라 유동적으로 사용하면 돼요!
SELECT JOB,
MGR,
GROUPING (JOB),
GROUPING (MGR),
MAX (SAL)
FROM EMP
GROUP BY ROLLUP ( (JOB, MGR))
ORDER BY JOB,
GROUPING (JOB),
GROUPING (MGR),
MGR;
JOB MGR GROUPING(JOB) GROUPING(MGR) MAX(SAL)
------------------ ---------- ------------- ------------- ----------
ANALYST 7566 0 0 3000
CLERK 7698 0 0 950
CLERK 7782 0 0 1300
CLERK 7788 0 0 1100
CLERK 7902 0 0 800
MANAGER 7839 0 0 2975
PRESIDENT 0 0 5000
SALESMAN 7698 0 0 1600
1 1 5000
9 rows selected.