[SQLD : Ⅳ. SQL 활용] 5. 그룹 함수

* 데이터 분석 개요

 - AGGREGATE FUNCTION

  GROUP AGGREGATE FUNCTION이라고도 부르며, GROUP FUNCTION의 한 부분으로 분류

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

 - GROUP FUNCTION

  소계, 중계, 합계, 총 합계 등 여러 레벨의 결산 보고서를 만드는 것이 중요 업무 중의 하나

  여러 단계의 SQL을 UNION, UNION ALL로 묶은 후 하나의 테이블을 여러 번 읽어 다시 재정렬하는 복잡한 단계

  CASE 함수를 이용하면 쉽게 원하는 포맷의 보고서 작성도 가능

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

  GROUP BY 항목들 간 다차원적인 소계를 계산 할 수 있는 CUBE 함수,

  특정 항목에 대한 소계를 계산하는 GROUPING SETS 함수

 - WINDOW FUNCTION

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

 

* ROLLUP 함수

 1. 일반적인 GROUP BY 절 사용

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

SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, JOB;

 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.DEPTNO = EMP.DEPTNO 
GROUP BY DNAME, JOB ORDER BY DNAME, JOB;

2. ROLLUP 함수 사용

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

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

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.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB) ORDER BY DNAME, JOB ;

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.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB);

  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.DEPTNO = EMP.DEPTNO 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.DEPTNO = EMP.DEPTNO GROUP BY ROLLUP (DNAME, JOB);

 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 Empl", SUM(SAL) "Total Sal" 
FROM EMP, DEPT 
WHERE DEPT.DEPTNO = EMP.DEPTNO GROUP BY DNAME, ROLLUP(JOB)

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을 소계시 하나의 집합으로 간주하여 구분하지 않음

 ROLLUP 함수 사용시 괄호로 묶은 JOB과 MGR의 경우 하나의 집합(JOB+MGR) 칼럼으로 간주하여

 괄호 내 각 칼럼별 집계를 구하지 않음

 

* CUBE 함수

 - Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용

 - 계층 구조인 ROLLUP과는 달리 평등한 관계, 순서가 바뀌는 경우 행간 순서는 바뀔 수 있어도 데이터 결과는 같다

5. CUBE 함수 이용

  [예제] GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY CUBE (DNAME, 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.DEPTNO = EMP.DEPTNO GROUP BY CUBE (DNAME, JOB) ;

5-2. UNION ALL 사용 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 ;

 

* GROUPING SETS 함수

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

   이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다

 - GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시

  [예제] 일반 그룹함수를 이용하여 부서별, 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별 인원수와 급여 합을 구하는데

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