[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 1장. SQL 기본 - 7. GROUP BY, HAVING 절

GROUP BY, HAVING 절

집계 함수(Aggregate Function)

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

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

• ALL : Default 옵션이므로 생략 가능함
• DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션임

주요 집계 함수

• 집계 함수는 그룹에 대한 정보를 제공하 므로 주로 숫자 유형에 사용되지만,
• MAX, MIN, COUNT 함수는 문자, 날짜 유형에도 적용이 가능한 함수이다.
  

예시

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

GROUP BY 절

• 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 사용
• SQL 문에서 FROM 절과 WHERE 절 뒤에 온다.

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

GROUP BY 절의 특징

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
  • 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
  • 집계 함수는 WHERE 절에는 올 수 없다.
    (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다)
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.

예제

• 포지션별 최대키, 최소키, 평균키를 출력해보자.

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

HAVING 절

HAVING 절의 특징

• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

예제

• 평균키가 180 센티미터 이상인 선수 정보를 조회해보자.

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

WHERE 절 vs HAVING 절

• K-리그의 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수는 얼마인가란 요구 사항을 WHERE 절과 HAVING 절을 사용한 두 가지 SQL을 작성해보자.
• WHERE 절 + GROUP BY 절

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

• GROUP BY 절 + HAVING 절

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

• 같은 실행 결과를 얻는 두 가지 방법 중 가능하면 WHERE 절에서 조건절을 적용하여 GROUP BY의 계산 대상을 줄이는 것이 효율적인 자원 사용 측면에서 바람직하다.

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

• 모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법

예제

• 부서별로 월별 입사자의 평균 급여를 알고 싶다는 고객의 요구사항을 해결해보자.

STEP 1. 개별 데이터 확인

• 먼저 개별 입사정보에서 월별 데이터를 추출하는 작업을 진행한다.
• Oracle

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

• SQL Server

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

STEP 2. 월별 데이터 구분

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

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);

STEP 3. 부서별 데이터 집계

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

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 ;

• Oracle의 DECODE 함수를 사용해도 같은 결과를 출력할 수 있다.

SELECT DEPTNO,
AVG(DECODE(MONTH, AVG(DECODE(MONTH, 1,SAL)) M01,
AVG(DECODE(MONTH, AVG(DECODE(MONTH, 2,SAL)) M02,
AVG(DECODE(MONTH, AVG(DECODE(MONTH, 3,SAL)) M03,
AVG(DECODE(MONTH, AVG(DECODE(MONTH, 4,SAL)) M04,
AVG(DECODE(MONTH, AVG(DECODE(MONTH, 5,SAL)) M05,
...
AVG(DECODE(MONTH, AVG(DECODE(MONTH, 12,SAL)) M12,
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL 
      FROM EMP)
GROUP BY DEPTNO ;

집계 함수와 NULL

• 다중 행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 NULL인 행을 다중 행 함수의 대상에서 제외한다.
• 리포트 출력 때 NULL이 아닌 0을 표시하고 싶은 경우에는 NVL(SUM(SAL),0)이나, ISNULL(SUM(SAL),0)처럼 전체 SUM의 결과가 NULL인 경우(대상 건수가 모두 NULL인 경우)에만 한 번 NVL/ISNULL 함수를 사용하면 된다.

예제

• 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장을 작성한다.
• 데이터가 없는 경우는 0으로 표시한다.
• 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;

• SQL Server

SELECT TEAM_ID,
ISNULL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW,
ISNULL(SUM(CASE HEN POSITION = 'GK' THEN 1 END), 0) GK,
ISNULL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF,
ISNULL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF,
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;