[ORACLE]오라클 집계 함수: ROLLUP 포함 활용법 및 예제

오라클 집계 함수 사용법: COUNT, SUM, AVG, MAX, MIN, 그리고 ROLLUP 함수

---

1. 오라클 집계 함수란 무엇인가?

집계 함수의 정의와 개념

오라클 집계 함수는 여러 행의 값을 하나의결과 값으로 요약하는데 사용합니다. 집계 함수는 데이터 분석과 보고에서 필수적인 도구로, 데이터베이스를 요약하고 통계를 낼때 주로 사용됩니다. 

집계 함수의 중요성

집계 함수는 대량의 데이터를 효고적으로 분석하고 요약할수 있게 해줍니다. 이를 통해 의미 있는 정보를 추출하고, 데이터 기반의 의사 결정을 지원합니다. 

관련 글

2024.06.28 - [개발(Dev)/DB] - [ORACLE]오라클 SQL 날짜 계산 방법: 초보 개발자를 위한 완벽 가이드

 

---

2. 주요 오라클 집계 함수 소개 : COUNT, SUM, AVG, MAX, MIN

SUM 함수

SUM 함수는 지정된 컬럼의 합계를 계산합니다. NULL 값은 무시합니다.

-- SUM 함수 예제
SELECT SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEES_TB;

설명: EMPLOYEES_TB 테이블에서 SALARY 컬럼의 모든 값을 더한 총합을 반환합니다. SALARY 컬럼에 NULL 값이 포함되어 있는 경우, 해당 NULL 값은 무시되고 계산되지 않습니다.

 

AVG 함수

AVG 함수는 지정된 컬럼의 평균 값을 계산합니다. NULL 값은 무시됩니다.

-- AVG 함수 예제
SELECT AVG(SALARY) AS AVERAGE_SALARY
FROM EMPLOYEES_TB;

설명: EMPLOYEES_TB 테이블에서 SALARY 컬럼의 평균 값을 반환합니다. SALARY 컬럼에 NULL 값이 포함되어 있는 경우, 해당 NULL 값은 무시되고 계산되지 않습니다.

 

COUNT 함수

COUNT 함수는 지정된 컬럼의 행 개수를 계산합니다. NULL 값은 무시되지만, COUNT(*)는 NULL 값을 포함한 모든 행을 계산합니다.

-- COUNT 함수 예제
SELECT COUNT(EMPLOYEE_ID) AS TOTAL_EMPLOYEES
FROM EMPLOYEES_TB;

설명: EMPLOYEES_TB 테이블에서 EMPLOYEE_ID 컬럼의 행 수를 반환합니다. EMPLOYEE_ID 컬럼에 NULL 값이 포함되어 있는 경우, 해당 NULL 값은 무시되고 계산되지 않습니다.

 

MAX 함수

MAX 함수는 지정된 컬럼의 최대 값을 반환합니다. NULL 값은 무시됩니다.

-- MAX 함수 예제
SELECT MAX(SALARY) AS MAX_SALARY
FROM EMPLOYEES_TB;

설명: EMPLOYEES_TB 테이블에서 SALARY 컬럼의 최대 값을 반환합니다. SALARY 컬럼에 NULL 값이 포함되어 있는 경우, 해당 NULL 값은 무시되고 계산되지 않습니다.

 

MIN 함수

MIN 함수는 지정된 컬럼의 최소 값을 반환합니다. NULL 값은 무시됩니다.

-- MIN 함수 예제
SELECT MIN(SALARY) AS MIN_SALARY
FROM EMPLOYEES_TB;

설명: EMPLOYEES_TB 테이블에서 SALARY 컬럼의 최소 값을 반환합니다. SALARY 컬럼에 NULL 값이 포함되어 있는 경우, 해당 NULL 값은 무시되고 계산되지 않습니다.

 

---

3. 집계 함수 예제: 실전에서 사용하는 방법

• 전체 데이터 예시 (EMPLOYEES_TB)

EMPLOYEE_ID	NAME	DEPARTMENT_ID	SALARY
1	Alice	10	5000
2	Bob	20	7000
3	Charlie	10	6000
4	David	30	4000
5	Eve	20	8000

 

SUM 함수 예제

-- 예제: 각 부서의 총 급여를 계산
SELECT DEPARTMENT_ID
	 , SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEES_TB
GROUP BY DEPARTMENT_ID;

-- 출력 예시
DEPARTMENT_ID | TOTAL_SALARY
--------------|--------------
10            | 15000
20            | 6000

 

AVG 함수 예제

-- 예제: 각 부서의 평균 급여를 계산
SELECT DEPARTMENT_ID
	 , AVG(SALARY) AS AVERAGE_SALARY
