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
문제1) EMP 테이블에서 모든 SALESMAN에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.
문제2) 아래의 SELECT 문장을 분석하여라.
문제3) EMP 테이블에 등록되어 있는 인원수, 보너스에NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하여라.
1.3 데이터 그룹 생성
지금까지 모든 그룹 함수는 테이블을 하나의 큰 그룹으로 다루었다. 여기서는 테이블의 자료를 보다 작은 그룹으로 나누어 사용할 수 있는 GROUP BY 절을 배워보자
1.3.1 Syntax
table_name 테이블명 질의 대상 테이블 이름
WHERE condition을 만족하는 행들만 검색
ORDER BY 질의 결과 정렬을 위한 옵션(ASC:오름차순(Default),DESC내림차순)
group_function group Function 이 SELECT 절 뒤에서 Column과 같이 기술되면 반드시 GROUP BY절이
기술되어야 한다.
GROUP BY group_by_expresstion 행을 그룹하기 위한 기준이 될 Column을 기술
HAVING condition 그룹에 대한 조건을 기술
1.3.2 SELECT 절
SELECT 절에 GROUP 함수와 Column 이 같이 기술되면 반드시 GROUP BY 절이 기술되어야 한다. 그러나 SELECT 절에 GROUP 함수만 기술되고 Column은 기술되지 않으면 반드시 GROUP BY절을 기술할 필요는 없다.
문제 4) EMP 테이블에서 부서별로 인원수, 평균, 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하여라.
문제 5) 각 부서별로 인원수, 급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.
문제 6) 각 부서별로 인원수, 급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.
참고
위 결과는 어느 행이 어떤 부서의 결과인지 알 수 없다. 즉 GROUP BY 절에 기술된 Column은 SELECT 절에 반드시 기술될 필요는 없다. 그러나 결과를 구분하기가 어렵다.
Guidelunes
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
GROUP BY group_by_expr1[, group_by_expr2, . . . .] 행을 그룹하기 위한 기준이 될 Column을 기술한다.'
문제 7) 부서별, 업무별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.
문제 8) 업무별, 부서별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.
1.3.4 WHERE 절
WHERE절을 이용하여 조회하고자 하는 ROW를 선별할 수 있다. 그러나 그룹에 대한 제한 조건은 WHERE절에서 기술할 수 없다. 즉 GROUP FUNCTION는 문법상 WHERE 절에 기술할 수 없다. 그러므로 그룹에 대한 제한 조건은 HAVING 절에서 기술한다.
문제 9) EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라.
1.3.5 HAVING 절
1) WHERE 절에 GROUP Function을 사용할 수 없다.
2) HAVING 절을 사용하여 출력될 그룹을 명시
3) 오라클 서버는 HAVING절을 사용할 때 다음의 단계를 수행
① 행을 그룹화
② 그룹 함수를 적용
③ HAVING절과 일치하는 그룹을 출력
문제 10) EMP 테이블에서 급여가 최대 2900 이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
문제 11) EMP 테이블에서 업무별 급여의 평균이 3000 이상인 업무에 대해서 업무명, 평균 급여, 급여의 합을 구하여 출력하여라.
문제 12) EMP 테이블에서 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하여라. 단 판매원은 제외하고 월 급여 합계로 정렬(내림차순)하여라.
Guidelines
1) HAVING절은 GROUP BY절 앞에 기술 가능하지만 GROUP BY절 다음에 기술하는 것이 논리적으로 권장 됩니다. HAVING절이 SELECT 절에 있는 그룹에 적용되기 전에 그룹은 구성되고 그룹 함수는 구성됩니다.
2) SELECT 절에 그룹 함수를 사용하지 않고 GROUP BY 절만 사용 가능하다. 그룹 함수의 결과로 행이 제한 된다면 HAVING절 뿐만 아니라 GROUP BY절을 사용해야 된다.
1.3.6 중첩 그룹 함수
그룹 함수는 어떤 LEVEL 까지도 중첩할 수 있습니다.
문제 13) 부서별 평균 중 최대 평균 급여, 부서별 급여의 합 중 최대 급여, 부서별 급여의 최소 급여, 부서별 급여의 최대 급여를 출력하여라.
'Legend 개발자 > Oracle' 카테고리의 다른 글
No.007 서브쿼리(SUBQUERY) [2017-06-07] (0) | 2017.06.07 |
---|---|
No.006 다중 테이블로부터 데이터 검색 [2017-06-07] (0) | 2017.06.07 |
No.004 단일 행 함수 [2017-06-02] (0) | 2017.06.02 |
Oracle 연습문제 (0) | 2017.06.02 |
No.003 데이터 제한과 정렬 [2017-06-02] (0) | 2017.06.01 |