[SQLD : Ⅲ. SQL 기본] 7. GROUP BY, HAVING 절

* 집계 함수

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

 - GROUP BY 절은 행들을 소그룹화 한다.

 - SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.

집계 함수명 ( [DISTINCT | ALL] 칼럼이나 표현식 ) 
- ALL : Default 옵션이므로 생략 가능함 
- DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션임

[예제] 일반적으로 집계 함수는 GROUP BY 절과 같이 사용되지만

아래와 같이 테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로도 사용 가능하다.

SELECT COUNT(*) "전체 행수", COUNT(HEIGHT) "키 건수", 
       MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키 
FROM PLAYER;

전체 행수 키 건수 최대키 최소키 평균키

------ ----- ---- ---- -----

480 447 196 165 179.31

1개의 행이 선택되었다.

 

* GROUP BY 절

 GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 옴

 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용

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

GROUP BY절과 HAVING 절 특성

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

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

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

- 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다) - WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.

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

- GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중,

  HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.

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

 

[예제] K-리그 선수들의 포지션별 평균키는 어떻게 되는가란 요구 사항을 접수하였다.

GROUP BY 절을 사용하지 않고 집계 함수를 사용했을 때 어떤 결과를 보이는지 포지션별 평균키를 구해본다.

SELECT POSITION 포지션, AVG(HEIGHT) 평균키 
FROM PLAYER; 

SELECT POSITION 포지션, AVG(HEIGHT) 평균키 * 1행에 오류: ERROR: 단일 그룹의 집계 함수가 아니다.

GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹 단위의 칼럼과 집계 함수를 사용할 수 있다.

그렇지 않으면 예제와 같이 에러를 발생하게 된다.

[예제] SELECT 절에서 사용된 포지션이라는 한글 ALIAS를 GROUP BY 절의 기준으로 사용해본다.

SELECT POSITION 포지션, AVG(HEIGHT) 평균키 
FROM PLAYER 
GROUP BY POSITION 포지션; 
GROUP BY POSITION 포지션 * 3행에 오류: ERROR: SQL 명령어가 올바르게 종료되지 않았다.

[예제] 포지션별 최대키, 최소키, 평균키를 출력한다.

(포지션별이란 소그룹의 조건을 제시하였기 때문에 GROUP BY 절을 사용한다.)

SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상,
       MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키 
FROM PLAYER GROUP BY POSITION;

 

* HAVING

[예제] K-리그 선수들의 포지션별 평균키를 구하는데,

평균키가 180 센티미터 이상인 정보만 표시하라는 요구 사항이 접수되었으므로

WHERE 절과 GROUP BY 절을 사용해 SQL 문장을 작성한다.

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER 
WHERE AVG(HEIGHT) >= 180 GROUP BY POSITION; 
WHERE AVG(HEIGHT) >= 180 * 3행에 오류: ERROR: 집계 함수는 허가되지 않는다.

 “집계 함수는 허가되지 않는다” : WHERE 절에는 AVG()라는 집계 함수는 사용할 수 없다.

WHERE 절은 FROM 절에 정의된 집합(주로 테이블)의 개별 행에 WHERE 절의 조건절이 먼저 적용되고,

WHERE 절의 조건에 맞는 행이 GROUP BY 절의 대상이 된다.

 HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다

[예제] HAVING 조건절에는 GROUP BY 절에서 정의한 소그룹의 집계 함수를 이용한 조건을 표시할 수 있으므로,

HAVING 절을 이용해 평균키가 180 센티미터 이상인 정보만 표시한다.

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키 
FROM PLAYER 
GROUP BY POSITION HAVING AVG(HEIGHT) >= 180;

[예제] SQL 문장은 GROUP BY 절과 HAVING 절의 순서를 바꾸어서 수행한다.

SELECT POSITION 포지션, AVG(HEIGHT) 평균키 
FROM PLAYER 
HAVING AVG(HEIGHT) >= 180 GROUP BY POSITION;

[예제] K-리그의 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수는 얼마인가란 요구 사항이 접수되었다.

WHERE 절과 GROUP BY 절을 사용한 SQL과 GROUP BY 절과 HAVING 절을 사용한 SQL을 모두 작성한다.

