[SQL 10] 그룹 함수, GROUP BY 절, HAVING 절

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
그룹 함수

그룹 함수는 검색된 여러 행을 이용하여 통계정보를 계산하는 함수이다.

그룹 함수

함수명	기능	사용
COUNT	행의 수를 계산한다.	COUNT(컬럼 | *)
MAX	값들 중에 최대 값을 반환한다.	MAX(컬럼)
MIN	값들 중에 최소 값을 반환한다.	MIN(컬럼)
AVG	평균 값을 계산한다.	AVG(컬럼)
SUM	총 합계를 계산한다.	SUM(컬럼)
VARIANCE	분산을 계산한다.	VARIANCE(컬럼)
STDDEV	표준편차를 계산한다.	STDDEV(컬럼)

• 반드시 하나의 값만을 반환한다.
• NULL 값은 무시된다.
• NULL 값이 무시되지 않으려면 NVL, NVL2와 같은 함수를 이용한다.
• GROUP BY 설정 없이 일반 컬럼과 기술될 수 없다.
  

◈ 예제

select count(commission_pct), count(nvl(commission_pct,0))

from employees;

보너스를 받지 않는 사람은 commission_pct가 null로 되어있다.

그룹함수는 null 값을 무시하기 때문에 첫 번째의 경우는 보너스를 받는 사람의 수만 계산된 것이고,

두 번째의 경우는 보너스를 받지 않는 사람은 0으로 만들어 줌으로써 전체 사원의 수가 계산된 것이다.

select avg(salary) 평균급여1, round(avg(salary)) 평균급여2

from employees;

평균급여1 같은 경우 출력 값의 형식은 세션의 설정이나 평균급여2와 같이 단일 함수를 이용해서 지정할 수 있다.

select sum(salary) 총액, round(avg(salary)) 평균, count(*) 전체인원

from employees;

select min(salary), max(salary), min(hire_date), max(hire_date)

from employees;

MIN, MAX 함수는 숫자 뿐만 아니라 날짜, 문자에도 사용할 수 있다. 문자의 경우 글자수로 판단한다.

이름을 가지고 MIN, MAX 함수를 사용해 보자.

No. 02
GROUP BY 절

특정 속성을 기준으로 그룹화 하여 검색할 때 그룹화 할 속성을 지정한다.

기본 구조

SELECT [DISTINCT] 컬럼, 그룹 함수(컬럼)

FROM 테이블명

[WHERE 조건]

[GROUP BY Group대상]

[ORDER BY 정렬대상 [ASC/DESC]]

• SELECT 절에 그룹 함수와 컬럼명이 같이 기술된 경우 해당 컬럼은 반드시 GROUP BY절에 그룹화 돼야 한다.
• 결과 값이 정렬되길 원한다면 반드시 ORDER BY 절을 추가한다.3

GROUP BY 절은 반드시 그룹함수와 함께 쓰이며 그룹 함수의 결과 값은 GROUP BY 절에 기술된 컬럼의 항목들의 행의 개수에 의해 결정된다.

◈ 예제

select department_id, round(avg(salary))

from employees

group by department_id;

각 부서별로 사원들 급여의 평균 값이 계산됐다.

일반 언어로 표현한다면 GROUP BY절에 기술된 '컬럼(부서)'별로 평균(그룹 함수)를 계산하라.

GROUP BY절의 의미는 그룹 함수를 GROUP BY절에 지정된 컬럼의 값이 같은 행에 대해서 통계 정보를 계산하라는 의미이다.

select round(avg(salary))

from employees

group by department_id;

GROUP BY절에 기술된 컬럼이 SELECT절에 기술되지 않아도 된다.

위의 경우 부서별로 사원들 급여의 평균 값이 계산됐지만 어느 부서의 평균 값인지 모르기 때문에 쓸모없는 정보나 마찬가지이다.

그러므로 SELECT절에도 GROUP BY절에 기술된 컬럼을 기술하길 권한다.

▶ 2개 이상의 그룹화

select department_id 부서번호, job_id 직업, sum(salary)

from employees

group by department_id, job_id

order by department_id;

위의 예를 보면 20번 부서가 2번 나와 있는데 이는 같은 부서일지라도 직업이 다르기 때문에 다른 그룹으로 묶인 것이다.

select department_id 부서번호, job_id 직업, manager_id 상사번호, sum(salary)

from employees

group by department_id, job_id, manager_id

order by department_id;

이번에는 부서번호와 직업이 같은 경우라도 하나의 그룹으로 묶이지 않았다. 이번에는 GROUP BY절에 상사번호까지 기술했기 때문이다.

부서번호, 직업, 상사번호까지 같아야 하나의 그룹으로 묶여 그룹함수의 계산이 이루어진다.

추가로 GROUP BY절을 사용할 경우 ORDER BY절을 사용하기를 권한다.(파악하기 좋게 하기 위해서)

