GROUP BY, HAVING 절

집계함수

EMP 테이블을 사용합니다.

SELECT
	*
FROM EMP;

집계함수란 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 다중행 함수이다.

집계함수의 특성

• 여러 행들의 그룹이 모여 그룹당 단 하나의 결과를 돌려주는 함수
• GROUP BY 절은 행들을 소그룹화한다.
• SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.

집게함수명([DISTINCT | ALL] 칼럼이나 표현식)

DISTINCT는 같은 값을 하나의 데이터로 간주
ALL는 Default 옵션

집계함수	사용 목적
COUNT()	행의 수
SUM()	합계
AVG()	평균
MAX()	최댓값
MIN()	최솟값
STDDEV()	표준 편차
VARIANCE()	분산
기타 통계 함수	벤더별로 다양한 통계식 제공

SELECT COUNT(*)
     , MAX(A.EMPNO)
     , MIN(A.ENAME)
     , SUM(A.COMM)
     , AVG(A.SAL)
FROM EMP A;

GROUP BY절

GROUP BY 절은 SQL문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류해 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용

SELECT [DISTINCT] 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼이나 표현식]
[HAVING 그룹조건식];

GROUP BY절과 HAVING 절은 다음과 같은 특성을 가진다.

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계함수를 사용한다.
• 집계함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• 집계함수는 WHERE 절에는 올 수 없다.
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거한다.
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

SELECT A.DEPTNO
     , MAX(A.SAL)
     , AVG(A.SAL)
FROM EMP A
GROUP BY A.DEPTNO;

SELECT A.DEPTNO
     , MAX(A.SAL)
     , AVG(A.SAL)
FROM EMP A
WHERE A.SAL < 2000
GROUP BY A.DEPTNO;

HAVING 절

SELECT A.DEPTNO
     , MAX(A.SAL)
     , AVG(A.SAL)
FROM EMP A
WHERE A.SAL < 2000
GROUP BY A.DEPTNO
HAVING MAX(A.SAL) > 1200;

WHERE 절에서의 조건절을 적용해 GROUP BY의 계산 대상을 줄이는 것이 효율적이다.

CASE 표현을 활용한 월별 데이터 집계

CASE를 이용하여 월별로 데이터 집계

SELECT CASE MONTH WHEN 1 THEN  SAL END AS M1, CASE MONTH WHEN 2 THEN  SAL END AS M2
     , CASE MONTH WHEN 3 THEN  SAL END AS M3, CASE MONTH WHEN 4 THEN  SAL END AS M4
     , CASE MONTH WHEN 5 THEN  SAL END AS M5, CASE MONTH WHEN 6 THEN  SAL END AS M6
     , CASE MONTH WHEN 7 THEN  SAL END AS M7, CASE MONTH WHEN 8 THEN  SAL END AS M8
     , CASE MONTH WHEN 9 THEN  SAL END AS M9, CASE MONTH WHEN 10 THEN  SAL END AS M10
     , CASE MONTH WHEN 11 THEN  SAL END AS M11, CASE MONTH WHEN 12 THEN  SAL END AS M12
FROM (SELECT ENAME
           , DEPTNO
           , EXTRACT(MONTH FROM HIREDATE) AS MONTH
           , SAL
      FROM EMP);

GROUP BY 절을 사용한 소그룹화

SELECT DEPTNO
	 , SUM(CASE MONTH WHEN 1 THEN  SAL END) AS M1
	 , SUM(CASE MONTH WHEN 2 THEN  SAL END) AS M2
     , SUM(CASE MONTH WHEN 3 THEN  SAL END) AS M3
     , SUM(CASE MONTH WHEN 4 THEN  SAL END) AS M4
     , SUM(CASE MONTH WHEN 5 THEN  SAL END) AS M5
     , SUM(CASE MONTH WHEN 6 THEN  SAL END) AS M6
     , SUM(CASE MONTH WHEN 7 THEN  SAL END) AS M7
     , SUM(CASE MONTH WHEN 8 THEN  SAL END) AS M8
     , SUM(CASE MONTH WHEN 9 THEN  SAL END) AS M9
     , SUM(CASE MONTH WHEN 10 THEN  SAL END) AS M10
     , SUM(CASE MONTH WHEN 11 THEN  SAL END) AS M11
     , SUM(CASE MONTH WHEN 12 THEN  SAL END) AS M12
FROM (SELECT ENAME
           , DEPTNO
           , EXTRACT(MONTH FROM HIREDATE) AS MONTH
           , SAL
      FROM EMP)
GROUP BY DEPTNO
ORDER BY DEPTNO;

DECODE를 사용한 예제

