[오라클 교재] 제5장 그룹 함수(Multi Row Function)

1. 그룹 함수

단일 행 함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수를 말한다. 그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY절을 이용하여 그룹화 할 수 있고 HAVING를 이용하여 그룹에 대한 조건을 제한하는 방법을 배우기로 한다.

 

1.1 그룹 함수의 종류

함   수	설    명
AVG(DISTINCT|ALL|n)	NULL값을 제외한 n개 행의 평균값
COUNT(DISTINCT|ALL|expr|*)	NULL이 아닌 행의 개수
MAX(DISTINCT|ALL|expr)	최대값
MIN(DISTINCT|ALL|expr)	최소값
STDDEV(DISTINCT|ALL|n)	NULL값을 제외한 n의 표준편차
SUM(DISTINCT|ALL|n)	NULL값을 제외한 n의 합계
VARIANCE(DISTINCT|ALL|n)	NULL값을 제외한 n의 분산

 

☞ Guidelines

1)       DISTINCT는 해당 함수로 하여금 오직 중복되지 않는 값만 RETURN하게 해준다. 그러나 ALL(Default)은 해당 함수로 하여금 모든 값을 고려하게 한다.

2)       Expr이 있는 인수들의 자료 형태는 CHAR, VARCHAR2, NUMBER, DATE형이 될 수 있다.

3)       COUNT(*)를 제외한 모든 그룹 함수들은 NULL값을 무시한다. NULL값을 하나의 값으로 치환하기 위해서는NVL함수를 사용하라.

4)       모든 자료형에 대하여 MAX와 MIN를 사용할 수 있다. 그러나 AVG, SUM, VARIANCE, STDDEV는 NUMBER만 사용 가능하다.

 

1.2 그룹 함수 사용

1.2.1 Syntax

SELECT              group_function(column) [,group_function(column), . . .]     FROM           table_name       [WHERE        condition]       [ORDER BY     column];

 

 

 

 

 

문제1) EMP 테이블에서 모든 SALESMAN에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.

SQL> SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)   2  FROM emp   3  WHERE job LIKE 'SAL%';    AVG(SAL)  MAX(SAL)  MIN(SAL)  SUM(SAL) --------- --------- --------- ---------      1400      1600      1250      5600

 

문제2) 아래의 SELECT 문장을 분석하여라.

SQL> SELECT MIN(ename),MAX(ename),MIN(hiredate),   2         MAX(hiredate),MIN(sal),MAX(sal)   3  FROM emp;   MIN(ENAME) MAX(ENAME) MIN(HIREDATE)      MAX(HIREDATE)       MIN(SAL)  MAX(SAL) ---------- ---------- ------------------ ------------------ --------- --------- ADAMS      WARD       17-DEC-80          12-JAN-83                800      5000

 

문제3) EMP 테이블에 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하여라.

SQL> SELECT COUNT(*) c_inwon,COUNT(comm) c_comm,AVG(comm) a_comm, 2         AVG(NVL(comm,0)) n_comm,COUNT(deptno) c_dept, 3         COUNT(DISTINCT deptno) c_dis   4  FROM emp;     C_INWON    C_COMM    A_COMM    N_COMM    C_DEPT     C_DIS --------- --------- --------- --------- --------- ---------        14         4       550 157.14286        14         3

 

1.3 데이터 그룹 생성

지금까지 모든 그룹 함수는 테이블을 하나의 큰 그룹으로 다루었다. 여기서는 테이블의 자료를 보다 작은 그룹으로 나누어 사용할 수 있는 GROUP BY절을 배워보자.

 

1.3.1 Syntax

SELECT               [column,] group_function(column) [,group_function(column),...]       FROM          table_name       [WHERE        condition]       [GROUP BY     group_by_expression]       [HAVING       condition]       [ORDER BY     column];

table_name   테이블명 질의 대상 테이블 이름

WHERE                condition 을 만족하는 행들만 검색

ORDER BY     질의 결과 정렬을 위한 옵션(ASC:오름차순(Default),DESC내림차순)

group_function                      Group Function이 SELECT절 뒤에서 Column과 같이

기술되면 반드시 GROUP BY절이 기술되어야 한다.

GROUP BY group_by_expression 행을 그룹하기 위한 기준이 될 Column을 기술

HAVING       condition             그룹에 대한 조건을 기술

 

1.3.2 SELECT절

SELECT절에 GROUP 함수와 Column이 같이 기술되면 반드시 GROUP BY절이 기술되어야 한다. 그러나 SELECT절에 GROUP 함수만 기술되고 Column은 기술되지 않으면 반드시 GROUP BY절을 기술할 필요는 없다.

SQL> SELECT deptno,COUNT(*),AVG(sal),MIN(sal),MAX(sal),SUM(sal)   2  FROM emp; SELECT deptno,COUNT(*),AVG(sal),MIN(sal),MAX(sal),SUM(sal)        * ERROR at line 1: ORA-00937: not a single-group group function

 

