[SQLD] 25. 그룹함수(GROUP FUNCTION)

제 5절 그룹 함수(GROUP FUNCTION)

1. 데이터 분석 개요

데이터 분석을 위한 세가지 함수는 다음과 같다.

- AGGREGATE FUNCTION

- GROUP FUNCTION

- WINDOW FUNCTION

1) AGGREGATE FUNCTION

  GROUP AGGREGATE FUNCTION 이라고도 부르며, GROUP FUNCTION의 한 부분으로 분류할 수 있다. 앞서 포스팅했던 COUNT, SUN, AVG, MAX 와 같은 함수들이 집계함수에 속한다.

2) GROUP FUNCTION

  결산개념을 가지는 업무의 경우, 소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만드는 것이 중요 업무 중의 하나이다. 그런데 이러한 레벨별 집계를 위해서는 여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러 번 다시 읽어 재정렬하는 복잡한 단계를 거쳐야 했다.

  하지만, 그룹 함수를 사용한다면, 하나의 SQL로 테이블을 한 번만 읽어서 빠르게 원하는 리포트를 작성할 수 있다. 추가로, 소계 / 합계를 표시하기 위해 GROUPING 함수와 CASE 함수를 이용하면 쉽게 원하는 포맷의 보고서 작성도 가능하다. 그룹 함수로는 다음과 같은 것들이 있다.

- ROLLUP : 집계 함수를 제외하고 소그룹 간의 소계를 계산

- CUBE    : GROUP BY 항목 간 다차원적인 소계를 계산

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

3) WINDOW FUNCTION

  분석함수(ANALYTIC FUNCTION)나 순위 함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수는 데이터 웨어하우스에서 발전한 기능이며, 다음 절에서 배우도록 한다.

2. ROLLUP 함수

- 롤업함수에 지정된 그룹핑 컬럼의 리스트는 Subtotal(소개)을 생성하기 위해 사용

- GROUP BY ROLLUP (DNAME, JOB); DNAME과 JOB은 소개를 생성하기 위하여 사용됨

- GROUP BY의 확장된 형태로 사용

- 병렬로 수행이 가능하여 매우 효과적

- 그룹핑 컬럼 수(N) + 1 = 소계(N+1개)

- ROLLUP의 인수는 계층구조이므로 인수 순서가 바뀌면 수행결과도 바뀜

STEP 1. 일반적인 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;

실행 결과, DNAME과 JOB에 따른 정렬의 결과로 끼리끼리 그룹핑되어 데이터가 표시된다.

STEP.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;

실행 결과, 각 DNAME과 JOB에 따른 정렬의 결과 그룹핑되어 각 그룹핑 된 DNAME과 JOB에 따라 소계가 나타나게 된다. 즉, ROLLUP 함수에 따라 레벨의 집계가 생성된다.

L1 - GROUP BY 수행 시 생성되는 표준 집계 (9건)

L2 - DNAME 별 모든 JOB의 SUBTOTAL (3건)

L3 - GRAND TOTAL (마지막 행, 1건)

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

실행 결과, 그룹핑을 하였던 두 컬럼에는 0이라는 값이 차례대로 나오게 되며, 소계가 계산된 결과에 대해서는 1이라는 값이 넣어지게 된다. 

- ROLLUP이나 CUBE에 의해 소계가 계산된 결과에는 GROUPING(EXPR) = 1

- 그 외의 결과 GROUPING(EXPR) = 0 

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

이런 식으로 CASE를 사용할 경우 0과 1같은 가독성이 떨어지는 문자를 넣는 것 보다 의미있는 문자열을 넣음으로 인해 가독성을 높일 수 있다.

여기서 ROLLUP 함수 일부를 사용하게 되는 경우 결과는 마지막 ALL DEPARTEMENTS & ALL JOBS 줄만 계산되지 않는다. 즉, ROLLUP이 JOB 칼럼에만 사용되었기 때문에 DNAME에 대한 집계는 필요하지 않기 때문이다.