FROM EMPLOYEES_TB
GROUP BY DEPARTMENT_ID;

-- 출력 예시
DEPARTMENT_ID | AVERAGE_SALARY
--------------|----------------
10            | 5000
20            | 6000

 

COUNT 함수 예제

-- 예제: 각 부서의 직원 수를 계산
SELECT DEPARTMENT_ID
	 , COUNT(EMPLOYEE_ID) AS TOTAL_EMPLOYEES
FROM EMPLOYEES_TB
GROUP BY DEPARTMENT_ID;

-- 출력 예시
DEPARTMENT_ID | TOTAL_EMPLOYEES
--------------|-----------------
10            | 3
20            | 2

 

MAX 함수 예제

-- 예제: 각 부서의 최대 급여를 계산
SELECT DEPARTMENT_ID
	 , MAX(SALARY) AS MAX_SALARY
FROM EMPLOYEES_TB
GROUP BY DEPARTMENT_ID;

-- 출력 예시
DEPARTMENT_ID | MAX_SALARY
--------------|-----------
10            | 5500
20            | 6000

 

MIN 함수 예제

-- 예제: 각 부서의 최소 급여를 계산
SELECT DEPARTMENT_ID
	 , MIN(SALARY) AS MIN_SALARY
FROM EMPLOYEES_TB
GROUP BY DEPARTMENT_ID;

-- 출력 예시
DEPARTMENT_ID | MIN_SALARY
--------------|-----------
10            | 4500
20            | 6000

 

---

4. GROUP BY와 집계 함수

GROUP BY 절의 사용법

GROUP BY 절은 데이터를 특정 컬럼을 기준으로 그룹화하여 요약된 결과를 출력하는 데 사용됩니다.

 

GROUP BY와 집계 함수의 결합

집계 함수와 GROUP BY 절을 결합하여, 각 그룹별로 요약된 값을 계산할 수 있습니다.

 

HAVING 절과의 조합

HAVING 절은 GROUP BY 절로 그룹화된 결과에 대해 조건을 적용하는 데 사용됩니다. WHERE 절과 달리, HAVING 절은 그룹화된 데이터에 조건을 걸 수 있습니다.

-- 예제: 급여가 5000 이상인 부서의 총 급여를 계산
SELECT DEPARTMENT_ID
	 , SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEES_TB
GROUP BY DEPARTMENT_ID
HAVING SUM(SALARY) >= 10000;

-- 출력 예시
DEPARTMENT_ID | TOTAL_SALARY
--------------|--------------
10            | 15000
20            | 6000

설명: 이 쿼리는 DEPARTMENT_ID별로 그룹화한 후, 총 급여가 10000 이상인 그룹만 출력합니다.

 

---

5. ROLLUP 함수

5.1 ROLLUP 함수란?

ROLLUP 함수는 GROUP BY 절에서 계층적 요약 데이터를 생성하는 데 사용됩니다. 이를 통해 소계 및 총계를 포함한 다양한 수준의 요약 데이터를 한 번에 얻을 수 있습니다.

 

5.2 ROLLUP 함수 사용법

ROLLUP은 GROUP BY 절에 포함되어 사용되며, 계층 구조에 따라 집계 결과를 생성합니다.

 

5.3 ROLLUP 함수 예제

테이블 스키마 및 데이터

• EMPLOYEES_TB

EMPLOYEE_ID	NAME	DEPARTMENT_ID	SALARY	HIRED_DATE
1	John Smith	10	5000	2021-01-15
2	Jane Doe	20	6000	2020-03-12
3	Bob Johnson	10	5500	2019-06-23
4	Alice Davis	20	NULL	2018-11-02
5	Steve Brown	10	4500	2022-08-05

 

• DEPARTMENTS_TB

DEPARTMENT_ID		DEPARTMENT_NAME
10		Sales
20		HR

 

• PROJECTS_TB

PROJECT_ID	PROJECT_NAME		DEPARTMENT_ID
1	Project A		10
2	Project B		20
3	Project C		10

 

예제 1: 부서별, 프로젝트별 및 고용 연도별 총 급여 계산

SELECT D.DEPARTMENT_NAME
	 , P.PROJECT_NAME
	 , SUM(E.SALARY) AS TOTAL_SALARY
	 , AVG(E.SALARY) AS AVERAGE_SALARY
FROM EMPLOYEES_TB E
JOIN DEPARTMENTS_TB D 
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN PROJECTS_TB P 
	ON D.DEPARTMENT_ID = P.DEPARTMENT_ID
