오라클 SQL 문법 공부14 - GROUP BY 절, HAVING 절

서론

SQL 기본 절 중에 가장 헛갈려 할 수 있는 GROUP BY 절에 대해서 알아보고자 한다. HAVING은 WHERE절의 조건과 비슷하지만 쓰임이 다르므로 구분만 하면 되겠다. GROUP BY 절을 완벽히 이해하면 대부분의 문법 문제들은 쉽게 풀 수가 있을 것이다.

본론

GROUP BY 절

--GROUP BY 절
--각 행을 특정 조건에 따라 그룹으로 분리하여 계산하도록 하는 구문식이다.
--WHERE 절을 사용하여 행을 그룹으로 나누기 전에 행을 제외한다(조건검색)
--GROUP BY에 있는 컬럼을 이용하여 그룹을 지정한다
--그룹에 대한 조건은 HAVING을 사용한다
--그룹에 대한 조건을 WHERE 절에서 사용할 수 있다
--같은 그룹끼리 묶기 위해 오라클 내에서 정렬을 수행함

--SELECT 그룹함수   -5
--FROM 테이블       -1
--WHERE 조건        -2
--GROUP BY 컬럼     -3
--HAVING 그룹조건   -4
--ORDER BY 컬럼     -6

SELECT SUM(SAL), SAL FROM EMP;
--위의 쿼리는 SUM은 단일행으로 결과가 나오는데 SAL은 모든 컬럼이 나와
--같이 표현을 할 수 없다. 틀린 SQL문이다. 특정 그룹으로 묶어 어떤 그룹의 합은 몇 이런식으로
--표현을 가능하게 해주는게 그룹 함수. 논리적으로 어떻게 표시 될건지
--그림을 그리고 쿼리를 작성하는게 좋은 듯

SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO;
--그룹바이 절에 있는 부서번호 컬럼의 같은 값들로 묶어서,
--SELECT 절의 부서번호 컬럼을 표시하고 SUM 함수를 통해 연봉의 합을 표현 할 거다.

SELECT DEPTNO, TO_CHAR(HIREDATE, 'YYYY'), SUM(SAL) FROM EMP GROUP BY DEPTNO, TO_CHAR(HIREDATE, 'YYYY');
--그룹바이 절에 있는 부서번호 컬럼의 같은 값들로 묶고
--같은 부서번호 값들 중에 입사년도가 같은 것들끼리 또 묶어서
--SELECT 절의 부서번호 컬럼을 표시하고,
--부서번호가 같지만 입사년도가 다른 값들도 표시해서
-- SUM 함수를 통해 연봉의 합을 표현 할 거다.

--GROUP BY 없이 그룹함수만 사용하면 행이 한 줄만 나오지만
--GROUP BY로 같은 값들끼리 묶었다면 여러 행이 나올 수도 있다.
--특정조건의 전체의 합(단일행), 특정조건의 특정 그룹 별간의 합(복수 행)

그룹바이절을 사용하여 조회를 한번이라도 해보면 아 이런식으로 데이터들이 표현되는구나 라고 생각이 될 것이다. 예를들어 전의 문법 공부 13에서 알아본 그룹함수를 통해서는 WHERE 절에 있는 조건에 있는 값들의 전체 합이나 평균등을 구할 수만 있었다면, GROUP BY 절을 통해 컬럼을 명시해 주면 해당 컬럼 값들을 그룹으로 묶어 그룹 별 합이나 평균등을 구하기 위해 사용 된다고 보면 될 것이다.

예를들어 가게 라는 테이블에 종류, 크기, 이름, 가격 컬럼이 있다면 그룹함수만 단독으로 사용 할 경우 특정 조건의 값들만 처리할 수 있었다. 하지만 그룹바이절을 통해 종류별로 가격들의 평균 혹은 종류별, 크기별 가격의 평균 등도 구할 수 있다는 것이다.

내가 구하고자하는 값을 위해 그룹바이 절에 컬럼들을 명시하였다면 SELECT 절에 마구잡이로 컬럼을 적는다고 값이 나오는게 아니라 그룹별로 원하고자 하는 형태를 상상하면서 SELECT 절에 컬럼들을 적어주면 좀 더 쉽게 그룹바이절을 사용 할 수 있을 것이다.

예를 들어 내가 종류별, 크기별로 묶은 행들의 가격의 평균 값을 구할 건데 그렇다면 종류컬럼과 크기 컬럼, 가격컬럼의 평균 값이 표시가 되어야 되니 SELECT 절에 종류, 크기, 가격 컬럼이 와야 되는데 쌩뚱 맞게 이름 컬럼까지 써버리면 당연히 오류가 난다.

HAVING 절

--해빙 절은 그룹을 만들어 놓고 나올 결과에 대하여 필터를 넣는거
--그룹으로 묶여서 나올 결과 전에 필터를 한번 더 걸어 주는 거라고 생각하면 쉬움
--부서별 평균연봉이 2500이상인 부서 정보 조회
SELECT DEPTNO, AVG(SAL)
FROM EMP GROUP BY DEPTNO
HAVING AVG(SAL) > 2500;

--10번 부서의 평균연봉 조회
SELECT DEPTNO, AVG(SAL)
FROM EMP GROUP BY DEPTNO
HAVING DEPTNO = 10;

