MySQL 데이터베이스 이해하기 / 집계함수와 그룹함수

집계함수(aggregation), 그룹함수(group)함수 

지정한 group에 함수 기능 처리한 결과가 1개가 조회된다. 
group by로 그룹을 지정하지 않으면 전체 데이타가 한개의 group으로 지정된다. 
주의) group의 수와 다른 데이터를 같이 조회하면 mysql은 의미 없는 결과가 나오지만 
         oracle, db2,... 다른 DB는 error가 발생한다. 

 

- 집계함수(all | distinct 컬럼명)
all 모든 값을 처리 ==> 생략시 기본적으로 all로 처리된다.
distinct 중복된 값을 제거하고 처리

 

 - 함수 종류 
min, max, sum, avg : null을 제외하고 처리한다. 
count(*)    : 해당 테이블의 모든 행의 객수를 count한다. 
count(컬럼명) : null이 아닌 행의 개수를 count한다.

 

* count 

select comm from emp;

-- 전체 직원수를 조회
select count(*)from emp;

-- 커미션을 받는 사원 수를 조회
select count(comm)from emp;

 

* 최소 습여, 최대 급여 , 평균 급여 조회

select min(sal), max(sal), avg(sal)
from emp;

 

group by 

데이타를 특정 컬럼의 데이타 종류를 기준으로 group 으로 데이타를 분류한다. 
group by와 함께 집계함수를 사용하면 집계함수의 결과는 group개수로 조회된다.

 

-- 부서별 인원수와, 평균 급여를 조회 
select DEPTNO, count(*), avg(sal)
from emp
group by DEPTNO
order by DEPTNO;

 

-- 부사별 업부별 인원수와 평균 급여를 조회
select DEPTNO, JOB , count(*) as 근무인원 , round(avg(sal),2) as cal
from emp
group by DEPTNO , JOB -- 1차 부서별 2차 직업별 그룹
order by DEPTNO, JOB ;

 

 

*

  - where => 행 하나 하나에 대한 조건 처리  

 - having => group 집계한 결과에 대한 조건은 having절에서 처리

having

     group 집계한 결과에 대한 조건은 having절에서 한다. 
     형식]  
     select   집계함수
     from     테이블
     where    레코드 각각에 대한 조건
     group by 컬럼(알리아스), ...
     having   집계한 결과에 대한 조건(알리아스)

 

-- 부서별 평균 급여가 2000이상인 부서를 조회
select DEPTNO, avg(sal) as AvgSal
from emp
group by DEPTNO having avg(sal) >= 2000;

rollup

group별 통계에  전체 통계를 추가로 조회 
형식] group by 컬럼명 with rollup;

 

- group으로 나눈 전단계에 대한 집계

rollup( job) =>  job / job 집계

rollup( job ,sal ) => ( job ,sal)  / ( job ,sal) 집계 / job / job 집계

 

* 전체 집계

cube( job ,sal) => ( job ,sal)  / ( job ,sal) 집계 / job / job 집계 / sal / sal 집계 => oracle만 지원

-- 업무별 근무 인원, 급여 평균과, 최소 급여, 최대 급여를 조회

SELECT 
    IFNULL(job, '프리랜서') AS job, 
    COUNT(*) AS '근무 인원', 
    ROUND(AVG(sal)) AS AvgSal, 
    MIN(sal) AS MinSal, 
    MAX(sal) AS MaxSal
FROM emp
GROUP BY job WITH ROLLUP;

//오라클의 경우
//GROUP BY rollup(job);

 

=> 이렇게 하면 통계 값도 프리랜서가 됌 =>grouping 사용이 필요 !!

grouping()

  rollup에 의해 조회된 데이타는 1 그렇지 않은 데이타는 0이 조회된다. 

 

SELECT 
    IF(GROUPING(job) = 1, 'total', IFNULL(job, '프리랜서')) AS job, 
    COUNT(*) AS '근무 인원', 
    ROUND(AVG(sal)) AS AvgSal, 
    MIN(sal) AS MinSal, 
    MAX(sal) AS MaxSal
FROM emp
GROUP BY job WITH ROLLUP;

// 오라클은 if가 아닌 case로 표현