[Oracle/SQL] 그룹쿼리와 집합연산자

Day28-3. 230608

집계함수

대상 데이터를 특정 그룹으로 묶은 다음 이 그룹에 대해 총합, 평균, 최댓값, 최솟값 등을 구하는 함수

coun(*) : null 포함한 수
coun(컬럼명) : null제외 수

 

-- 전체 사원수, 최고급여, 최저급여, 평균급여, 총급여
select count(empno),max(sal),min(sal),avg(sal),sum(sal)
from emp;

-- 업무별 사원수, 최고급여, 최저급여, 평균급여, 총급여
select job,count(empno) , max(sal) , min(sal) , avg(sal) , sum(sal)
from emp
group by job
order by max(sal) desc;

 

 

GROUP BY절과 HAVING절

group by : 그룹으로 묶을 컬럼명이나 표현식을 group by절에 명시해서 사용

where와 order by절 사이에 위치

 

-- 업무가 analyst이거나 manager에 한해
-- 업무별 사원수, 최고급여, 최저급여, 평균급여, 총급여
select job,count(empno),max(sal),min(sal),avg(sal),sum(sal)
from emp
where job='ANALYST' or job=UPPER('manager')
group by job
order by max(sal) desc;

 

having절은 group by절 다음에 위치해 group by한결과를 대상으로 다시 필터를 거는 역할.

select job,count(empno),max(sal),min(sal),avg(sal),sum(sal)
from emp
group by job
having job = 'ANALYST' or job=UPPER('manager')
order by max(sal) desc;

 

where절은 쿼리 전체에 대한 필터역할,having절은 where조건을 처리한 결과에 대해 group by를 수행 후 산출된 결과에 대해 다시 조건을 걸어 데이터를 걸러냄.->having절이 없는게 더 빠름.

-- 업무별 종사자수가 3명이상이되는 업무종사자수, 최고급여액과 최저급여액의 차를 출력
-- 업무별 종사자수가 많은 업무부터 보여주되
-- 종사자수가 동일하면 간극이 작은 업무부터 출력
select job,count(job),max(sal)-min(sal)
from emp
group by job
having count(job)>=3
order by count(job) desc, max(sal)-min(sal) asc;

결과)

select job,count(job)업무종사자수,max(sal)-min(sal)
from emp
group by job
having count(job)>=3
order by 업무종사자수 desc, 3 asc;

위와 같은 결과

 

rollup절

rollup()은 ()안에 표현식을 기준으로 집계한 결과, 즉 추가적인 집계 정보를 보여줌.

 

-- 업무별 사원수
select job,count(empno)
from emp
group by rollup(job);

결과)

cube절

rollup()이 순차적 집계했다면 cube는 명시한 표현식 개수에 따라 가능한 모든 조합별로 집계한 결과를 반환.

-- 업무별 사원수+총사원수
select job,count(empno)
from emp
group by cube(job);

결과)

cube절이 rollup()절보다 더 자세하게 나옴.

 

집합연산자

합집합 : union, union all

select deptno
from dept
union
select deptno
from emp;

union : 중복되는 것 한번만 출력

select deptno
from dept
union all
select deptno
from emp;

union all : 중복되는 것 모두 출력

교집합 : intersect

select deptno
from dept
intersect
select deptno
from emp;

차집합 : minus

select deptno
from dept
minus
select deptno
from emp;