--쿼리 순서를 알게 되면 위와 같은 조건에서는
--WHERE 절에서 미리 필터를 한 후에 그룹을 묶는게
--성능상 더 좋겠다
SELECT DEPTNO, AVG(SAL)
FROM EMP 
WHERE DEPTNO = 10
GROUP BY DEPTNO;

해빙 절은 그룹바이 절에서 사용된 컬럼혹은 함수를 사용한 컬럼들, 그룹함수에 대해 필터를 걸어준다고 보면 되겠다. 위에도 명시해 놓았지만 아주 단순한 필터 같은 경우에는 미리 WHERE절에서 필터해주는 것이 성능상 더 좋다. 그룹바이 절에 없는 것에 대한 필터는 불가능하다. WHERE 절과 헛갈릴 수 있는데 WHERE 절은 모든 컬럼에 대해 필터를 건다고 생각하면 되고 HAVING 절은 그룹바이로 묶은 것들에 대해서만 필터를 건다고 보면 된다. 따라서 그룹바이절에서 사용되지 않은 것에 대해서 해빙을 사용할 순 없다.

EX) GROUP BY DEPTNO, TO_CHAR(HIREDATE, 'YYYY') HAVING HIREDATE = SYSDATE (X)

     GROUP BY DEPTNO, TO_CHAR(HIREDATE, 'YYYY') HAVING TO_CHAR(HIREDATE, 'YYYY') = '2017' (O)

그래서 보통 단순 컬럼값들에 대해서는 WHERE절에서 필터를 걸고, 주로 해빙이 사용되는 경우는 그룹함수에 대한 범위를 지정해 준다고 보면 좋겠다.

EMP테이블에서 업무별 급여의 평균이 3000이상인 업무에 대해서 업무명, 평균 급여, 급여의 합을 구하여라

SELECT JOB, AVG(SAL), SUM(SAL)

FROM EMP

GROUP BY JOB

HAVING AVG(SAL) >= 3000;

실습문제

--1) EMP 테이블에서 이름과 SAL, COMM을 출력하되, COMM이 NULL이면 'COMMISSION IS NULL'로 바꿔 출력한다.
SELECT ENAME,
       SAL,
       TO_CHAR(NVL(TO_CHAR(COMM), 'COMMISSION IS NULL'))
  FROM EMP;

--2) PROFESSOR 테이블에서 입사일이 'SEP 01, 01'인 교수의 이름과 입사일을 출력하여라.
--단, 조건절에 'SEPTEMBER 01, 01'를 변형하지 않고 그대로 작성하여라
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;

SELECT *
  FROM PROFESSOR
 WHERE HIREDATE = TO_DATE('SEPTEMBER 01, 01', 'MM DD, YY');


--3) EMP테이블에 있는 사원들의 연봉인상을 하고자 한다. 사원이름, 기존 SAL, 새로운 SAL을 출력하여라.
--새로운 연봉(SAL)은 2000이하인 경우 인상률이 15%이고, 2000이상인 경우는 8% 이다.(소수점 첫번째 자리에서 반올림하여라)
--새로운 연봉은 '2,000 만원' 형식으로 작성하고 열 이름을 NEW_SALARY 한다.
SELECT ENAME,
       SAL,
       TO_CHAR(ROUND(
                                       CASE
                                         WHEN SAL < 2000 THEN SAL * 1.15
                                         ELSE SAL * 0.8
                                       END), '999,999') || '만원' AS NEW_SALARY
  FROM EMP;
 
--4) EMP2 테이블에서 고용형태(EMP_TYPE)에 따라 평균 연봉을 구하고
--그 연봉이 30000000 이상인 직원의 고용형태와 평균연봉을 출력하라.
--단, 평균연봉은 천단위 구분기호로 표시하여라.
SELECT EMP_TYPE,
       TO_CHAR(AVG(PAY), '999,999,999')
  FROM EMP2
 GROUP BY EMP_TYPE
HAVING AVG(PAY) >= 30000000 ;
 

--5) EMP2 테이블에서 출생년도(1960,1970,1980,1990)별로 평균연봉을 구하되, 
--출생년도가 같은 직원들에 대해서는 고용형태에 따라 분리하여 
--평균연봉을 구하여라.
SELECT SUBSTR(TO_CHAR(BIRTHDAY, 'YYYY'), 1, 3) || '0',
       EMP_TYPE,
       AVG(PAY)
  FROM EMP2
 WHERE SUBSTR(TO_CHAR(BIRTHDAY, 'YYYY'), 1, 3) IN ('196',
               '197',
               '198',
               '199')
 GROUP BY SUBSTR(TO_CHAR(BIRTHDAY, 'YYYY'), 1, 3),
       EMP_TYPE;

결론

GROUP BY와 HAVING 절에 대해서 알아보았다. 그룹 바이 절은 정말 중요한 절이기 때문에 확실하게 알고 가는게 좋을 것이다. 특정 시간별로 나누거나 날짜별로 나누어서 처리하는 것들이 많기 때문에 확실히 자기 것으로 만드는 것이 좋겠다. 실습문제에 대한 샘플데이터 들은 본 블로그 문법공부 4장에 SQL 파일로 있다.

2017/09/19 - [공부/Oracle] - 오라클 SQL 문법 공부4 - 기본문법 실습