GROUP BY DNAME, ROLLUP(JOB);

또한, ROLLUP 함수 결합 칼럼을 사용하였을 경우, JOB과 MGR은 하나의 집합으로 간주하고, 부서별, JOB & MGR에 대한 ROLLUP 결과를 출력한다. 즉, 괄호로 묶은 JOB과 MGR의 경우 하나의 집합 칼럼으로 간주하여 괄호내 각 칼럼별 집계를 구하지 않는다. 

GROUP BY ROLLUP(DNAME, (JOB, MGR));

3. CUBE 함수

- 소계만 생성한 ROLLUP과는 달리 CUBE는 결합 가능한 모든 값에 대해 다차원 집계를 생성

- 그룹핑 컬럼의 순서를 바꾸어서 또 한번의 쿼리를 수행하므로 시스템 연산 대상이 많음, 시스템 부하

- 그룹핑 컬럼이 가질 수 있는 모든 컬럼에 대하여 소계를 생성해야 하는 경우 CUBE를 씀

- 표시된 인수들에 대한 계층별 집계를 구할 수 있음

- 계층구조인 ROLLUP과는 달리 , 평등한 관계이므로 인수의 순서가 바뀌어도 정렬은 다를 수 있으나 데이터의 결과는 같음

- 그룹핑 컬럼 수 N개, 2의 N승 레벨의 소계 생성

STEP 1. 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);

실행 결과, 가질 수 있는 모든 경우의 수에 대하여 소계를 생성하게 된다. 예를 들어 CLERK 소계, ANALYST 소계, MANAGER 소계, ... 등 이는 UNION ALL을 사용한 SQL문으로도 동일한 데이터를 추출할 수 있게끔 작성할 수 있으나 효율적이지 않으므로 때에따라 (모든 경우의 수에 대한 소계를 구할 때) CUBE를 사용하도록 한다. 

CUBE 함수를 사용하면서 가장 개선된 점은, 사용 전 SQL에서 EMP, DEPT 테이블을 몇번이나 반복 엑세스 하는 부분을 CUBE 사용으로 인해 한 번으로 줄일 수 있다는 점이다. 결과적으로 수행속도 및 자원사용율을 개선할 수 있으며, 문장도 짧아지고 가독성도 높아지게 된다. 

4. GROUPING SETS 함수

- 원하는 부분의 소계 집합을 만들 수 있음

- GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있음

- 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같음

- ORDER BY 절을 이용하여 정렬하는 작업을 권고

일반 그룹함수를 이용한 SQL

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;

 DNAME         JOB         Total     Empl Total Sal 

----------     -------     --------     ------ 

ACCOUNTING  All Jobs           3      8750 

RESEARCH     All Jobs           5     10875 

SALES         All Jobs           6      9400 

All Departments CLERK            4      4150 

All Departments SALESMAN       4       5600 

All Departments PRESIDENT       1       5000 

All Departments MANAGER        3       8275 

All Departments ANALYST         2        6000 

8개의 행이 선택되었다.

GROUPING SETS 사용 SQL (오라클)

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

DNAME          JOB Total     Empl Total Sal 

---------------- ---------- -------- ------- 

All Departments     CLERK         4     4150 

All Departments     SALESMAN    4     5600 

All Departments     PRESIDENT    1     5000 

All Departments     MANAGER     3     8275 

All Departments     ANALYST      2     6000 

ACCOUNTING      All Jobs       3     8750 

RESEARCH        All Jobs        5    10875

SALES            All Jobs        6     9400 

8개의 행이 선택되었다.

계층구조를 가지는 ROLLUP 함수와 반대로, GROUPING SETS 함수와 CUBE 함수는 인수들의 순서를 변경해도 데이터의 결과는 같다.

3개의 인수를 이용해 GROUPING SETS을 하였을 경우 괄호로 묶은 집합별로 집계를 구할 수 있으며 괄호 내는 계층구조가 아닌 하나의 데이터로 간주할 수 있다.

GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR));