PLT 5.6 그룹 함수

PLT 5.6 그룹 함수

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

 

그룹 함수의 종류

함   수	설    명
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의 분산

COUNT(*)를 제외한 모든 그룹 함수들은 NULL값을 무시한다. NULL값을 하나의 값으로 치환하기 위해서는NVL함수를 사용하라. 모든 자료형에 대하여 MAX와 MIN를 사용할 수 있다. 그러나 AVG, SUM, VARIANCE, STDDEV는 NUMBER만 사용 가능하다.

 

그룹 함수 사용

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

 

예) 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

 

데이터 그룹 생성

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

 

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            그룹에 대한 조건을 기술

 

SELECT절

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

 

예) 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

 

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

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

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

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

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

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

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

 

하나 이상의 Column으로 그룹화

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

 

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을 기술한다.

SQL> SELECT deptno,job,COUNT(*),AVG(sal),SUM(sal)   2  FROM emp   3  GROUP BY deptno,job;

 

SQL> SELECT job,deptno,COUNT(*),AVG(sal),SUM(sal)   2  FROM emp   3  GROUP BY job,deptno;

 

WHERE절

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

 

HAVING절

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

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

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

n        행을 그룹화

n        그룹 함수를 적용

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

 

예) 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

 

예) 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

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

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

 

중첩 그룹 함수

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

 

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

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 [출처] PLT 5.6 그룹 함수|작성자 후루꾸