오라클 집계 함수 사용법: COUNT, SUM, AVG, MAX, MIN, 그리고 ROLLUP 함수
1. 오라클 집계 함수란 무엇인가?
집계 함수의 정의와 개념
오라클 집계 함수는 여러 행의 값을 하나의결과 값으로 요약하는데 사용합니다. 집계 함수는 데이터 분석과 보고에서 필수적인 도구로, 데이터베이스를 요약하고 통계를 낼때 주로 사용됩니다.
집계 함수의 중요성
집계 함수는 대량의 데이터를 효고적으로 분석하고 요약할수 있게 해줍니다. 이를 통해 의미 있는 정보를 추출하고, 데이터 기반의 의사 결정을 지원합니다.
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 함수
1) ROLLUP 함수란?
ROLLUP 함수는 GROUP BY 절에서 계층적 요약 데이터를 생성하는 데 사용됩니다. 이를 통해 소계 및 총계를 포함한 다양한 수준의 요약 데이터를 한 번에 얻을 수 있습니다.
2) ROLLUP 함수 사용법
ROLLUP은 GROUP BY 절에 포함되어 사용되며, 계층 구조에 따라 집계 결과를 생성합니다.
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 | 54500CROSS 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 - [▶ IT & 개발] - 오라클 날짜 계산 방법 총정리 : 특정 날짜 차이 계산