GROUP BY ROLLUP(D.DEPARTMENT_NAME, P.PROJECT_NAME);

-- 출력 예시
DEPARTMENT_NAME | PROJECT_NAME | TOTAL_SALARY | AVERAGE_SALARY
----------------|--------------|--------------|----------------
Sales           | Project A    | 5000         | 5000
Sales           | Project C    | 10000        | 5000
Sales           | NULL         | 15000        | 5000
HR              | Project B    | 6000         | 6000
HR              | NULL         | 6000         | 6000
NULL            | NULL         | 21000        | 5250

 

예제 2: 각 부서의 고용 연도별 총 급여 계산

SELECT D.DEPARTMENT_NAME
	 , EXTRACT(YEAR FROM E.HIRED_DATE) AS HIRE_YEAR
	 , SUM(E.SALARY) AS TOTAL_SALARY
FROM EMPLOYEES_TB E
JOIN DEPARTMENTS_TB D 
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY ROLLUP(D.DEPARTMENT_NAME, EXTRACT(YEAR FROM E.HIRED_DATE));


-- 출력 예시
DEPARTMENT_NAME | HIRE_YEAR | TOTAL_SALARY
----------------|-----------|--------------
Sales           | 2021      | 5000
Sales           | 2019      | 5500
Sales           | 2022      | 4500
Sales           | NULL      | 15000
HR              | 2020      | 6000
HR              | 2018      | NULL
HR              | NULL      | 6000
NULL            | NULL      | 21000

 

예제 3: 각 부서의 최대 급여와 최소 급여 계산

SELECT D.DEPARTMENT_NAME
	 , MAX(E.SALARY) AS MAX_SALARY
	 , MIN(E.SALARY) AS MIN_SALARY
FROM EMPLOYEES_TB E
JOIN DEPARTMENTS_TB D 
	ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME;

-- 출력 예시
DEPARTMENT_NAME | MAX_SALARY | MIN_SALARY
----------------|------------|------------
Sales           | 5500       | 4500
HR              | 6000       | 6000

 

---

6. 심화 예제: 집계 함수와 ROLLUP을 활용한 4개 테이블 데이터 분석

문제 설명

네 개의 테이블 EMPLOYEES_TB, DEPARTMENTS_TB, GENDERS_TB, EMPLOYMENT_TYPES_TB가 있습니다. 각 부서에 속한 직원 수와 해당 부서의 총 급여, 그리고 각 부서별 성별 및 고용 형태(정규직/비정규직) 정보를 계산하여 DEPARTMENTS_TB의 각 행에 추가된 결과를 반환합니다. 성별 및 고용 형태 데이터가 없는 경우에도 해당 행을 포함하고, 각각의 항목의 소계와 합계를 표시합니다.

테이블 스키마 및 데이터 

EMPLOYEES_TB

COMPANY_CD	EMPLOYEE_ID	NAME	DEPARTMENT_ID	SALARY	GENDER_ID	EMPLOYMENT_TYPE	HIRED_DATE
WIN	1	김철수	10	5000	1	100	2021-01-15
WIN	2	이영희	20	6000	2	200	2020-03-12
WIN	3	박민수	10	5500	1	100	2019-06-23
WIN	4	최지현	20	NULL	2	200	2018-11-02
WIN	5	정해인	10	4500	1	200	2022-08-05
WIN	6	김민정	30	7000	2	100	2019-04-10
WIN	7	김태희	30	8000	1	100	2020-12-17
WIN	8	이수진	10	4800	2	100	2021-07-22
WIN	9	박서준	20	6200	1	100	2020-01-30
WIN	10	한지민	30	7500	2	200	2021-03-19

 

DEPARTMENTS_TB

COMPANY_CD	DEPARTMENT_ID	DEPARTMENT_NAME
WIN	10	Sales
WIN	20	HR
WIN	30	IT

 

GENDERS_TB

COMPANY_CD	GENDER_ID	GENDER_NAME	GENDER_NAME_KR
WIN	1	Male	남성
WIN	2	Female	여성

 

EMPLOYMENT_TYPES_TB

COMPANY_CD	EMPLOYMENT_TYPE	EMPLOYMENT_NAME
WIN	100	정규직
WIN	200	비정규직

 

SQL 쿼리

DEPARTMENTS_TB의 각 부서에 대해 EMPLOYEES_TB에서 해당 부서에 속한 직원 수와 총 급여, 그리고 각 부서별 성별 정보를 포함하는 결과를 반환합니다. 성별 데이터가 없는 경우에도 해당 성별 행을 포함하고, 각각의 항목의 소계와 합계를 표시합니다.

