Oracle Chapter5. 그룹함수 (오라클 입문자용)

현 블로그는 모바일 환경이 아닌 PC환경에 최적화 되어있습니다.

Mir의 운영환경

그룹함수란? 
그룹함수는 전체 데이터를 그룹별로 구분하여 통계적인 결과를 구하기 위해 자주 사용되는 함수이다.
앞서 Chapter4에서 배운 함수는 각각의 값을 변화시키는 것이였다면
그룹함수는 그 값들의 평균, 합계, 최소값, 최대값등을 구하는것이다.

SUM - 해당 컬럼의 행들의 합계를 구한다.
 ex) select SUM(sal) from emp;    --> emp테이블에서 월급(sal)컬럼의 합계를 구하라.

AVG - 해당 컬럼의 행들의 평균을 구한다.
 ex) select AVG(sal) from emp;    --> emp테이블에서 월급(sal)컬럼의 평균을 구하라.

MIN - 해당 컬럼의 행중에 최소값을 구한다.
MAX - 해당 컬럼의 행중에 최대값을 구한다.
 ex) select MAX(sal), MIN(sal) from emp;   --> emp테이블에서 월급(sal)컬럼의 최대값과 최소값을 구하라.

Max와 Min은 숫자뿐만 아니라 문자타입, 날짜타입에도 사용이 가능하다.
 ex) MAX(hiredate)  --> 최근입사날짜 ,  MIN(ename)  --> 알파벳역순으로 제일 빠른이름

COUNT - 조건을 만족시키는 행의 개수를 구한다.
 ex) select COUNT(sal) from emp;   -->  emp테이블에서 월급(sal)컬럼의 행의 개수를 구하라.
 ex) select COUNT(sal) from emp where sla>=2000  --> emp테이블에서 월급이 2000이상인 행의 개수를 구하라.


위 5개의 그룹함수는 모두 조건절(where)을 넣어 조건을 만족시키는 행들의 합계, 평균, 최대값, 최소값을 구할수 있다.
COUNT만 '조건을 만족시키는 행의 갯수를 구한다'라고 설명한 이유는 많이 사용되기 때문이다.
COUNT는 COUNT(*)로 사용될수도 있는데 이것은 테이블에 있는 행의 갯수를 구하는 식이다. 

그룹함수와 일반함수의 최대 차이점은 출력문의 개수이다.
14개의 행을 가진 컬럼에서 일반함수를 사용하면 14개의 출력문이 나오게 된다.
하지만 그룹함수는 14개의 행의 합계,평균등을 출력하기 때문에 1개의 출력문만 나오게 된다.

그럼 그룹함수와 일반함수를 같이 출력하면 어떻게 될까?
그룹함수는 1개의 출력문만을 가지고 있는데 일반컬럼은 14개의 출력문을 출력시켜야된다.
결국 충돌이 일어나게되어 아래처럼 오류가 나게 된다.

 

마지막으로 그룹함수는 NULL값을 자동적으로 제외하는 특징이 있다.
아래는 COMM의 합계를 구한식이다.

일반함수는 NVL이라는 함수를 사용하여 NULL값을 0이나 다른값으로 대체해주어야 오류가 나지 않는다.
하지만 그룹함수는 NULL값을 자동적으로 제외하고 계산을 하기때문에 하나하나 NVL로 값을 대체해줄 필요가 없다.

문제를 풀어보자.



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

1.  사원들의 직책(job)의 갯수를 구하세요.


2. 사원들의 직책(job)이 몇종류가 있는지 갯수를 구하세요.


3. 직책(job)별로 보너스(comm)를 받는사람의 수를 구하세요.


4. 아래는 사원들의 입사한 년도이다. 년도별로 몇명의 사람이 입사했는지 알아보세요.
    (단 이번에는 한번의 명령으로 다 출력해보세요.)

..tip decode를 사용해야 된다. 

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



1.  사원들의 직책(job)의 갯수를 구하세요.
select count(job) from emp;

2. 사원들의 직책(job)이 몇종류가 있는지 갯수를 구하세요.
select count(distinct job) from emp;


1번이나 2번은 똑같은 말이다. 아마 1번문제를 2번과 똑같은 정답으로 푼 사람도 있을것이다.
원래 2번만 낼려고 했는데 헷갈리는 사람이 있을듯해서 1번문제를 추가시켰다.
자 여기서 주의할점이 있다.
중복값을 제거 할때 DISTINCT를 사용하는데 위 정답처럼 job을 먼저  DISTINCT하고 난뒤
COUNT를 해줘야지 중복값이 제외된 값들의 갯수가 나오게 된다.
DISTINCT COUNT(job)를 하게되면 이미 14개의 숫자를 세었기때문에 제거할 중복값이 사라지게 된다.