문제4) EMP 테이블에서 부서별로 인원수,평균 급여,최저급여,최고 급여,급여의 합을 구하여 출력하여라.

SQL> SELECT deptno,COUNT(*),AVG(sal),MIN(sal),MAX(sal),SUM(sal)   2  FROM emp   3  GROUP BY deptno;      DEPTNO  COUNT(*)  AVG(SAL)  MIN(SAL)  MAX(SAL)  SUM(SAL) --------- --------- --------- --------- --------- ---------        10         3 2916.6667      1300      5000      8750        20         5      2175       800      3000     10875        30         6 1566.6667       950      2850      9400

 

문제5) 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

SQL> SELECT deptno,COUNT(*),AVG(sal),MIN(sal),MAX(sal),SUM(sal)   2  FROM emp   3  GROUP BY deptno   4  ORDER BY SUM(sal) DESC;      DEPTNO  COUNT(*)  AVG(SAL)  MIN(SAL)  MAX(SAL)  SUM(SAL) --------- --------- --------- --------- --------- ---------        20         5      2175       800      3000     10875        30         6 1566.6667       950      2850      9400        10         3 2916.6667      1300      5000      8750

 

문제6) 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

SQL> SELECT COUNT(*),AVG(sal),MIN(sal),MAX(sal),SUM(sal)   2  FROM emp   3  GROUP BY deptno   4  ORDER BY SUM(sal) DESC;    COUNT(*)  AVG(SAL)  MIN(SAL)  MAX(SAL)  SUM(SAL) --------- --------- --------- --------- ---------         5      2175       800      3000     10875         6 1566.6667       950      2850      9400         3 2916.6667      1300      5000      8750

 

♣ 참고

위 결과는 어느 행이 어떤 부서의 결과인지 알 수 없다. 즉 GROUP BY절에 기술된 Column은 SELECT절에 반드시 기술될 필요는 없다. 그러나 결과를 구분하기가 어렵다.

 

☞ Guidelines

1)       SELECT절에 GROUP Function이 포함된다면 GROUP BY절에 각각의 열이 명시되어야 함

2)       WHERE절을 사용하여 행을 그룹으로 나누기 전에 행을 제외

3)       그룹에 대한 조건은 HAVING절을 사용(그룹에 대한 조건을 WHERE절에서 기술 불가)

4)       GROUP BY절에 열을 포함(열의 별칭은 사용할 수 없다)

5)       Default는 GROUP BY절 다음에 기술된 순서로 오름차순으로 정렬되지만 ORDER BY절을 이용하여 변경 가능

 

1.3.3 하나 이상의 Column으로 그룹화

때로는 그룹 내의 그룹에 대한 결과를 검색할 필요가 있다. 이러한 경우 대그룹, 중그룹,소그룹으로 나누고자 하는 순서대로 GROUP BY절 뒤에 기술하면 순서대로 오름차순으로 정렬된다.

 

가) Syntax

SELECT               [column,] group_function(column) [,group_function(column),...]       FROM          table_name       [WHERE        condition]       [GROUP BY     group_by_expr1[,group_by_expr2, . . . .]]       [HAVING       condition]       [ORDER BY     column];

GROUP BY group_by_expr1[,group_by_expr2, . . . .]] 행을 그룹하기 위한 기준이 될 Column을 기술한다.

 

문제7) 부서별, 업무별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

SQL> SELECT deptno,job,COUNT(*),AVG(sal),SUM(sal)   2  FROM emp   3  GROUP BY deptno,job;      DEPTNO JOB        COUNT(*)  AVG(SAL)  SUM(SAL) --------- --------- --------- --------- ---------        10 CLERK             1      1300      1300        10 MANAGER           1      2450      2450        10 PRESIDENT         1      5000      5000 . . . . . . . . 9 rows selected.

 

문제8) 업무별, 부서별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

SQL> SELECT job,deptno,COUNT(*),AVG(sal),SUM(sal)   2  FROM emp   3  GROUP BY job,deptno;   JOB          DEPTNO  COUNT(*)  AVG(SAL)  SUM(SAL) --------- --------- --------- --------- --------- ANALYST          20         2      3000      6000 CLERK            10         1      1300      1300 CLERK            20         2       950      1900 CLERK            30         1       950       950 MANAGER          10         1      2450      2450 MANAGER          20         1      2975      2975 MANAGER          30         1      2850      2850 . . . . . . . . . . 9 rows selected.

 

1.3.4 WHERE절

WHERE절을 이용하여 조회하고자 하는 ROW를 선별할 수 있다. 그러나 그룹에 대한 제한 조건은 WHERE절에서 기술할 수 없다. 즉 GROUP FUNCTION는 문법상 WHERE절에 기술할 수 없다. 그러므로 그룹에 대한 제한 조건은 HAVING절에서 기술한다.