WITH DEPT_GENDER_EMPLOY AS (
    SELECT D.COMPANY_CD
		 , D.DEPARTMENT_ID
		 , D.DEPARTMENT_NAME
		 , G.GENDER_NAME
		 , G.GENDER_NAME_KR
		 , T.EMPLOYMENT_TYPE
		 , T.EMPLOYMENT_NAME
    FROM DEPARTMENTS_TB D
    CROSS JOIN GENDERS_TB G
    CROSS JOIN EMPLOYMENT_TYPES_TB T
    WHERE D.COMPANY_CD = G.COMPANY_CD
    AND G.COMPANY_CD = T.COMPANY_CD
    AND D.COMPANY_CD = 'WIN'
)
SELECT 
    CASE 
        WHEN GROUPING(DG.DEPARTMENT_ID) = 1 THEN NULL
        ELSE DG.DEPARTMENT_ID
    END AS DEPARTMENT_ID,
    CASE 
        WHEN GROUPING(DG.DEPARTMENT_ID) = 1 THEN '합계'
        WHEN GROUPING(DG.GENDER_NAME) = 1 THEN '소계'
        ELSE DG.DEPARTMENT_NAME
    END AS DEPARTMENT_NAME,
    CASE 
        WHEN GROUPING(DG.GENDER_NAME) = 1 THEN NULL
        ELSE DG.GENDER_NAME
    END AS GENDER_NAME,
    CASE 
        WHEN GROUPING(DG.GENDER_NAME_KR) = 1 THEN NULL
        ELSE DG.GENDER_NAME_KR
    END AS GENDER_NAME_KR,
    CASE 
        WHEN GROUPING(DG.EMPLOYMENT_TYPE) = 1 THEN NULL
        ELSE DG.EMPLOYMENT_TYPE
    END AS EMPLOYMENT_TYPE,
    CASE 
        WHEN GROUPING(DG.EMPLOYMENT_NAME) = 1 THEN NULL
        ELSE DG.EMPLOYMENT_NAME
    END AS EMPLOYMENT_NAME,
    COUNT(E.EMPLOYEE_ID) AS EMPLOYEE_COUNT,
    SUM(E.SALARY) AS TOTAL_SALARY
FROM DEPT_GENDER_EMPLOY DG
LEFT JOIN EMPLOYEES_TB E 
    ON  DG.DEPARTMENT_ID   = E.DEPARTMENT_ID 
    AND DG.GENDER_NAME     = (SELECT GENDER_NAME FROM GENDERS_TB WHERE GENDER_ID = E.GENDER_ID AND COMPANY_CD = 'WIN')
    AND DG.EMPLOYMENT_TYPE = E.EMPLOYMENT_TYPE
    AND E.COMPANY_CD       = 'WIN'
GROUP BY ROLLUP(DG.DEPARTMENT_ID, DG.DEPARTMENT_NAME, DG.GENDER_NAME, DG.GENDER_NAME_KR, DG.EMPLOYMENT_TYPE, DG.EMPLOYMENT_NAME)
ORDER BY COALESCE(DG.DEPARTMENT_ID, 9999), DG.GENDER_NAME, DG.EMPLOYMENT_TYPE;

-- 출력 예시
DEPARTMENT_ID | DEPARTMENT_NAME | GENDER_NAME | GENDER_NAME_KR | EMPLOYMENT_TYPE | EMPLOYMENT_NAME | EMPLOYEE_COUNT | TOTAL_SALARY
--------------|-----------------|-------------|----------------|-----------------|-----------------|----------------|--------------
10            | Sales           | Female      | 여성           | 100             | 정규직          | 1              | 4800
10            | Sales           | Female      | 여성           | 200             | 비정규직        | 0              | NULL
10            | Sales           | Male        | 남성           | 100             | 정규직          | 2              | 10500
10            | Sales           | Male        | 남성           | 200             | 비정규직        | 1              | 4500
NULL          | 소계            | NULL        | NULL           | NULL            | NULL            | 4              | 19800
20            | HR              | Female      | 여성           | 100             | 정규직          | 0              | NULL
20            | HR              | Female      | 여성           | 200             | 비정규직        | 2              | 6000
20            | HR              | Male        | 남성           | 100             | 정규직          | 1              | 6200
20            | HR              | Male        | 남성           | 200             | 비정규직        | 0              | NULL
NULL          | 소계            | NULL        | NULL           | NULL            | NULL            | 3              | 12200
30            | IT              | Female      | 여성           | 100             | 정규직          | 1              | 7000
30            | IT              | Female      | 여성           | 200             | 비정규직        | 1              | 7500
30            | IT              | Male        | 남성           | 100             | 정규직          | 1              | 8000
30            | IT              | Male        | 남성           | 200             | 비정규직        | 0              | NULL
NULL          | 소계            | NULL        | NULL           | NULL            | NULL            | 3              | 22500
NULL          | 합계            | NULL        | NULL           | NULL            | NULL            | 10             | 54500

