2.5 그룹 함수 (GROUP FUNCTION)

Study/SQL전문가가이드

2.5 그룹 함수 (GROUP FUNCTION)

슐슈리 2015. 11. 19. 11:47

1. 데이터 분석 개요

- AGGREGATE FUNCTION

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

- GROUP FUNCTION

: 집계 함수를 제외하고 소그룹 간의 소계를 계산하는 ROLLUP, GROUP BY 항목들 간 다차원적인 소계를 계산하는 CUBE, 특정 항목에 대한 소계를 계산하는 GROUPING SEST

ROLLUP은 GROUP BY의 확장된 형태로 사용하기가 쉬우며 병렬로 수행이 가능하기 때문에 매우 효과적일 뿐만 아니라 시간 및 지역처럼 계층적 분류를 포함하고 있는 데이터의 집계에 적합

CUBE는 결합 가능한 모든 값에 대하여 다차원적인 집계를 생성하게 되므로 ROLLUP에 비해 다양한 데이터를 얻는 장점이 있는 반면에, 시스템에 부하를 많이 주는 단점이 있다.

GROUPING SETS는 원하는 부분의 소계만 손쉽게 추출할 수 있는 장점이 있다.

- WINDOW FUNCTION

: 분석함수(ANALYTIC FUNCTION)나 순위함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수는 데이터 웨어하우스에서 발전한 기능

2. ROLLUP 함수

: ROLLUP에 지정된 GROUPING COLUMNS의 LIST는 SUBTOTAL을 생성하기 위해 사용되며 GROUPING COLUMNS의 수를 N이라고 했을 때 N+1의 LEVEL의 SUBTOTAL이 생성됨

ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에 주의

STEP 1. 일반적인 GROUP BY 절 사용

예제] 부서명과 업무명을 기준으로 사원수와 급여 합을 집계한 일반적인 GROUP BY SQL문장을 수행한다.

SELECT DNAME, JOB, COUNT(*), SUM(SAL)

FROM EMP, DEPT

WHERE DEPT.DEPTNO=EMP.DEPTNO

GROUP BY DNAME, JOB;

STEP 1-2. GROUP BY 절 + ORDER BY 절 사용

예제] 부서명과 업무명을 기준으로 집계한 일반적인 GROUP BY SQL문장에 ORDER BY 절을 사용함으로써 부서, 업무별로 정렬

SELECT DNAME, JOB, COUNT(*), SUM(SAL)

FROM EMP, DEPT

WHERE DEPT.DEPTNO=EMP.DEPTNO

GROUP BY DNAME, JOB

ORDER BY DNAME, JOB;

STEP 2. ROLLUP 함수 사용

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

SELECT DNAME, JOB, COUNT(*), SUM(SAL)

FROM EMP, DEPT

WHERE DEPT.DEPTNO=EMP.DEPTNO

GROUP BY ROLLUP (DNAME, JOB);

STEP 2-2. ROLLUP 함수 + ORDER BY 절 사용

SELECT DNAME, JOB, COUNT(*), SUM(SAL)
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY DNAME, JOB

STEP 3. GROUPING 함수 사용

- ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR)=1이 표시되고

- 그 외의 결과에는 GROUPING(EXPR)=0이 표시된다

예제] ROLLUP 함수를 추가한 집계보고서에서 집계 레코드를 구분할 수 있는 GROUPING 함수가 추가된 SQL문장

SELECT DNAME, GROUPING(DNAME),

JOB, GROUPING(JOB),

COUNT(*), SUM(SAL)

FROM EMP, DEPT

WHERE DEPT.DEPTNO=EMP.DEPTNO

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 Emp", SUM(SAL) "Total Sal"

FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)

DECODE사용]

SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,

DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,

COUNT(*) "Total Emp",

SUM(SAL) "Total Sal"

FROM EMP, DEPT

WHERE DEPT.DEPTNO=EMP.EMPNO

GROUP BY ROLLUP (DNAME, JOB);

STEP 4-2. ROLLUP 함수 일부 사용

예제] GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY DNAME, 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 Emp", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB)

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

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

3. CUBE 함수

: ROLLUP에서는 단지 가능한 SUBTOTAL만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성.

CUBE를 사용할 경우에는 내부적으로는 GROUPING COLUMNS의 순서를 바꾸어도 또 한번의 query를 추가 수행해야 한다. 뿐만 아니라 Grand Total은 양쪽의 query 에서 모두 생성이 되므로 한 번의 query에서는 제거 되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많다.

이처럼 grouping columns이 가질 수 있는 모든 경우에 대하여 subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다.

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

STEMP 5. CUBE 함수 이용

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

=> CUBE함수 사용으로 ROLLUP 함수 결과에다 업무별집계까지 추가해서 출력

STEP 5-2. UNION ALL 사용 SQL

예제] SELECT DNAME, JOB, COUNT(*) "Total Emp", SUM(SAL) "Total Sal"
        FROM EMP, DEPT
        WHERE DEPT.DEPTNO=EMP.DEPTNO
        GROUP BY DNAME, JOB
        UNION ALL
        SELECT DNAME, 'All Jobs', COUNT(*) "Total Emp", SUM(SAL) "Total Sal"
        FROM EMP, DEPT
        WHERE DEPT.DEPTNO=EMP.DEPTNO
        GROUP BY DNAME
        UNION ALL
        SELECT 'All Departments', JOB, COUNT(*) "Total Emp", SUM(SAL) "Total Sal"
        FROM EMP, DEPT
        WHERE DEPT.DEPTNO=EMP.DEPTNO
        GROUP BY JOB
        UNION ALL
        SELECT 'All Departments','All Jobs', COUNT(*) "Total Emp", SUM(SAL) "Total Sal"
        FROM EMP, DEPT
        WHERE DEPT.DEPTNO=EMP.DEPTNO

4. GROUPING SETS 함수

: GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이 때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.

예제] 일반 그룹함수를 이용하여 부서별, JOB별 인원수와 급여 합 구하라

SELECT DNAME, 'All Jobs' JOB, COUNT(*) "Total Emp", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
GROUP BY DNAME
UNION ALL
SELECT 'All Deparments' DNAME, JOB, COUNT(*) "Total Emp", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
GROUP BY JOB

예제] 일반 그룹함수를 GOUPING SETS 함수로 변경하여 부서별, JOB별 인원수와 급여 합을 구하라

SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,
COUNT(*) "Total Emp", SUM(SAL) "Total Sal"
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO
GROUP BY GROUPING SETS (JOB,DNAME)

=> GROUPING SETS 함수 사용시 UNION ALL을 사용한 일반 그룹함수를 사용한 SQL과 같은 결과를 얻을 수 있으며, 괄호로 묶은 집합 별로(괄호 내는 계층 구조가 아닌 하나의 데이터로 간주함) 집계를 구할 수 있다.

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

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

=> 실행 결과에서 첫 번째 10건의 데이터는 (DNAME+JOB+MGR) 기준의 집계이며, 두 번째 9건의 데이터는 (DNAME+JOB), 세 번째 8건의 데이터는 (JOB+MGR) 기준의 집계

저작자표시 비영리 변경금지 (새창열림)