10_SQL함수(6) 그룹함수

 

그룹함수(Aggregate Function)란?

레코드를 특정 그룹으로 묶고, 그룹에 대해 연산을 실행하는 함수

그룹함수는 n개의 레코드에 대한 연산결과로 결과 값을 1개 또는 (n-x)개의 결과 값을 얻는다.

n개의 레코드에 대해 n개의 결과값을 얻는 단일행 함수와 쓰임이 다르다.

 

 

 

 

그룹함수의 사용 (GROUP BY, HAVING절)

그룹함수는 단일행함수와 달리 모든 레코드에 1:1로 대응되는 함수가 아니라,

특정 그룹으로 레코드를 묶어서 연산을 적용하는 함수이기 때문에, 

그룹함수를 적용하려면 먼저 그룹을 어떻게 묶을것인지 사용자가 반드시 명시해야 한다. 

이때 사용되는 것이 GROUP BY절이고, 항상 특정 컬럼이 기준이 된다. 예제를 보자.

     

 Ex1)  t_professor 테이블에서 '학과(deptno)별'로 교수님들의 평균 보너스 출력하라.

 

t_professor 테이블

t_professor 테이블이 위의 사진과 같이 작성되어 있을 때, 예제를 풀기 위해서는

 

   1) 일단 먼저 레코드들을 학과별로 그룹하기 위해서 'deptno'컬럼을 그룹기준으로 한다. >> GROUP BY deptno

   2) bonus 필드에 대해 평균값을 구하는 그룹함수를 적용한다. >> AVG(bonus)  

 

    =  SELECT deptno "학과" , AVG(bonus) "학과별 보너스" FROM t_professor GROUP BY deptno; 

 

Ex1 결과

 

 Ex2)  t_professor 테이블에서 학과별(deptno), 직급별(position)로 교수들의 평균 급여를 계산하여 출력하라.

 

   1) 학과별로 묶기 위해서 'deptno'컬럼을 그룹기준으로 한다. >> GROUP BY deptno

   2) 직급별로 묶기 위해서 'postion'컬럼을 기준으로 추가한다. >> GROUP BY deptno, position

   3) pay 필드에 대해 평균값을 구하는 그룹함수를 적용한다. >> AVG(pay) 

 

     =  SELECT deptno "학과", position "직급", AVG(pay) "평균급여"  FROM t_professor 

         GROUP BY deptno, position; 

Ex2 결과

 

 Ex3)  t_professor 테이블에서 학과별(deptno)로 교수들의 평균 급여를 계산하여 출력하라

        (단, 교수들의 학과별 평균급여가 450 이상인 학과만 출력하라)

 

**주의** '교수들의 학과별 평균급여'에 '450 이상'이라는 조건이 붙었다. 이는 HAVING절로 작성한다.

 

   1) 학과별로 묶기 위해서 'deptno'컬럼을 그룹기준으로 한다. >>  GROUP BY deptno, position

   2) pay 필드에 대해 평균값을 구하는 그룹함수를 적용한다. >> AVG(pay) 

   3) 1)의 그룹데이터에 대해 300이상이라는 조건을 명세한다 >> HAVING AVG(pay) >= 300

 

   =  SELECT deptno "학과", AVG(pay) "평균급여"  FROM t_professor  GROUP BY deptno

       HAVING AVG(pay) >= 450; 

 

Ex3 결과

 

 

**주의** SELECT 여러개의 절이 추가되면서 쿼리문이 길어졌으나, 반드시 작성 순서를 지켜야 한다.

SELECT [컬럼명 또는 표현식] 

FROM [테이블명, 뷰명]  

WHERE [조건절]  

GROUP BY [그룹할 컬럼] 

HAVING [그룹함수 조건절]   

ORDER BY [정렬열] [ASC/DESC];

 

 

 

 

 

그룹함수의 종류

(1) COUNT(필드명)

데이터의 개수를 반환하는 함수

 

(2) SUM(필드명)

데이터의 합계를 반환하는 함수

 

(3) AVG(필드명)

데이터의 평균을 반환하는 함수

 

(4) MAX(필드명)

데이터들 중 최대값을 반환하는 함수

 

(5) MIN(필드명)

데이터들 중 최소값을 반환하는 함수

 

 

 

(복합예제1) t_professor 테이블에서 직위가 '정교수' 이거나 '조교수'인 분들 중에서  ‘과별(deptno)’로  

                 과번호, 소속교수 인원, 교수들의 근속일 평균, 급여평균, 보너스 평균을 출력하라

 

                 =  SELECT deptno 학과,                                      

                                  COUNT(*) 총인원,                              
                                  AVG(SYSDATE - hiredate) 근속평균, 
                                  AVG(pay) 급여평균,                            

                                  AVG(NVL(bonus,0)) 보너스평균         
                     FROM t_professor                                           
                     WHERE position IN('정교수','조교수')                        --그룹에 대한 조건이 아니므로 where절로 작성
                     GROUP BY deptno;                                         

 

복합예제1

 

(복합예제2) t_student 테이블에서 학과별(deptno1)로 학과번호, (최대-최소)몸무게 차이 값을 출력하라 

 

                 =  SELECT deptno1 학과, 

                                   MAX(weight) - MIN(weight) 최대최소몸무게차
                     FROM t_student 
                     GROUP BY deptno1;

복합예제2