[SQLD 2-1-5] SQL 기본 - GROUP BY, HAVING 절

1. 집계 함수 (Aggregate Fuction)

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

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

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

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

 

# 집계 함수의 종류

---

2. GROUP BY 절

- WHERE 절을 통해 조건에 맞는 데이터를 조회했지만 테이블에 1차적으로 존재하는 데이터 이외의 정보 즉, 2차 가공 정보도 필요할 때 사용

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

SELECT [DISTINCT] 컬럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 컬럼이나 표현식]
[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-리그 선수들의 포지션별 평균키는 어떻게 되는가란 요구 사항

 

일반적으로 쉽게 범할 수 있는 오류가 나는 쿼리문을 알아보자.

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

 

GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹 단위의 칼럼과 집계 함수를 사용할 수 있기 때문에, 위의 쿼리문은 오류가 난다.

 

또한 다음 쿼리문에서도 오류가 날 것이다.

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

 

GROUP BY 에서는 ALIAS명을 사용할 수 없다.

 

포지션별로 평균키, 인원수, 키 대상 인원수, 최대 키를 출력하기 위해서는 다음과 같이 SQL문을 작성하면 된다.

SELECT POSITION AS 포지션, COUNT(*) AS 인원수, COUNT(HEIGHT) AS 키대상, MAX(HEIGHT) AS 최대키
FROM PLAYER
GROUP BY POSITION;

 

결과는 다음과 같을 것이다.

포지션	인원수	키대상	최대키
	3	0
GK	43	43	196
DF	172	142	190
FW	100	100	194
MF	162	162	189

 

최대키를 구할때 키 값이 NULL인 경우는 계산 대상에서 제외된다.

---

3. HAVING 절

# 예제

- K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180 센티미터 이상인 정보만 표시하라는 요구 사항이 접수되었으므로 WHERE 절과 GROUP BY 절을 사용해 SQL 문장을 작성한다.

 

오류가 발생하는 SQL문 먼저 살펴보자.

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

 

3행에서 오류가 나게된다.

WHERE절에는 AVG()라는 집계함수를 사용할 수 없기 때문이다.

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

그런 다음, 결과 집합의 행에 HAVING 조건절이 적용된다.

결과적으로 HAVING 절의 조건을 만족하는 내용을 출력한다.

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

 

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

 

이렇게 작성해야 올바른 결과를 출력할수 있다. 결과는 다음과 같을 것이다.

포지션	평균키
GK	186.26
DF	180.21
FW	179.91

 

SQL을 보면 SELECT 절에서 사용하지 않는 MAX 집계 함수를 HAVING 절에서 조건절로 사용한 사례이다.

GROUP BY절과 HAVING절의 순서를 바꾸어서 수행하더라도 문법 에러가 없고 결과물도 동일한 결과를 출력한다.

단, SQL Server에서는 문법오류가 발생하므로, GROUP BY 절과 HAVING절의 순서를 지키는 것을 권고한다.

---

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

- “집계 함수(CASE( ))~GROUP BY” 기능은, 모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법이다.

 

# 예제

부서별로 월별 입사자의 평균 급여를 알고 싶다는 고객의 요구사항이 있는데, 입사 후 1년마다 급여 인상이나 보너스 지급과 같은 일정이 정기적으로 잡힌다면 업무적으로 중요한 정보가 될 수 있다.

 

STEP1. 개별 데이터 확인

- 먼저 개별 입사정보에서 월별 데이터를 추출하는 작업을 진행한다. (월별 정보가 있다면 생략 가능)

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

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

 

실행 결과

| ENAME  | DEPTNO | 입사월 |  SAL  |
|--------|--------|--------|-------|
| SMITH  |     20 |     12 |   800 |
| ALLEN  |     30 |      2 |  1600 |
| WARD   |     30 |      2 |  1250 |
| JONES  |     20 |      4 |  2975 |
| MARTIN |     30 |      9 |  1250 |
| BLAKE  |     30 |      5 |  2850 |
| CLARK  |     10 |      6 |  2450 |
| SCOTT  |     20 |      7 |  3000 |
| KING   |     10 |     11 |  5000 |
| TURNER |     30 |      9 |  1500 |
| ADAMS  |     20 |      7 |  1100 |
| JAMES  |     30 |     12 |   950 |
| FORD   |     20 |     12 |  3000 |
| MILLER |     10 |      1 |  1300 |

 

 

STEP2. 월별 데이터 구분

- 추출된 월별 데이터를 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 12 THEN SAL END M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP);

 

실행 결과

