[Oracle] 그룹 함수

그룹 함수

함수	설명
MAX(컬럼)	컬럼 값 중에 최대 값을 반환
MIN(컬럼)	컬럼 값 중에 최소 값을 반환
AVG(컬럼)	평균 값을 반환
COUNT(컬럼 | *)	NULL이 아닌 행의 수를 반환 | *는 모든 행의 수
SUM(컬럼)	컬럼의 합계
STDDEV(컬럼)	표준 편차를 반환
VARIANCE(컬럼)	분산을 반환

- 그룹 함수에서 NULL은 무시된다.
- GROUP BY 절 없이 일반 컬럼과 같이 기술 될 수 없다.

//사원의 평균 급여를 검색한다.
SELECT AVG(sal) 평균급여, TO_CHAR(AVG(sal),'$999,999') 평균급여
FROM emp;

//월간 지급 급여 총액과 년간 지급 급여 총액을 검색한다.
SELECT SUM(sal), TO_CHAR(SUM(sal),'$999,999') 월간_급여_총액,
       SUM(sal)*12, TO_CHAR(SUM(sal)*12,'$999,999') 년급여_총액
FROM emp;

//사원에게 지급된 보너스(NULL값 존재) 지급 총액과 보너스 평균을 검색한다.
SELECT SUM(comm) 총액, TO_CHAR(AVG(comm),'$999,999') 보너스_평균
FROM emp;

SELECT SUM(comm) 총액,
       COUNT(comm) 수령인원, TO_CHAR(AVG(comm),'999,999') 평균,
       COUNT(*) 전체인원, TO_CHAR(AVG(NVL(comm,0)),'999,999') 평균
FROM emp;
-- DB에 저장된 NULL은 무결성 문제를 유발한다.
-- 개발자 보다는 설계자나 운영자의 문제일 가능성이 높다.

//10번 부서원들보다 급여가 높은 사원을 검색한다
SELECT eno, ename, sal, dno
FROM emp
WHERE sal > SELECT MAX(sal) FROM emp WHERE dno=10
-- 가독성이 더 좋음

SELECT eno 사번, ename 이름, dno 부서번호
FROM emp
WHERE sal > ALL(SELECT sal
                FROM emp
                WHERE dno = '10')

 

GROUP BY

SELECT ...
FROM  테이블 ...
WHERE 조건 ...
GROUP BY 컬럼
ORDER BY 정렬_대상 ... ;

‐ GROUP BY 절은 컬럼의 동일한 값을 바탕으로 소그룹에 대한 통계 정보를 검색한다. 
SELECT 절에 그룹 함수와 같이 쓰인 일반 컬럼이 GROUP BY 절에 기술되지 않으면
카디널리티(cardinality)가 일치하지 않아 ‘ORA-00937: not a single-group group 
function’에러가 발생한다.
‐ 결과 값이 정렬되길 원한다면 반드시 ORDER BY절을 추가한다.

//업무별 평균 급여, 평균 연봉을 검색한다.
SELECT job 업무, TO_CHAR(AVG(sal),'999,999') 평균_급여,
       TO_CHAR(AVG(sal*12+NVL(comm,0)), '999,999') 평균_연봉
FROM emp
GROUP BY job;

//부서별 평균 급여, 평균 연봉을 검색한다.
SQL> SELECT d.dno 부서번호, dname 부서,TO_CHAR(AVG(sal),'999,999') 평균_급여,
       TO_CHAR(AVG(sal*12+NVL(comm,0)),'999,999') 평균_연봉
FROM dept d, emp e
WHERE d.dno = e.dno
GROUP BY d.dno, dname
ORDER BY d.dno;

카디널리티(Cardinality) 와 정렬

• 카디널리티

‐ 값의 개수(기수)를 의미한다.
‐ SELECT 문의 각 컬럼은 반드시 카디널리티가 같아야한다.
‐ '그룹 함수와 같이 검색되는 모든 컬럼은 반드시 GROUP BY절에 기술한다.' 이것이 원칙이다.
   - GROUP BY 절에 기술되어도 그룹 함수와 같이 검색되지 않는 경우도 있다.
   -카디널리티가 일치하는 것이 중요하다.

• 정렬

‐ GROUP BY 절은 정렬을 수행한다.
‐ 오라클은 해시 알고리즘을 이용함으로 정렬되지 않는다.
‐ 정렬된 결과를 위해서는 반드시 ORDER BY 절을 이용한다.