3. 부서번호(deptno)별로 보너스(comm)를 받는사람의 수를 구하세요.

부서번호(deptno)는 10,20,30이 있기 때문에 3번의 명령을 내려 확인해 보았다.
(필자는 40까지 있는줄 알고 4번의 명령을 내렸다;;;;)
이번달은 30번부서만 보너스를 받은사람이 있다는것을 확인이 된다.


4. 아래는 사원들의 입사한 년도이다. 년도별로 몇명의 사람이 입사했는지 알아보세요.
    (단 이번에는 한번의 명령으로 출력해보세요.)

select count(decode(to_char(hiredate,'yyyy'),'1980',1)) as "1980",

count(decode(to_char(hiredate,'yyyy'),'1981',1)) as "1981",

coaunt(decode(to_char(hiredate,'yyyy'),'1982',1)) as "1982",

count(decode(to_char(hiredate,'yyyy'),'1987',1)) as "1987"

  from emp;

자 해석을 해보자
1 -  우선 입사년도를 date형에서 문자형으로 변환해주었다. to_char(hiredate, 'yyyy')
2 - 문자형으로 바꾼 입사년도를 1980년과 비교를 하여서 true이면 1의 값을 주었다. (decode 1번식, '1980, 1)
3 - 1의 값을 준것(1980년도)를 count 하였다.  count(2번식)
4 - 1980이라는 별칭을 부여하였다. as "1980"
위에서 true이면 1의 값을 주었는데 1말고 다른값을 주어도 count되기때문에 아무값이나 주어도 된다. 

count말고도 sum을 이용해서 풀수도 있다.
select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) as "1980", <====== 참일경우 1, 거짓일경우 0을 대입

sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) as "1981",

sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) as "1982",

sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) as "1987"

from emp; 

위식을 보면 참일경우 1, 거짓일경우 0을 넣어서 모두 더하는 형식으로 구하였다.
count는 거짓을 경우 null값을 넣었기때문에(값을 넣지않았다는것이 즉 null이다.)
자동적으로 제외를 하였지만 만약 0을 넣었으면 0도 count 하였을것이다.

필자가 배우기는 sum을 이용하는 방법을 배웠는데 count를 사용해서도 할수 있다.
(생각하면 왜 count를 이용하는 방법은 안가르쳐줬는지 모르겠다. 이유가 있을려나..) 
뭐 어찌됫던... 다음 GROUP BY절을 배우면 이거 싹다 쓸모 없다...
(어렵게 푼사람들 한테는 미얀하다.. 나만 고생하긴 싫엇다..;;)
그냥 이렇게 하는 방법이 있다고만 알아두자;;



GROUP BY 절 

GROUP BY절은 어떠한 컬럼을 기준으로 두어 그 컬럼의 값별로 합계,평균등을 보고자 할때 사용된다.
예를 들어 위의 문제에서 나온 부서번호를 기준으로 삼으면 부서번호의 값별(10,20,30,40)로
다른 컬럼의 합계,평균등을 구할수 있는것이다.

SELECT 컬럼명 별칭,
FROM 테이블명
WHERE 조건식
GROUP BY 기준으로 삼을 컬럼명;

형식으로 쓰인다.
자 그럼 위의 부서번호(deptno)별로 보너스(comm)을 받는사람을 구해보자.
select deptno, count(comm)
from emp
group by deptno;

매우 간단하게 구해진다.
그런데 위에서 그룹함수에 대해 설명할때 그룹함수는 하나의 출력문을 가지고 있기때문에
여러개의 출력문을 가지는 일반컬럼이랑 같이 출력하면 오류가 난다고 설명했었다.
하지만 GROUP BY절을 사용할때는 오류가 나지않고 원하는 결과가 제대로 나오게된다.

자 위 예제를 한번 풀어서 살펴보자.
GROUP BY절은 DISTINCT를 적용한 컬럼과 같은 효과를 가지게 된다.
우선 deptno를 중복값을 제외하면 3개의 출력문이 나오게 된다.

