[SQLD] SQL 활용 2-5. 그룹 함수

5절. 그룹 함수

 

1. 데이터 분석 개요

데이터 분석 함수 종류

- AGGREGATE FUNCTION

- GROUP FUNCTION

- WINDOW FUNCTION

 

AGGREGATE FUNCTION

- COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수

 

GROUP FUNCTION

- 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성

- 소계/합계를 표시하기 위해 GROUPING 함수와 CASE 함수를 이용하면 쉽게 원하는 포맷의 보고서 작성

그룹 함수의 종류

 

ROLLUP

: 소그룹 간의 소계를 계산하는 함수

GROUP BY의 확장으로 병렬로 수행이 가능

-> 시간이나 지역처럼 계층적 분류를 포함하고 있는 데이터 집계에 적합

 

CUBE

: GROUP BY 항목들 간 다차원적인 소계를 계산하는 함수

결합 가능한 모든 값에 대하여 다차원적인 집계를 생성

-> 다양한 데이터를 얻는 장점이 있는 반면에, 시스템에 부하를 많이 주는 단점

 

GROUPING SETS

: 특정 항목에 대한 소계를 계산

원하는 부분의 소계만 손쉽게 추출 가능

 

GROUP FUNCTION 함수에서 정렬이 필요한 경우
ORDER BY 절에 정렬 칼럼을 명시

 

WINDOW FUNCTION

: 분석 함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)

---

2. ROLLUP 함수

Grouping Columns의 수가 N인 경우, N+1 Level의 Subtotal이 생성되며, ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의해야 한다.

 

단계별 데이터 출력

(1) 부서명과 업무명을 기준으로 사원수와 급여 합을 집계

STEP 1. 일반적인 GROUP BY 절 사용 SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB;

정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 칼럼을 표시한다.

 

(2) 부서, 업무별로 정렬해 부서명과 업무명을 기준으로 집계한 내용을 출력

STEP 1-2. GROUP BY 절 + ORDER BY 절 사용 SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB ORDER BY DNAME, JOB;

 

(3) 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용

STEP 2. ROLLUP 함수 사용 SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB);

GROUPING COLUMNS(DNAME, JOB)에 대하여

다음과 같은 추가 LEVEL의 집계가 생성

L1 - GROUP BY 수행시 생성되는 표준 집계 (9건) L2 - DNAME 별 모든 JOB의 SUBTOTAL (3건) L3 - GRAND TOTAL (마지막 행, 1건)

ROLLUP은 계층 간 집계에 대해서는 LEVEL 별 순서(L1→L2→L3)를 정렬
단, 계층 내 GROUP BY 수행시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않는다.

L1, L2, L3 계층 내 정렬을 위해서는 별도의 ORDER BY 절을 사용

 

(4) 부서, 업무별로 정렬한 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL 문장에 ROLLUP 함수를 사용해 출력 

STEP 2-2. ROLLUP 함수 + ORDER BY 절 사용 SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB) ORDER BY DNAME, JOB ;

 

(5) ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가

STEP 3. GROUPING 함수 사용 SELECT DNAME, GROUPING(DNAME), JOB, GROUPING(JOB), COUNT(*) "Total Empl", SUM(SAL) "Total Sal" FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB);

부서별, 업무별과 전체 집계를 표시한 레코드에서는 GROUPING 함수가 1
전체 합계를 나타내는 결과 라인에서는 부서별 GROUPING 함수와 업무별 GROUPING 함수가 둘 다 1

 

(6) ROLLUP 함수를 추가한 집계 보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수와 CASE 함수를 함께 사용한 SQL 문장 작성

STEP 4. GROUPING 함수 + CASE 사용 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.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, 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 ROLLUP (DNAME, JOB);

 ‘ALL JOBS’와 ‘ALL DEPARTMENTS’라는 사용자 정의 텍스트를 확인

 

(7) GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY DNAME, ROLLUP(JOB) 조건으로 변경