| ENAME  | DEPTNO | M01  | M02  | M03  | M04  | M05  | M06  | M07  | M08  | M09  | M10  | M11  | M12  |
|--------|--------|------|------|------|------|------|------|------|------|------|------|------|------|
| SMITH  |     20 |      |      |      |      |      |      |      |      |      |      |      |  800 |
| ALLEN  |     30 |      | 1600 |      |      |      |      |      |      |      |      |      |      |
| WARD   |     30 |      | 1250 |      |      |      |      |      |      |      |      |      |      |
| JONES  |     20 |      |      |      | 2975 |      |      |      |      |      |      |      |      |
| MARTIN |     30 |      |      |      |      |      |      |      |      | 1250 |      |      |      |
| BLAKE  |     30 |      |      |      |      | 2850 |      |      |      |      |      |      |      |
| CLARK  |     10 |      |      |      |      |      | 2450 |      |      |      |      |      |      |
| SCOTT  |     20 |      |      |      |      |      |      | 3000 |      |      |      |      |      |
| KING   |     10 |      |      |      |      |      |      |      |      |      |      | 5000 |      |
| TURNER |     30 |      |      |      |      |      |      |      |      | 1500 |      |      |      |
| ADAMS  |     20 |      |      |      |      |      |      | 1100 |      |      |      |      |      |
| JAMES  |     30 |      |      |      |      |      |      |      |      |      |      |      |  950 |
| FORD   |     20 |      |      |      |      |      |      |      |      |      |      |      | 3000 |
| MILLER |     10 | 1300 |      |      |      |      |      |      |      |      |      |      |      |

 

 

STEP3. 부서별 데이터 집계

- 최종적으로 보여주는 리포트는 부서별로 월별 입사자의 평균 급여를 알고 싶다는 요구사항이므로 부서별 평균값을 구하기 위해 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 12 THEN SAL END) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH,
      SAL FROM EMP)
GROUP BY DEPTNO;

 

실행 결과

| DEPTNO | M01  | M02  | M03  | M04  | M05  | M06  | M07  | M08  | M09  | M10  | M11  | M12  |
|--------|------|------|------|------|------|------|------|------|------|------|------|------|
|     30 |      | 1425 |      |      | 2850 |      |      | 1375 |      |      |  950 |      |
|     20 |      |      |      | 2975 |      |      | 2050 |      |      | 1900 |      |      |
|     10 | 1300 |      |      |      |      | 2450 |      |      |      | 5000 |      |      |

 

하나의 데이터에 여러 번 CASE 표현을 사용하고 집계함수가 적용되므로 SQL 처리 성능 측면에서 나쁘다고 생각될 수 있지만,

하나의 SQL 문장으로 처리 가능하므로 DBMS 자원 활용이나 처리 속도에서 훨씬 효율적이다.

데이터의 건수가 많아질수록 처리 속도의 차이는 더 커질 수 있다.

 

 

STEP4. Oracle의 DECODE 함수를 사용하면 코드를 더 줄일 수 있다.

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

---

5. 집계 함수와 NULL

- 리포트의 빈칸을 NULL이 아닌 ZERO로 표현하기 위해 NVL(Oracle)/ISNULL(SQL Server) 함수를 사용하는 경우가 많은데, 다중 행 함수를 사용하는 경우는 오히려 불필요한 부하가 발생하므로 굳이 NVL 함수를 다중 행 함수 안에 사용할 필요가 없다.

- 다중 행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 NULL인 행을 다중 행 함수의 대상에서 제외한다.

- 예를 들면 100명 중 10명의 성적이 NULL 값일 때 평균을 구하는 다중 행 함수 AVG를 사용하면 NULL 값이 아닌 90명의 성적에 대해서 평균값을 구하게 된다. CASE 표현 사용시 ELSE 절을 생략하게 되면 Default 값이 NULL이다. NULL은 연산의 대상이 아닌 반면, SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0 END)처럼 ELSE 절에서 0(Zero)을 지정하면 불필요하게 0이 SUM 연산에 사용되므로 자원의 사용이 많아진다.

- 같은 결과를 얻을 수 있다면 가능한 ELSE 절의 상수값을 지정하지 않거나 ELSE 절을 작성하지 않도록 한다.

- Oracle의 DECODE 함수는 4번째 인자를 지정하지 않으면 NULL이 Default로 할당된다. 

- 불필요하게 NVL/ISNULL 함수를 사용해 0(Zero)으로 변환시켜 데이터 건수만큼의 연산이 일어나게 하는 것은 시스템의 자원을 낭비하는 일이다. 리포트 출력 때 NULL이 아닌 0을 표시하고 싶은 경우에는 NVL(SUM(SAL),0)이나, ISNULL(SUM(SAL),0)처럼 전체 SUM의 결과가 NULL인 경우(대상 건수가 모두 NULL인 경우)에만 한 번 NVL/ISNULL 함수를 사용하면 된다.