CROSS JOIN 사용 이유와 설명

CROSS JOIN은 두 테이블의 모든 조합을 생성합니다. 이 쿼리에서는 각 부서와 모든 성별 및 고용 형태의 조합을 만들기 위해 CROSS JOIN을 사용합니다.

 

CROSS JOIN  사용 이유:

• 각 부서별로 모든 성별 및 고용 형태를 포함하는 결과를 생성하여, 직원이 없는 경우에도 해당 조합을 포함합니다.
• 쿼리에서 모든 가능한 조합을 생성하여 LEFT JOIN과 함께 사용하여 데이터가 없는 경우에도 결과에 포함될 수 있도록 합니다.

 

CROSS JOIN 사용 시기:

• 두 테이블의 모든 가능한 조합을 만들어야 할 때 사용합니다.
• 일반적으로 각 행을 모든 다른 행과 조합해야 할 때 사용합니다.

 

CASE WHEN에서 GROUPING 함수 사용 필요성

GROUPING 함수는 ROLLUP 또는 CUBE와 함께 사용되어 집계 수준을 식별합니다.

 

GROUPING을 사용하는 이유:

• GROUPING 함수는 집계 결과에서 소계 및 합계 행을 식별하는 데 사용됩니다.
• CASE 문에서 GROUPING 함수를 사용하여 집계된 행과 실제 데이터 행을 구분할 수 있습니다.
• 소계 및 합계 행에서 특정 컬럼 값을 NULL로 설정하거나 특정 명칭을 부여하는 데 유용합니다.

CASE 
    WHEN GROUPING(column) = 1 THEN '소계'
    ELSE column
END

 

GROUPING을 사용해야 하는 이유:

• ROLLUP 결과에서 소계 및 합계 행을 구분하지 않으면, 결과의 의미를 명확히 알 수 없습니다.
• GROUPING 함수를 사용하지 않으면 소계 및 합계 행을 정확하게 식별하고 처리하기 어렵습니다.

 

GROUP BY ROLLUP 예시 설명

GROUP BY ROLLUP(DG.DEPARTMENT_ID, DG.DEPARTMENT_NAME, DG.GENDER_NAME, DG.GENDER_NAME_KR, DG.EMPLOYMENT_TYPE, DG.EMPLOYMENT_NAME)

이 쿼리는 DEPARTMENT_ID, DEPARTMENT_NAME, GENDER_NAME, GENDER_NAME_KR, EMPLOYMENT_TYPE, EMPLOYMENT_NAME에 대해 그룹화하고, 각 차원의 소계 및 합계를 생성합니다.

 

ROLLUP의 변경 예시:

GROUP BY ROLLUP((DG.DEPARTMENT_ID, DG.DEPARTMENT_NAME), DG.GENDER_NAME, DG.GENDER_NAME_KR, DG.EMPLOYMENT_TYPE, DG.EMPLOYMENT_NAME)

이 쿼리는 (DEPARTMENT_ID, DEPARTMENT_NAME)를 하나의 그룹으로 간주하고, 나머지 컬럼들에 대해 소계를 생성합니다.

 

사용 시기:

• (DEPARTMENT_ID, DEPARTMENT_NAME)를 하나의 차원으로 간주하여 소계를 생성하고 싶을 때 사용합니다.
• 특정 컬럼들 간의 관계를 유지하면서 그룹화하고 싶을 때 유용합니다.

 

예시:

• 부서별로 성별 및 고용 형태에 따른 소계를 계산하고, 부서 전체의 합계를 계산하고자 할 때 유용합니다.

 

사용 이유:

• 여러 컬럼을 하나의 그룹으로 묶어 소계를 계산해야 할 때 사용합니다.
• 데이터의 계층 구조를 유지하면서 그룹화를 더욱 명확하게 정의할 수 있습니다.

 

---

관련 글

2024.06.28 - [개발(Dev)/DB] - [ORACLE]오라클 SQL 날짜 계산 방법: 초보 개발자를 위한 완벽 가이드

 

 

여기까지 읽어주셔서 진심으로 감사드립니다.
이 글이 마음에 드셨다면, 우측 아래 하트(공감)를 눌러 응원의 표시를 부탁드려요.
여러분의 소중한 관심과 사랑이 큰 힘이 됩니다. 감사합니다!