그리고 count(comm)이라는 명령을 내렸을때
GROUP BY절로 나눠진 3개의 그룹에 각각 count(comm)을 하게 된다.
그럼 출력값이 3개가 나오게 된다. (deptno=10의 그룹에서 1개, 20에서 1개, 30에서 1개)

각 출력값이 3개씩 매치가 되기때문에 오류가 나지않고 정상적으로 출력이 가능해진다.

그럼 아래와 같은 식은 어떻게 될까?
select deptno, ename, count(comm)
from emp
group by deptno;

결과는 오류가 나게 된다.
deptno는 아래에서 그룹을 지어주기때문에 3개의 출력문을 가지지만
ename는 그룹이 지어지지 않기 때문에 14개의 출력문을 가지게 되는것이다.

만약 오류를 내고 싶지 않다면 ename도 같이 그룹을 지어주면 되지만 그렇게 되면
원하는값(부서별로 보너스를 받은사람)은 구할수 없게된다.

ename도 그룹을 지어야 하는데 모두의 이름이 다르기때문에
GROUP BY절을 적용안하는것과 똑같이 되는것이다.
아래 문제를 풀면서 익혀보자.


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

1. 각 부서별로 최대 급여를 구하세요.

2. 각 직급(job)별로 최대 급여를 구하세요. 

3. 각 부서별로 평균 급여를 구하세요.

4. 각 직급(job)별의 인원수를 구하세요.

5. 각 부서별 인원수와 보너스(comm)을 받는사람을 구하세요. 

6. 각 년도별로 입사한 인원수를 구하세요.
 

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

1. 각 부서별로 최대 급여를 구하세요.
select deptno, max(sal) from emp group by deptno;

2. 각 직급(job)별로 최대 급여를 구하세요. 
select job, max(sal) from emp group by job; 

3. 각 부서별로 평균 급여를 구하세요.
select deptno, avg(sal) from emp group by deptno;

4. 각 직급(job)별의 인원수를 구하세요.
select job, count(*) from emp group by job;

5. 각 부서별 인원수와 보너스(comm)을 받는사람을 구하세요. 
select deptno, count(*), count(comm) from emp group by deptno;

 

6. 각 년도별로 입사한 인원수를 구하세요.

select to_char(hiredate, 'YYYY'), count(*)

from emp

group by to_char(hiredate, 'YYYY');

 

마지막 문제를 보자 to_char(hiredate, 'YYYY')를 그룹으로 지정해 주었다.
입사날짜에서 년도만 빼서 그룹을 지어줬기때문에 위의 select 문에서도 년도만을 지정해주어야 한다.
그냥 hiredate를 지정하게 되면 오류가 나게 된다.




having절
일반적으로 조건식을 적을때 where을 사용하지만 group by절에서는 where을 사용할수 없기때문에
having절을 사용하게 된다. (그룹뒤에 where를 쓸수 없고 from뒤에 having를 쓸수 없다.)
select - from - where 이고 group by - having 이라는것을 기억해두자.
별다른 설명이 필요가 없다 문제로 이해하자!

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

1. 부셔별 평균급여를 구하고 그 결과 평균급여가 2000 이상인 부서만 출력하세요.

2. 급여가 1000 이상인 사람들로 부서별 평균급여를 구하고 그 결과 평균급여가 2000 이상인 사람만 출력하세요. 

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



1. 부셔별 평균급여를 구하고 그 결과 평균급여가 2000 이상인 부서만 출력하세요.
select deptno, avg(sal) from emp group by deptno where avg(sal)>=2000

2. 급여가 1000 이상인 사람들로 부서별 평균급여를 구하고 그 결과 평균급여가 2000 이상인 사람만 출력하세요. 

select deptno, avg(sal) from emp 

where sal>=1000 ◀===== where절은 집계 이전 처리가 가능

group by deptno ◀===== 집계

having avg(sal)>=2000; ◀===== having절은 집계 이후 처리가 가능

 

그룹함수와 GROUP BY절에 대해서 공부해 보았다.
다음 Chapter부터는 이제 하나의 테이블이 아닌 하나이상의 테이블을 가지고
값을 출력하는 방법을 배워볼것이다.

처음 데이터베이스를 설명할때 테이블들이 모여서 만들어지는것이라고 설명을 했었다.
하지만 그 테이블이 그냥 모이기만 해서는 데이터베이스가 만들어지는것이 아니다.
서로 서로 연결을 시켜줘야지 데이터베이스가 되는것이다.
다음 Chapter에서는 테이블을 임시적으로 연결시켜서 값을 출력하는 방법을 알아보자.