SQL> SELECT deptno,COUNT(*),SUM(sal)   2  FROM emp   3  WHERE COUNT(*) > 4   4  GROUP BY deptno; WHERE COUNT(*) > 4       * ERROR at line 3: ORA-00934: group function is not allowed here

문제9) EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라.

SQL> SELECT deptno,COUNT(*),SUM(sal)   2  FROM emp   3  GROUP BY deptno   4  HAVING COUNT(*) > 4;      DEPTNO  COUNT(*)  SUM(SAL) --------- --------- ---------        20         5     10875        30         6      9400

 

1.3.5 HAVING절

1)       WHERE절에 GROUP Function을 사용할 수 없다.

2)       HAVING절을 사용하여 출력될 그룹을 명시

3)       오라클 서버는 HAVING절을 사용할 때 다음의 단계를 수행

①       행을 그룹화

②       그룹 함수를 적용

③       HAVING절과 일치하는 그룹을 출력

 

문제10) EMP 테이블에서 급여가 최대 2900이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.

SQL> SELECT deptno, AVG(sal),SUM(sal)   2  FROM emp   3  GROUP BY deptno   4  HAVING MAX(sal) > 2900;      DEPTNO  AVG(SAL)  SUM(SAL) --------- --------- ---------        10 2916.6667      8750        20      2175     10875

 

문제11) EMP 테이블에서 업무별 급여의 평균이 3000 이상인 업무에 대해서 업무명,평균 급여, 급여의 합을 구하여 출력하여라.

SQL> SELECT job, AVG(sal),SUM(sal)   2  FROM emp   3  GROUP BY job   4  HAVING AVG(sal) >= 3000;   JOB        AVG(SAL)  SUM(SAL) --------- --------- --------- ANALYST        3000      6000 PRESIDENT      5000      5000

문제12) EMP 테이블에서 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하여라. 단 판매원은 제외하고 월 급여 합계로 정렬(내림차순)하여라.

SQL> SELECT job,SUM(sal) PAYROLL   2  FROM emp   3  WHERE job NOT LIKE 'SALE%'   4  GROUP BY job   5  HAVING SUM(sal) > 5000   6  ORDER BY SUM(sal) DESC;   JOB         PAYROLL --------- --------- MANAGER        8275 ANALYST        6000

 

☞ Guidelines

1)       HAVING절은 GROUP BY절 앞에 기술 가능하지만 GROUP BY절 다음에 기술하는 것이 논리적이므로 권장 됩니다. HAVING절이 SELECT절에 있는 그룹에 적용되기 전에 그룹은 구성되고 그룹 함수는 구성됩니다.

2)       SELECT절에 그룹 함수를 사용하지 않고 GROUP BY 절만 사용 가능하다. 그룹 함수의 결과로 행이 제한 된다면 HAVING절 뿐만 아니라 GROUP BY절을 사용해야 된다.

 

1.3.6 중첩 그룹 함수

그룹 함수는 어떤 LEVEL까지도 중첩할 수 있습니다.

 

문제13) 부서별 평균 중 최대 평균 급여, 부서별 급여의 합 중 최대 급여, 부서별 급여의 최소 급여, 부서별 급여의 최대 급여를 출력하여라.

SQL> SELECT MAX(AVG(sal)),MAX(SUM(sal)),MIN(MIN(sal)),MAX(MAX(sal))   2  FROM emp   3  GROUP BY deptno;   MAX(AVG(SAL)) MAX(SUM(SAL)) MIN(MIN(SAL)) MAX(MAX(SAL)) ------------- ------------- ------------- -------------     2916.6667         10875           800          5000

 

 

◈ 연 습 문 제 ◈

 

1. EMP 테이블에서 인원수,최대 급여,최소 급여,급여의 합을 계산하여 출력하는 SELECT 문장을 작성하여라.

 

2. EMP 테이블에서 각 업무별로 최대 급여,최소 급여,급여의 합을 출력하는 SELECT 문장을 작성하여라.

 

3. EMP 테이블에서 업무별 인원수를 구하여 출력하는 SELECT 문장을 작성하여라.

 

4. EMP 테이블에서 최고 급여와 최소 급여의 차이는 얼마인가 출력하는 SELECT 문장을 작성하여라.

 

5. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

H_YEAR  COUNT(*)  MIN(SAL)  MAX(SAL)  AVG(SAL)  SUM(SAL) ------ --------- --------- --------- --------- --------- 80             1       800       800       800       800 81            10       950      5000    2282.5     22825 82             2      1300      3000      2150      4300 83             1      1100      1100      1100      1100

 

6. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

TOTAL      1980      1981      1982      1983 --------- --------- --------- --------- ---------        14         1        10         2         1

 

7. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

JOB       Deptno 10 Deptno 20 Deptno 30     Total --------- --------- --------- --------- --------- ANALYST                  6000                6000 CLERK          1300      1900       950      4150 MANAGER        2450      2975      2850      8275 PRESIDENT      5000                          5000 SALESMAN                           5600      5600