[SQLD] SQL활용 - GROUP 함수

그룹 함수

그룹 함수란 하나 이상의 행을 그룹으로 묶어 연산하여 총합(SUM), 최댓값(MAX), 평균(AVG) 등의  하나의 결과로 나타내는 것이다.

 

GROUP BY절

WHERE 절을 통해 조건에 맞는 데이터를 조회했지만 테이블에 1차적으로 존재하는 데이터 이외의 정보, 2차 가공 정보도 필요하다. GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용되는 SQL문이다.

특징

- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.

- 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.

- GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.

- 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다)

- WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.

- HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.

- GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.

- HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

 

ROLLUP

GROUP BY절 기준 컬럼에 대해 서브토탈을 만들어 준다. ROLLUP시 GROUP BY절에 컬럼이 두 개 이상오면 순서에 따라 결과가 달라진다.

SELECT DEPTNO, 
    JOB, 
    SUM(SAL)
FROM EMP e 
GROUP BY ROLLUP (DEPTNO, JOB)--DEPTNO별 JOB별 SAL의 합계 + DEPTNO별 JOB의 소계 + 전체 소계

DEPTNO|JOB      |SUM(SAL)|
------+---------+--------+
    10|CLERK    |    1300|
    10|MANAGER  |    2450|
    10|PRESIDENT|    5000|
    10|         |    8750|
    20|IT       |     800|
    20|CLERK    |    1100|
    20|ANALYST  |    6000|
    20|MANAGER  |    2975|
    20|         |   10875|

-> 앞쪽부터 순서대로 부서별, 직업별 순서대로 롤업을 실행하면 부서별 합계 + 직업별 합계 + 전체 합계가 출력된다.

 

SELECT 
	NVL(JOB, '전체JOB') AS JOB,
	NVL(DEPTNO, 0) AS DEPTNO, 
	SUM(SAL)
FROM EMP e 
GROUP BY ROLLUP (JOB, DEPTNO) --JOB별 DEPTNO별 SAL의 합계 + JOB별 DEPTNO의 소계 + 전체 소계
JOB      |DEPTNO|SUM(SAL)|
---------+------+--------+
IT       |     0|        |
IT       |    20|     800|
IT       |     0|     800|
CLERK    |    10|    1300|
CLERK    |    20|    1100|
CLERK    |    30|     950|
CLERK    |     0|    3350|
SALESMAN |    30|    5600|
SALESMAN |     0|    5600|
PRESIDENT|    10|    5000|
PRESIDENT|     0|    5000|
전체JOB    |     0|   29025|

 -> 순서가 바뀌면 직업별 합계 + 부서별 합계 + 전체 합계의 형태로 출력된다.

 -> NVL은 합계 조회시 NULL값을  문자로 바꿔 출력하기 위함이다.

 

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP e 
GROUP BY DEPTNO , ROLLUP(JOB) --DEPTNO별 JOB별 SAL의 합계 + DEPTNO별 JOB의 소계

DEPTNO|JOB      |SUM(SAL)|
------+---------+--------+
      |IT       |        |
      |         |        |
    10|CLERK    |    1300|
    10|MANAGER  |    2450|
    10|PRESIDENT|    5000|
    10|         |    8750|
    20|IT       |     800|
    20|CLERK    |    1100|
    20|ANALYST  |    6000|
    20|MANAGER  |    2975|
    20|         |   10875|
    30|CLERK    |     950|
    30|MANAGER  |    2850|
    30|SALESMAN |    5600|
    30|         |    9400|

-> 직업만 롤업하면 전체 합계는 빠진다

 

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP e 
GROUP BY ROLLUP ((DEPTNO, JOB)) --DEPTNO별 JOB별 SAL의 합계 + 전체 소계

DEPTNO|JOB      |SUM(SAL)
------+---------+--------
      |IT       |        
    10|CLERK    |    1300
    10|MANAGER  |    2450
    10|PRESIDENT|    5000
    20|IT       |     800
    20|CLERK    |    1100
    20|ANALYST  |    6000
    20|MANAGER  |    2975
    30|CLERK    |     950
    30|MANAGER  |    2850
    30|SALESMAN |    5600
      |         |   29025

 

CUBE

제시된 컬럼에 대해 결합 가능한 모든 집계를 계산하는 함수이다. 컬럼의 순서가 바뀌어도 결과는 같고(정렬 순서는 다를 수 있음) 모든 경우의 수를 계산하기 때문에 부하가 크다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP e 
GROUP BY CUBE(DEPTNO, JOB)

DEPTNO|JOB      |SUM(SAL)|
------+---------+--------+
      |         |   29025|
      |IT       |        |
      |IT       |     800|
      |CLERK    |    3350|
      |ANALYST  |    6000|
      |MANAGER  |    8275|
      |SALESMAN |    5600|
      |PRESIDENT|    5000|
    10|         |    8750|
    10|CLERK    |    1300|
    10|MANAGER  |    2450|
    10|PRESIDENT|    5000|
    20|         |   10875|
    20|IT       |     800|
    20|CLERK    |    1100|
    20|ANALYST  |    6000|
    20|MANAGER  |    2975|
    30|         |    9400|
    30|CLERK    |     950|
    30|MANAGER  |    2850|
    30|SALESMAN |    5600|

-> 부서별 합계 + 직업 별 합계 + 부서별 직업별 합계  + 전체 합계 조회된다.

 

GROUPING SETS

GROUP BY절에 나오는 컬럼 순서와 상관없이 지정된 컬럼의 소계를 계산하는 함수이다. 컬럼의 순서가 바뀌어도 결과는 같다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP e 
GROUP BY GROUPING SETS(DEPTNO, JOB) --DEPTNO 별 소계, JOB별 소계

DEPTNO|JOB      |SUM(SAL)|
------+---------+--------+
      |IT       |     800|
      |SALESMAN |    5600|
      |CLERK    |    3350|
      |PRESIDENT|    5000|
      |MANAGER  |    8275|
      |ANALYST  |    6000|
    30|         |    9400|
    20|         |   10875|
    10|         |    8750|

-> 지정해둔 컬럼에 대한 합계만 출력한다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP e 
GROUP BY GROUPING SETS((DEPTNO, JOB)) --일반 GROUP BY절 사용시 결과 (괄호안 컬럼을 하나로 봄)

-> 소괄호로 한번 더 감싸면 컬럼을 하나로 봐 일반 그룹바이 절과 결과 값이 동일하다.

GROUPING

ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분짓기 위해 만들어진 집계 함수이다. 소계가 계산된 결과는 1이 출력 되고 아니면 0이 출력된다.