//각 부서별 급여 평균이 가장 높은 값과 낮은 값을 검색한다.
SELECT dno 부서번호,
       MAX(AVG(sal)) 최대평균, MIN(AVG(sal)) 최소평균
FROM emp
GROUP BY dno;

SELECT MAX(AVG(sal)) 최대평균, MIN(AVG(sal)) 최소평균
FROM emp
GROUP BY dno --오류

//각 부서별 최소 급여를 받는 사원의 정보를 검색한다
SELECT d.dno, dname, eno, ename, sal
FROM emp e, dept d
WHERE d.dno=e.dno
AND (d.dno, sal) IN (SELECT dno, MIN(sal)
                     FROM emp
                     GROUP BY dno)
ORDER BY d.dno;

그룹 함수 ORDER BY 예제

1. 각 학과별 학생 수를 검색한다.
SELECT major 학과, COUNT(sno) 학생수
FROM student
GROUP BY major
ORDER BY major;


2. 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색한다.
SELECT major 학과, TO_CHAR(AVG(avr*1.125),'9.99') 평균
FROM student
WHERE major='화학' OR major='생물'
GROUP BY major
ORDER BY major;

3. 부임일이 10년 이상 된 직급별(정교수, 조교수, 부교수) 교수의 수를 검색한다.
SELECT orders 직급, COUNT(pno) 교수의 수
FROM professor
WHERE TRUNC(hiredate) < TRUNC(ADD_MONTHS(sysdate,-120))
GROUP BY orders
ORDER BY orders;

4. 과목명에 화학이 포함된 과목의 학점수 총합을 검색한다.
SELECT '화학이 포함된 과목의 학점수의 총합: '||SUM(st_num) 합계
FROM course
WHERE cname LIKE '%화학%';

5. 화학과 학생들의 기말고사 성적을 성적순으로 검색한다.
SELECT major 학과, s.sno 학번, sname 이름,TO_CHAR(AVG(result),'999') 기말고사_성적
FROM student st, score s
WHERE st.sno=s.sno AND major='화학'
GROUP BY s.sno,major,sname
ORDER BY AVG(result) DESC;

6. 학과별 기말고사 평균을 성적순으로 검색한다.
SELECT major,TO_CHAR(AVG(result),'999')
FROM student st, score s
WHERE st.sno=s.sno
GROUP BY major
ORDER BY AVG(result) DESC;

7. 30번 부서의 업무별 연봉의 평균을 검색한다.
‐ 단 출력 양식은 소수이하 두 자리까지 통일된 형식으로 출력한다.
SELECT job 업무, TO_CHAR(AVG(sal),'$9999.99') 평균_급여
FROM emp
WHERE dno='30'
GROUP BY job
ORDER BY job;

8. 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색한다. 
SELECT major, syear, MAX(avr)
FROM student
WHERE major='물리'
GROUP BY major,syear
ORDER BY syear;

9. 학년별로 환산 평점의 평균값을 검색한다.
‐ 단 출력 양식은 소수이하 두 자리까지 통일된 양식으로 출력한다.
SELECT syear 학년, TO_CHAR(AVG(avr*1.125),'99.99') 평균
FROM student
GROUP BY syear
ORDER BY syear;

10. 화학과 1학년 학생 중 평점이 평균 이하인 학생을 검색한다.
SELECT major,sno, sname, syear, avr
FROM student
WHERE major='화학' AND syear=1
AND avr <= (SELECT AVG(avr) FROM student WHERE major='화학' AND syear=1);

 

그룹 함수 조건 (HAVING)

SELECT ...
FROM  테이블 ...
WHERE 조건 ...
GROUP BY 컬럼
HAVING [그룹조건] ...
ORDER BY 정렬_대상 ... ;
  - HAVING : 조건 중에 그룹 함수를 포함하는 조건을 기술한다. (그룹핑된 조건을 기술한다.)
  ‐ HAVING절은 그룹된 결과에 대한 조건이므로 가능한 GROUP BY절 뒤에 기술하는 것을 권한다.

SELECT ROUND(AVG(sal))
FROM emp
GROUP BY dno;

SELECT ROUND(AVG(sal))
FROM emp
GROUP BY job;
‐ 동일 SELECT 절(그룹 함수)에서도 GROUP BY 절의 그룹 대상에 따라 결과가 달라진다.
‐ HAVING절은 그룹된 대상에 대한 조건이다.(SELECT 절과는 무관하다)