즉, 여러 컬럼을 그룹화 할 경우 GROUP BY절에 나열된 컬럼들의 값이 모두 같아야 같은 그룹으로 묶인다.

▶ 그룹 함수를 중첩해서 사용하는 경우

select department_id 부서번호, max(avg(salary)) 최대평균, min(avg(salary)) 최소평균

from employees

group by department_id;

ORA-00937: not a single-group group function

00937. 00000 -  "not a single-group group function"

부서번호를 그룹화 했음에도 불구하고 위의 예제의 경우 에러가 발생한다.

max(avg()), min(avg())는 평균값의 최대 값과 최소 값이므로 어떤 컬럼으로 그룹을 하던 결과는 최대 값 하나 최소 값 하나이다. 그러나 부서번호의 경우 부서별로 그룹을 묶었기 때문에 값이 여러 개이다.

이해가 안된다면 차근차근 생각해보자.

1. 일단 위의 예제는 부서별로 그룹을 묶었다. 10, 20, 30, … 각 부서마다 그룹으로 묶인 것이다.
2. 그 후 그룹별로 사원들 급여의 평균 값을 구한다.
3. 각 그룹별로 평균 값 중에서 최대 값과 최소 값을 구했다. 각각 결과는 하나이다.
4. select절에 부서번호를 기술했기 때문에 화면에 출력할 때는 그룹으로 묶인 부서들도 보여주어야 한다.

현재는 어느 부서의 최대평균, 최소평균인지는 모른다. 단지 평균값들 중에 최대, 최소가 무엇인지만 검색된 상황이다. 각 부서별로 하나의 행을 차지하고 있을텐데 특정 부서의 옆에 최대평균, 최소평균을 같이 출력한다면 마치 그 부서의 최대평균과 최소평균인 것처럼 보이기 때문에 잘못된 정보가 되버린다. 그래서 이런 상황이 나오지 않도록 예제처럼 SQL문을 작성할 경우 에러로 인식되게 한 것이다.

select max(avg(salary)) 최대평균, min(avg(salary)) 최소평균

from employees

group by department_id;

정리하면 그룹 함수가 중첩된 경우 GROUP BY절에 기술한 컬럼도 출력 할 수 없다.

No. 03
HAVING 절

HAVING 절은 해석상 WHERE 절과 동일하다. 단 조건 내용에 그룹 함수를 포함하는 것만을 포함한다.

일반 조건은 WHERE 절에 기술하지만 그룹 함수를 포함한 조건은 HAVING 절에 기술한다.

기본 구조

SELECT [DISTINCT] 컬럼, 그룹 함수(컬럼)

FROM 테이블명

[WHERE 조건]

[GROUP BY Group대상]

[HAVING 그룹 함수 포함 조건]

[ORDER BY 정렬대상 [ASC/DESC]]

• HAVING 절은 GROUP BY 절 뒤에 기술한다.
• HAVING 절의 조건은 그룹 함수를 포함해야 한다.

◈ 예제

select department_id 부서번호, round(avg(salary)) 평균급여

from employees

group by department_id

having avg(salary) < 7000;

SQL문장에서 일반 조건은 WHERE절에 기술하지만 조건에 그룹 함수가 포함된 경우에는 HAVING절에 기술해야 한다.

WHERE절에 그룹 함수가 포함된 조건을 기술하면 에러가 발생한다.

select department_id 부서번호, sum(salary) 급여총액

from employees

group by department_id

having job_id != 'IT_PROG';

ORA-00979: not a GROUP BY expression

00979. 00000 -  "not a GROUP BY expression"

반대로 HAVING 절에 일반 조건을 기술하면 에러가 발생한다.

그러나 GROUP BY에 기술된 컬럼에 대한 일반 조건은 HAVING절에 기술 가능하다. 하지만 그렇게 되면 SQL문을 해석하기 힘들기 때문에 기술하지 않는 것이 좋다.

일반 조건은 WHERE 절에 그룹 함수를 포함한 조건은 HAVING 절에 기술한다.

select job_id, sum(salary) 급여총액

from employees

where job_id not like '%REP%'

group by job_id

having sum(salary) > 13000

order by sum(salary);

GROUP BY 와 HAVING 절까지 기술한 SELECT 구문을 해석해보자.

1. from 절에 기술한 테이블에서

2. where 절에 기술한 조건에 해당하는 내용들만 정리하고

3. 정리된 내용들을 가지고 group by 절에 나열된 컬럼에 대해서 그룹을 만든다.

4. 만들어진 그룹별로 having절에 기술된 그룹함수로 계산하여 having절 조건에 맞는 그룹만 추리고

5. 추려진 그룹들을 order by 절에 잇는 조건으로 정렬시킨다.

6. 마지막으로 select절에 있는 그룹화된 컬럼 또는 그룹함수를 화면에 출력한다.