SELECT TEAM_ID 팀ID, COUNT(*) 인원수 
FROM PLAYER 
WHERE TEAM_ID IN ('K09', 'K02') GROUP BY TEAM_ID;

팀ID 인원수
---- ----- 
K02 49
K09 49 2개의 행이 선택되었다.

SELECT TEAM_ID 팀ID, COUNT(*) 인원수 
FROM PLAYER 
GROUP BY TEAM_ID HAVING TEAM_ID IN ('K09', 'K02'); 

팀ID 인원수 
----- ----- 
K02 49 
K09 49 2개의 행이 선택되었다.

[예제] 포지션별 평균키만 출력하는데, 최대키가 190cm 이상인 선수를 가지고 있는 포지션의 정보만 출력한다.

SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키 
FROM PLAYER 
GROUP BY POSITION HAVING MAX(HEIGHT) >= 190;

 

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

 - 개별 데이터 확인

[예제] 먼저 개별 입사정보에서 월별 데이터를 추출하는 작업을 진행한다. 이 단계는 월별 정보가 있다면 생략 가능하다.

Oracle SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) 입사월, SAL FROM EMP;

SQL Server SELECT ENAME, DEPTNO, DATEPART(MONTH, HIREDATE) 입사월, SAL FROM EMP;

SQL Server SELECT ENAME, DEPTNO, MONTH(HIREDATE) 입사월, SAL FROM EMP;

 - 월별 데이터 구분

[예제] 추출된 MONTH 데이터를 Simple Case Expression을 이용해서 12개의 월별 칼럼으로 구분한다.

실행 결과에서 보여 주는 ENAME 칼럼은 최종 리포트에서 요구되는 데이터는 아니지만,

정보의 흐름을 이해하기 위해 부가적으로 보여 주는 임시 정보이다.

SELECT ENAME, DEPTNO, CASE MONTH WHEN 1 THEN SAL END M01, 
                      CASE MONTH WHEN 2 THEN SAL END M02, 
                      CASE MONTH WHEN 3 THEN SAL END M03, 
                      CASE MONTH WHEN 4 THEN SAL END M04, 
                      CASE MONTH WHEN 5 THEN SAL END M05, 
                      CASE MONTH WHEN 6 THEN SAL END M06, 
                      CASE MONTH WHEN 7 THEN SAL END M07,
                      CASE MONTH WHEN 8 THEN SAL END M08,
                      CASE MONTH WHEN 9 THEN SAL END M09, 
                      CASE MONTH WHEN 10 THEN SAL END M10, 
                      CASE MONTH WHEN 11 THEN SAL END M11, 
                      CASE MONTH WHEN 12 THEN SAL END M12 
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL 
      FROM EMP);

 - 부서별 데이터 집계

[예제] 최종적으로 보여주는 리포트는 부서별로 월별 입사자의 평균 급여를 알고 싶다는 요구사항

부서별 평균값을 구하기 위해 GROUP BY 절과 AVG 집계 함수를 사용한다.

직원 개인에 대한 정보는 더 이상 필요 없으므로 제외한다.

ORDER BY 절을 사용하지 않았기 때문에 부서번호별로 정렬이 되지는 않았다.

SELECT DEPTNO, AVG(CASE MONTH WHEN 1 THEN SAL END) M01, 
               AVG(CASE MONTH WHEN 2 THEN SAL END) M02, 
               AVG(CASE MONTH WHEN 3 THEN SAL END) M03, 
               AVG(CASE MONTH WHEN 4 THEN SAL END) M04, 
               AVG(CASE MONTH WHEN 5 THEN SAL END) M05, 
               AVG(CASE MONTH WHEN 6 THEN SAL END) M06, 
               AVG(CASE MONTH WHEN 7 THEN SAL END) M07, 
               AVG(CASE MONTH WHEN 8 THEN SAL END) M08, 
               AVG(CASE MONTH WHEN 9 THEN SAL END) M09, 
               AVG(CASE MONTH WHEN 10 THEN SAL END) M10, 
               AVG(CASE MONTH WHEN 11 THEN SAL END) M11, 
               AVG(CASE MONTH WHEN 12 THEN SAL END) M12 
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL 
      FROM EMP) 