// 부서별 급여 평균이 3천 달러 미만인 부서의 평균 급여를 검색한다.
SELECT dno 부서번호, TO_CHAR(AVG(sal),'$999,999') 평균_급여
FROM emp
GROUP BY dno
HAVING AVG(sal) < 3000

// 부서별 급여 평균이 3천 달러 미만인 부서의 최대 급여를 검색한다.
SELECT dno 부서번호, TO_CHAR(MAX(sal),'$999,999') 최대_급여
FROM emp
GROUP BY dno
HAVING AVG(sal) < 3000

 

그룹 함수 HAVING 예제

1. 화학과를 제외하고 학과별로 학생들의 평점 평균을 검색한다.
SELECT major, TO_CHAR(AVG(avr),'99.99') 평균
FROM student
GROUP BY major
HAVING major!='화학'
ORDER BY AVG(avr) DESC;

2. 화학과를 제외한 각 학과별 평균 평점 중에 평점이 2.0 이상인 정보를 검색한다. 
SELECT major, TO_CHAR(AVG(avr),'99.99') 평균
FROM student
GROUP BY major
HAVING major!='화학' AND AVG(avr)>=2.0
ORDER BY AVG(avr) DESC;

3. 기말고사 평균이 60점 이상인 학생의 정보를 검색한다.(학번과 기말고사 평균)
SELECT s.sno, AVG(result) 평균
FROM student st, score s 
WHERE st.sno=s.sno
GROUP BY s.sno
HAVING AVG(result)>=60
ORDER BY AVG(result) DESC;

4. 강의 학점수가 3학점 이상인 교수의 정보를 검색한다.(교수번호, 이름과 담당 학점수)
SELECT p.pno, pname, MAX(st_num)
FROM professor p, course c
WHERE p.pno=c.pno
GROUP BY c.pno, p.pno, pname
HAVING MAX(st_num) >= 3
ORDER BY MAX(st_num) DESC;

5. 기말고사 성적이 핵 화학과목보다 우수한 과목의 과목명과 담당 교수명 검색한다.
SELECT s.cno, cname, p.pno, pname, TO_CHAR(AVG(result),'99.99') 평균
FROM score s, course c, professor p
WHERE p.pno=c.pno AND c.cno=s.cno
GROUP BY s.cno, cname, p.pno, pname 
HAVING AVG(result) > (SELECT AVG(result)
                      FROM course c, score s
                      WHERE c.cno=s.cno
                      AND cname = '핵화학'
                      GROUP BY c.cno)
ORDER BY AVG(result) DESC;

-- 서브쿼리 이용한 풀이
SELECT cno, cname, p.pno, pname
FROM course c, professor p
WHERE c.pno=p.pno 
AND cno IN (SELECT cno
		    FROM score
            GROUP BY cno
            HAVING AVG(result) > (SELECT AVG(result)
            					  FROM course c, score s
                                  WHERE c.cno=s.cno
                                  AND cname = '핵화학'
                                  GROUP BY c.cno))
ORDER BY c.cno;

6. 근무 중인 직원이 4명 이상인 부서를 검색한다.
SELECT dno, COUNT(eno) 직원 수
FROM emp
GROUP BY dno
HAVING COUNT(eno) >=4
ORDER BY COUNT(eno) DESC;

7. 업무별 평균 년봉이 2만불 이상인 업무를 검색한다.
SELECT job, TO_CHAR(AVG(sal*12),'$999,999.0') 평균연봉
FROM emp
GROUP BY job
HAVING AVG(sal*12) > 20000
ORDER BY AVG(sal*12);

8. 각 학과의 학년별 인원중 인원이 5명 이상인 학년을 검색한다.
SELECT major, syear, COUNT(sno)
FROM student
GROUP BY major, syear
HAVING COUNT(sno) >= 5
ORDER BY major, COUNT(sno) DESC;

9. 인원수가 가장 많은 학과를 검색한다.
SELECT major, COUNT(sno)
FROM student
GROUP BY major
HAVING COUNT(sno) = (SELECT MAX(COUNT(sno)) FROM student GROUP BY major);

10. 학생 중 기말고사 성적이 가장 낮은 학생의 정보를 검색한다.
SELECT s.sno, sname, syear, major, TO_CHAR(AVG(result),'999')
FROM student st, score s
WHERE st.sno=s.sno
GROUP BY s.sno, sname, syear, major
HAVING AVG(result) = (SELECT MIN(AVG(result)) FROM score GROUP BY sno);