[Oracle] 2020.12.18. day_40 집계함수(그룹함수), rollup, cube

 

○ 집계함수(그룹함수)

• 컬럼 중 같은 값을 묶어서 하나로 만드는 형식
• group by와 같이 사용하면 그룹별 집계를 얻을 수 있다
• where절에서 사용할 수 없다

함수명	기능	사용법
where절에서 사용할 수 없다
count	조회된 레코드의 수를 반환하는 함수 (레코드의 수) null을 세지 않는다	count(컬럼명) 데이터형 모두 다
sum	컬럼의 값을 합쳐 반환하는 함수	sum(컬럼명)
avg	컬럼에 존재하는 모든 레코드의 평균 값을 반환하는 함수 평균 값이 숫자이기 때문에 매개변수도 숫자형만 가능하다	avg(컬럼명) 데이터형 숫자만
max	컬럼에 존재하는 값 중 가장 큰 값을 반환하는 함수	max(컬럼명)
min	컬럼에 존재하는 값 중 가장 작은 값을 반환하는 함수	min(컬럼명)

○ 집계함수(그룹함수) 연습해보기

함수명	예시코드	실행
사원테이블에서 모든 사원수, 보너스를 수령하는 사원수, 수령하지 않는 사원수  조회
count	select count(empno) , count(comm),  count(empno)-count(comm) from  emp;
사원테이블에서 연봉의 합
sum	select sum(sal) from emp;
사원테이블에서 연봉의 평균
avg	select trunc(avg(sal)) from emp;
사원테이블에서 최고연봉
max	select max(sal) from emp;
사원테이블에서 최저연봉
min	select min(sal) from emp;
집계함수는 여러행이 조회되는 컬럼과 함께 사용되면 Error발생 사원테이블에서 최고연봉 (max), 최고연봉자 조회_sub query 배우고 사용가능해짐
Error		최고연봉은 1행,  사원명은 n행이 조회되기 때문에 Error발생
집계함수는 where절에 사용할 수 없다 Error발생 사원테이블에서 평균연봉보다 많이 받는 사원의 사원번호, 사원명, 연봉, 입사일 조회
Error		집계함수는 where절에 사용할 수 없다 Error발생 sub query 배우고 사용가능해짐
사원테이블에서 부서별인원이 3명 이상인 부서의   사원번호, 사원명, 연봉, 입사일, 부서번호  조회
Error		집계함수는 where절에 사용할 수 없다 Error발생 sub query 배우고 사용가능해짐

---

○ 집계함수와 group by절을 사용해보기(그룹별 집계얻기)

- group by : 같은 값이 묶여서 나온다

함수명	예시코드	실행
사원테이블에서  부서번호, 부서별 인원수, 부서별 연봉, 부서별 평균연봉 합 , 부서별 최고연봉액, 부서별 최저연봉액 조회
group by + count	select       deptno,   count(ename),   sum(sal),   trunc(avg(sal)),   max(sal),   min(sal)  from emp group by deptno;
사원테이블에서 매니저번호, 매니저별 담당사원수, 최고연봉을 조회 단, 매니저가 존재하는 사원만 출력
group by  + where + count, max	select    mgr, count(ename), max(sal) from emp where mgr is not null group by mgr; where절과도 함께 쓸 수 있다

○ 집계함수와 having절을 사용해보기

함수명	예시코드	실행
사원테이블에서 사원수가 4명이상인 부서의 부서번호, 부서별사원수, 연봏합 조회
group by + having + count, sum	select    deptno , count(empno) , sum(sal) from      emp group by  deptno having    count(empno) >= 4;
사원테이블에서 매니저가 관리하는 사원이 2명 이상인 매니저의 매니저번호, 관리사원수, 평균연봉, 최고연봉과 최저연봉차를 조회
group by + having + count, avg, max, min	select     mgr,  count(ename),  trunc(avg(sal)),  max(sal)-min(sal) from   emp group by  mgr having    count(mgr) >= 2;

---

○ 집계의 총 집계를 얻을 때 사용하는 rollup, cube

함수명	기능	사용법
rollup	집계 후 전체결과를 출력	group by rollup(그룹으로 묶일 컬럼)
cube	전체결과 후 집계를 출력	group by cube(그룹으로 묶일 컬럼)

	rollup, cube 연습해보기
사원테이블에서 부서별 부서번호, 부서별 사원수, 연봉합, 전체연봉합 조회
rollup	select    deptno, count(*), sum(sal) from     emp group by  rollup(deptno) 컬럼의 구성을 잘 모를 때에는, *로 count를 하면  알아서 null이 아닌 컬럼수를 반환한다	합산결과를 나중에 출력
cube	select    deptno, count(*), sum(sal) from     emp group by  cube(deptno);	합산결과를 먼저 출력
여러 컬럼이 그룹으로 묶이면 그룹별 결과, 중간결과, 총합결과를 출력한다 부서에 해당하는 직무별분류, 해당직무 사원수를 조회
rollup 여러 컬럼을 그룹으로 묶기	select deptno, job, count(job) from     emp group by  rollup(deptno, job);
cube 여러 컬럼을 그룹으로 묶기	select deptno, job, count(job) from     emp group by  cube(deptno, job); 전체 합산-직무별합산-하위합산