SELECT DEPTNO, SUM(DECODE(MONTH, 1, SAL)) AS M1, SUM(DECODE(MONTH, 2, SAL)) AS M2
     , SUM(DECODE(MONTH, 3, SAL)) AS M3, SUM(DECODE(MONTH, 4, SAL)) AS M4
     , SUM(DECODE(MONTH, 5, SAL)) AS M5, SUM(DECODE(MONTH, 6, SAL)) AS M6
     , SUM(DECODE(MONTH, 7, SAL)) AS M7, SUM(DECODE(MONTH, 8, SAL)) AS M8
     , SUM(DECODE(MONTH, 9, SAL)) AS M9, SUM(DECODE(MONTH, 10, SAL)) AS M10
     , SUM(DECODE(MONTH, 11, SAL)) AS M11, SUM(DECODE(MONTH, 12, SAL)) AS M12
FROM (SELECT ENAME
           , DEPTNO
           , EXTRACT(MONTH FROM HIREDATE) AS MONTH
           , SAL
      FROM EMP)
GROUP BY DEPTNO
ORDER BY DEPTNO;

집계함수와 NULL 처리

집계함수는 NULL을 제외해서 계산한다.

위의 결과식을 내놓은 쿼리를 이용하여 알아보자.

SELECT AVG(M1), AVG(M2), AVG(M3), AVG(M4), AVG(M5), AVG(M6)
     , AVG(M7), AVG(M8), AVG(M9), AVG(M10), AVG(M11), AVG(M12)
FROM
(SELECT DEPTNO, SUM(DECODE(MONTH, 1, SAL)) AS M1, SUM(DECODE(MONTH, 2, SAL)) AS M2
     , SUM(DECODE(MONTH, 3, SAL)) AS M3, SUM(DECODE(MONTH, 4, SAL)) AS M4
     , SUM(DECODE(MONTH, 5, SAL)) AS M5, SUM(DECODE(MONTH, 6, SAL)) AS M6
     , SUM(DECODE(MONTH, 7, SAL)) AS M7, SUM(DECODE(MONTH, 8, SAL)) AS M8
     , SUM(DECODE(MONTH, 9, SAL)) AS M9, SUM(DECODE(MONTH, 10, SAL)) AS M10
     , SUM(DECODE(MONTH, 11, SAL)) AS M11, SUM(DECODE(MONTH, 12, SAL)) AS M12
FROM (SELECT ENAME
           , DEPTNO
           , EXTRACT(MONTH FROM HIREDATE) AS MONTH
           , SAL
      FROM EMP)
GROUP BY DEPTNO
ORDER BY DEPTNO);

SELECT ROUND(AVG(NVL(M1, 0)), 2) AS M1, ROUND(AVG(NVL(M2, 0)), 2) AS M2
	 , ROUND(AVG(NVL(M3, 0)), 2) AS M3, ROUND(AVG(NVL(M4, 0)), 2) AS M4
     , ROUND(AVG(NVL(M5, 0)), 2) AS M5, ROUND(AVG(NVL(M6, 0)), 2) AS M6
     , ROUND(AVG(NVL(M7, 0)), 2) AS M7, ROUND(AVG(NVL(M8, 0)), 2) AS M8
     , ROUND(AVG(NVL(M9, 0)), 2) AS M9, ROUND(AVG(NVL(M10, 0)), 2) AS M10
     , ROUND(AVG(NVL(M11, 0)), 2) AS M11, ROUND(AVG(NVL(M12, 0)), 2) AS M12
FROM
(SELECT DEPTNO, SUM(DECODE(MONTH, 1, SAL)) AS M1, SUM(DECODE(MONTH, 2, SAL)) AS M2
     , SUM(DECODE(MONTH, 3, SAL)) AS M3, SUM(DECODE(MONTH, 4, SAL)) AS M4
     , SUM(DECODE(MONTH, 5, SAL)) AS M5, SUM(DECODE(MONTH, 6, SAL)) AS M6
     , SUM(DECODE(MONTH, 7, SAL)) AS M7, SUM(DECODE(MONTH, 8, SAL)) AS M8
     , SUM(DECODE(MONTH, 9, SAL)) AS M9, SUM(DECODE(MONTH, 10, SAL)) AS M10
     , SUM(DECODE(MONTH, 11, SAL)) AS M11, SUM(DECODE(MONTH, 12, SAL)) AS M12
FROM (SELECT ENAME
           , DEPTNO
           , EXTRACT(MONTH FROM HIREDATE) AS MONTH
           , SAL
      FROM EMP)
GROUP BY DEPTNO
ORDER BY DEPTNO);