STEP 4-2. ROLLUP 함수 일부 사용 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.DEPTNO = EMP.DEPTNO GROUP BY DNAME, ROLLUP(JOB)

ALL DEPARTMENTS&ALL JOBS 줄만 계산이 되지 않았다.
ROLLUP이 JOB 칼럼에만 사용되었기 때문에 DNAME에 대한 집계는 필요하지 않기 때문

 

(8) 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));

ROLLUP 함수 사용시
JOB, MGR을 소계시 하나의 집합으로 간주하여 구분하지 않는다.
괄호로 묶은 JOB과 MGR의 경우 하나의 집합(JOB+MGR) 칼럼으로 간주하여 괄호 내 각 칼럼별 집계를 구하지 않는다.

---

3. CUBE 함수

: 결합 가능한 모든 값 에 대하여 다차원 집계를 생성

[ROLLUP과의 차이 : ROLLUP은 가능한 Subtotal(소계)만 생성]

 

• CUBE는 내부적으로는 Grouping Columns의 순서를 바꾸어 한 번의 Query를 추가 수행한다.
• ROLLUP에 비해 시스템의 연산 대상이 많다. 

-> Grand Total은 양쪽의 Query에서 모두 생성이 되므로 한 번의 Query에서는 제거되어야만 하기 때문에

 

따라서, CUBE 함수는 Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우 사용

 

표시된 인수들에 대한 계층별 집계를 구할 때 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다.

[ROLLUP의 경우 표시된 인수들 간에 계층구조이다.]

 

또한, 정렬이 필요한 경우 ORDER BY 절에 명시적으로 표시해야 한다.

 

(8) GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY CUBE (DNAME, JOB) 조건으로 변경해서 수행

STEP 5. CUBE 함수 이용 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.DEPTNO = EMP.DEPTNO GROUP BY CUBE (DNAME, JOB);

모든 경우의 수에 대하여 Subtotal을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면,

-> 2의 N승 LEVEL의 Subtotal을 생성하게 된다.

 

실행 결과에서 CUBE 함수 사용으로 ROLLUP 함수의 결과에다 업무별 집계까지 추가 가능

 

(9) UNION ALL은 Set Operation 내용으로, 여러 SQL 문장을 연결하는 역할을 할 수 있다.

[위 SQL은 첫 번째 SQL 모듈부터 차례대로 결과가 나오므로 위 CUBE SQL과 결과 데이터 는 같으나 행들의 정렬은 다를 수 있다. ]

 

STEP 5-2. UNION ALL 사용 SQL [STEP5와 동일한 결과] 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 사용 전, 반복 액세스하는 부분을 CUBE 사용 SQL에서는 한 번으로 줄일 수 있다.

-> 수행속도 및 자원 사용율 개선 가능

 

---

4. GROUPING SETS 함수

: 다양한 소계 집합 생성 -> GROUP BY SQL 반복하지 않고 결과를 쉽게 도출

 

표시된 인수들에 대한 개별 집계를 구할 수 있고,

계층구조인 ROLLUP과 달리 평등하므로 인수의 순서가 바뀌어도 결과는 같다.

 

정렬이 필요한 경우 ORDER BY절에 명시적으로 표시한다.

 

(1) 일반 그룹 함수를 이용한 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 ;

 

(2) 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과 결과 데이터는 같으나 행들의 정렬 순서는 다를 수 있다.

 

(3) GROUPING SETS을 사용하면서 순서 변경한 SQL

-부서별, 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 (JOB, DNAME);

GROUPING SETS 인수들은 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.

 

(4) 3개의 인수를 사용해 GROUPING SETS 이용한 SQL

- GROUPING SETS 함수를 이용해 부서-JOB-매니저 별 집계와, 부서-JOB 별 집계와, JOB-매니저 별 집계 출력

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));

GROUPING SETS 함수 사용시
괄호로 묶은 집합별로(괄호 내는 계층구조가 아닌 하나의 데이터로 간주함) 집계를 구할 수 있다