GROUP BY DEPTNO ;

[예제] Simple Case Expression으로 표현된 위의 SQL과 같은 내용으로

Oracle의 DECODE 함수를 사용한 SQL 문장을 작성한다

 SELECT DEPTNO, AVG(DECODE(MONTH, 1,SAL)) M01, 
                AVG(DECODE(MONTH, 2,SAL)) M02, 
                AVG(DECODE(MONTH, 3,SAL)) M03, 
                AVG(DECODE(MONTH, 4,SAL)) M04, 
                AVG(DECODE(MONTH, 5,SAL)) M05, 
                AVG(DECODE(MONTH, 6,SAL)) M06, 
                AVG(DECODE(MONTH, 7,SAL)) M07, 
                AVG(DECODE(MONTH, 8,SAL)) M08, 
                AVG(DECODE(MONTH, 9,SAL)) M09, 
                AVG(DECODE(MONTH,10,SAL)) M10, 
                AVG(DECODE(MONTH,11,SAL)) M11, 
                AVG(DECODE(MONTH,12,SAL)) M12 
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL 
      FROM EMP) 
GROUP BY DEPTNO ;

 

* 집계함수와 NULL

빈칸을 NULL이 아닌 ZERO로 표현하기 위해 NVL(Oracle)/ISNULL(SQL Server) 함수를 사용

다중 행 함수를 사용하는 경우는 오히려 불필요한 부하가 발생. NVL 함수를 다중 행 함수 안에 사용할 필요가 없다.

개별 데이터의 급여(SAL)가 NULL인 경우는 NULL의 특성으로 자동적으로 SUM 연산에서 빠짐

불필요하게 NVL/ISNULL 함수로 0 변환시켜 데이터 건수만큼 연산이 일어나게 하는 것은 시스템의 자원을 낭비하는 일

[예제] 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장을 작성한다.

데이터가 없는 경우는 0으로 표시한다.

SIMPLE_CASE_EXPRESSION 조건 
- Oracle SELECT TEAM_ID, NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END),0) FW,
                NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END),0) MF, 
                NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END),0) DF, 
                NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END),0) GK, 
                COUNT(*) SUM 
          FROM PLAYER GROUP BY TEAM_ID;
         
SIMPLE_CASE_EXPRESSION 조건 
- Oracle CASE 표현의 ELSE 0, ELSE NULL 문구는 생략 가능. Default 값인 NULL이 적용됨. 
SELECT TEAM_ID, NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 END),0) FW, 
       NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 END),0) MF, 
       NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 END),0) DF,
       NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 END),0) GK, 
       COUNT(*) SUM 
FROM PLAYER GROUP BY TEAM_ID;

SEARCHED_CASE_EXPRESSION 조건 
- Oracle SELECT TEAM_ID, NVL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW, 
                NVL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF, 
                NVL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF, 
                NVL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END), 0) GK, 
                COUNT(*) SUM 
         FROM PLAYER GROUP BY TEAM_ID;
         
SEARCHED_CASE_EXPRESSION 조건 
- SQL Server SELECT TEAM_ID, ISNULL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW, 
                    ISNULL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF, 
                    ISNULL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF, 
                    ISNULL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END), 0) GK, 
                    COUNT(*) SUM 
              FROM PLAYER GROUP BY TEAM_ID;

[예제] GROUP BY 절 없이 전체 선수들의 포지션별 평균 키 및 전체 평균 키를 출력할 수도 있다.

SELECT ROUND(AVG(CASE WHEN POSITION = 'MF' THEN HEIGHT END),2) 미드필더, 
       ROUND(AVG(CASE WHEN POSITION = 'FW' THEN HEIGHT END),2) 포워드, 
       ROUND(AVG(CASE WHEN POSITION = 'DF' THEN HEIGHT END),2) 디펜더, 
       ROUND(AVG(CASE WHEN POSITION = 'GK' THEN HEIGHT END),2) 골키퍼, 
       ROUND(AVG(HEIGHT),2) 전체평균키 
FROM PLAYER;