<목차> |
※ 자료출처: 데이터 자격검정 협회
3. 그룹 함수
그룹함수 개요
- 결산 개념의 업무를 가지는 원가나 판매 시스템의 결산 보고서를 작성하기 위해서는 SQL이 포함된 3GL으로 배치 프로그램을 작성하거나, 레벨별 집계를 위한 여러 단계의 SQL을 UNION, UNION ALL을 로 묶은 후 하나의 테이블을 여러 번 읽어 다시 재정렬하는 복잡한 단계를 거쳐야 했음.
- 그룹 함수를 사용하면 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트 작성 가능.
그룹함수에는
| - ROLLUP: 집계 함수를 제외하고, 소그룹 간의 소계를 계산 - CUBE : GROUP BY 항목들 간 다차원적인 소계를 계산 - GROUPING SETS: 특정 항목에 대한 소계를 계산 |
그룹함수의 종류
- ROLLUP
- GROUP BY의 확장된 형태로 사용하기가 쉬우며 병렬로 수행 가능
> 매우 효과적일 뿐 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합
- ROLLUP에 지정된 GROUPING COLUMN의 LIST는 SUBSTOTAL을 생성하기 위해 사용
- GROUPING COLUMN의 수를 N이라고 했을 때 N+1 Level의 Subtotal 생성
- ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀜. (인수의 순서에 주의)
- CUBE
- 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻을 수 있음.
> 시스템에 부하를 많이 주는 단점
- ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계 생성
- CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행해야 함.
- Grand Total은 양쪽의 Query에서 모두 생성이 되므로 한 번의 Query에서는 제거되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많음. > ROLLUP에 비해 시스템에 많은 부담
- 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP 과는 달리 평등한 관계 > 인수가 바뀌더라도 정렬 순서는 바뀔 수 있어도 결과는 같음.
- GROUPING SETS
- 원하는 부분의 소계만 손쉽게 추출
- GROUP BY SQL 문장을 여러 번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있음.
- 인수들 간에는 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같음
ROLLUP과 CUBE의 효과
■ STEP 1. 일반적인 GROUP BY 절 사용
[예제] 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL 문장 수행
SELECT DNAME, JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY DNAME, JOB;
※ 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼 표시
■ STEP 1-2. GROUP BY 절 + ORDER BY 절 사용
[예제] 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ORDER BY 절을 사용함으로써 부서, 업무별로 정렬
SELECT DNAME, JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY DNAME, JOB
ORDER BY DNAME, JOB;
■ STEP 2. ROLLUP 함수 사용
[예제] 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수 사용
SELECT DNAME, JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY ROLLUP (DNAME, JOB);
※ 실행 결과에서 2개의 GROUPING COLUMNS(DNAME, JOB)에 대하여 다음과 같은 추가 LEVEL의 집계가 생성된 것을 볼 수 있다.
L.1 - GROUP BY 수행시 생성되는 표준 집계 (9건)
L.2 - DNAME 별 모든 JOB의 SUBTOTAL (3건)
L.3 - GRAND TOTAL (마지막 행, 1건)
※ ROLLUP의 경우 계층 간 집계에 대해서는 LEVEL 별 순서(L1→L2→L3)를 정렬하지만, 계층 내 GROUP BY 수행 시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않음. (별도의 정렬을 위해서는 별도의 ORDER BY 절 사용)
■ STEP 2-2. ROLLUP 함수 + ORDER BY 절 사용
[예제] 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수 사용, 추가로 ORDER BY 절을 사용해서 부서, 업무별로 정렬
SELECT DNAME, JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY DNAME, JOB;
■ STEP 3. GROUPING 함수 사용
ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수 추가
- ROLLUP 이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1
- 그 외의 결과에는 GROUPING(EXPR) = 0 표시
GROUPING 함수와 CASE/DECODE를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있어, 보고서 작성 시 유용하게 사용
[예제] ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가된 SQL
SELECT DNAME, GROUPING(DNAME),
JOB, GROUPING(JOB),
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY ROLLUP (DNAME, JOB)
※ 부서별, 업무별과 전체 집계를 표시한 레코드에서는 GROUPING 함수가 1을 리턴.
전체 합계를 나타내는 결과 라인에서는 부서별 GROUPING 함수와 업무별 GROUPING 함수가 둘 다 1
■ STEP 4. GROUPING 함수 + CASE 사용
[예제] ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수와 CASE 함수를 함께 사용한 SQL 문장 작성
SELECT
CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY ROLLUP (DNAME, JOB);
<Oracle> - DECODE 함수를 사용해서 좀 더 간략하게 표현 가능
SELECT
DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY ROLLUP (DNAME, JOB);
※ 부서별과 전체 집계를 표시한 레코드에서 'All Jobs'와 'All Departments' 사용자 정의 텍스트 확인
■ STEP 4-2. ROLLUP 함수 사용
[예제] GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY DANME, ROLLUP(JOB) 조건으로 변경
SELECT
CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY DNAME, ROLLUP (JOB);
※ 결과는 마지막 All Departments & All Jobs 줄만 계산되지 않음. (ROLLUP 이 JOB 칼럼에만 사용되었기 때문에 DNAME에 대한 집계는 필요하지 않기 때문)
삭제된 레코드)
| All Departments All Jobs 14 29025 |
■ STEP 4-3. ROLLUP 함수 결합 칼럼 사용
[예제] JOB과 MGR는 하나의 집합으로 간주하고, 부서별, JOB&MGR에 대한 ROLLUP 결과 출력
SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, (JOB, MGR));
☞ JOB, MGR을 소계시 하나의 집합으로 간주하여 구분하지 않음.
■ STEP 5. CUBE 함수 이용
[예제] GROUP BY ROLLUP(DNAME, JOB) 조건에서 GROUP BY CUBE(DANME, JOB) 조건으로 변경해서 수행
SELECT
CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB,
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPNO = EMP.DEPT NO
GROUP BY CUBE (DNAME, JOB);
※ CUBE는 GROUPING COLUMNS 이 가질 수 있는 모든 경우의 수에 대하여 Subtotal을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면, 2의 N승 LEVEL의 Subtotal을 생성.
실행 결과에서 CUBE 함수 사용으로 ROLLUP 함수의 결과에다 업무별 집계까지 추가해서 출력할 수 있는데, ROLLUP 함수에 비해 업무별 집계를 표시한 5건의 레코드가 추가
(All Departments - CLERK, ANALYST, MANAGER, SALESMAN, PRESIDENT 별 집계가 5건 추가)
■ STEP 5-2. UNION ALL 사용 SQL
UNION ALL : Set Operation 내용으로, 여러 SQL 문장 연결하는 역할
[예제]
SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, JOB
UNION ALL
SELECT DNAME, 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME
UNION ALL
SELECT 'All Departments', JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB
UNION ALL
SELECT 'All Departments', 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO;
※ CUBE 함수를 사용하면 위의 SQL에서 EMP, DEPT 테이블을 네 번이나 반복 액세스하는 부분을 CUBE 사용 SQL에서는 한 번으로 줄일 수 있음. > 수행속도 및 자원 사용율을 개선 / 가독성 높아짐.
GROUPING SETS 함수 효과
■ 일반 그룹함수를 이용한 SQL
[예제] 일반 그룹함수를 이용하여 부서별, JOB별 인원수와 급여 산출
SELECT DNAME, 'All Jobs' JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME
UNION ALL
SELECT 'All Departments' DNAME, JOB, COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY JOB;
■ GROUPING SETS 사용 SQL
[예제] 일반 그룹함수를 GROUPING SETS 함수로 변경하여 부서별, JOB별 인원수와 급여합 산출.
SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB
COUNT(*) "Total Empl",
SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (DNAME, JOB);
※ GROUPING SETS 함수 사용시 UNION ALL을 사용한 일반 그룹함수를 사용한 SQL과 같은 결과를 얻을 수 있으며, 괄호로 묶은 집합 별로 집계 산출 가능.
■ GROUPING SETS 사용 SQL - 순서 변경
[예제] 일반 그룹함수를 GROUPING SETS 함수로 변경하여 부서별, JOB별 인원수와 급여합을 구하는데 GROUPING SETS의 인수들의 순서를 변경
SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB
COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (JOB, DNAME);
■ 3개의 인수를 이용한 GROUPING SETS 이용
[예제] 부서-JOB-매니저 별 집계와, 부서-JOB 별 집계와, JOB-매니저 별 집계를 GROUPING SETS 함수를 이용하여 산출
SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS
(((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR));
※ 실행결과에서 첫번째 10건의 데이터는 (DANME+JOB+MGR) 기준의 집계이며, 두 번째 8건의 데이터는 (JOB+MGR) 기준의 집계이며, 세 번째 9건의 데이터는 (DNAME+JOB) 기준의 집계임.
이전글
다음글
'자격증 > SQLD' 카테고리의 다른 글
| SQLD 2과목 SQL 활용 - TOP N 쿼리 (1) | 2024.01.25 |
|---|---|
| SQLD 2과목 SQL 활용 - 윈도우 함수 (2) | 2024.01.24 |
| SQLD 2과목 SQL 활용 - 집합연산자 (1) | 2024.01.16 |
| SQLD 2과목 SQL 활용 - 서브쿼리 (1) | 2024.01.15 |
| SQLD 2과목 SQL 기본 및 활용 - 표준 조인 (0) | 2024.01.14 |
