SQL 그룹함수(group by, grouping, rollup,cube)와 having

group by

group by는 해당 컬럼의 같은 값끼리 그룹화를 진행시켜주는 구문이다. 

형식은 group by 컬럼명의 형태로 적는다.

--부서별로 그룹나누기--
select  deptno from emp
group by deptno;

--직업으로 그룹화하기--
select job, count(*) as job_count from emp
group by job order by job_count;

group by를 사용할 경우, group by에 정의되지 않은 컬럼은 select 절에서 사용할 수 없다. 부서별로 그룹화했는데 사원이름인ename을 함께 출력하고자 하여 select 절에 ename을 추가하게 되면 에러가 발생한다. 

이미 그룹화를 하여 그룹별 하나의 값인 단일행으로 그룹화되었는데 사원이름은 다중행이다보니 에러가 나는것! 

 

 

집계함수와 함께 사용하기

--부서별로 그룹나누기--
select  deptno from emp
group by deptno;
--집계함수와 함께 사용하여 그룹별로 총 부서가 몇개있는지 고유값 확인하기--
select deptno, count(*) as deptno_count from emp
group by deptno ;
--부서별 최고 연봉 알아보기--
select deptno, max(sal) as max_sal from emp
group by deptno;
--부서별 평균 월급과 최저월급 알아보기--
select deptno, round(avg(sal),2), min(sal) from emp
where sal is not null
group by deptno order by deptno;


--직업으로 그룹화하기--
select job, count(*) as job_count from emp
group by job order by job_count;
--직업별 평균연봉 알아보기--
select job, round(avg(nvl(sal,0)),2) as sal_avg from emp
group by job order by sal_avg desc;

group by는 보통 이렇게 집계함수와 함께 사용한다. group by를 집계함수와 함께 사용하면 집계함수는 그룹으로 나눠진 그룹에 해당하는 값들을 계산한다.

select job, count(*) as job_count from emp group by job order by job_count;

이 경우, count는 전체 직업수가 아닌, 'CLERK'라는 직업을 가진 사원의 수를 출력한다.

select job, round(avg(nvl(sal,0)),2) as sal_avg from emp group by job order by sal_avg desc;

마찬가지로 전체 사원의 평균연봉이 아니라 직업별 평균 연봉을 계산한다.

 

 

where절 사용하여 원하는 조건의 그룹추출

--where절로 원하는 조건의 그룹만 출력하기--
select job, count(*) as job_count from emp
where job = 'CLERK'
group by job;

만약 위의 직업별로 그룹화를 한뒤, 'CLERK'의 그룹만 보고싶다면 where절을 이용하여 조건을 걸어주면 된다.

 

 

 

이중그룹화

--이중 그룹화--
select deptno, job, count(job) as job_count from emp
group by deptno, job order by deptno;

group by절에는 하나의 컬럼만 적을 수 있는것이 아니라, 여러개수의 컬럼을 적을 수 있다. 위의 쿼리문의 경우 부서별로 나누고, 그 부서안에서도 다시 직업별로 그룹을 가지게 된다. 이처럼 부서번호가 10번이면서 직업이 'CLERK'인 사원의 수를 출력할 수 있다.

 

 

group function

group by에는 위에서 사용한 단순 group화 뿐만 아니라 그룹함수를 사용하여 다양한 형태의 그룹형식으로 출력할 수 있다.

 

 

group function의 종류

• group by grouping sets
• group by rollup
• group by cube

 

grouping  sets

--group by grouping--
select deptno, job, count(*) from emp
group by grouping sets(deptno,job); --sets절안에 나열된 순서대로 그룹화--

grouping sets은 sets절안에 나열된 컬럼을 한번씩 따로 그룹화를 진행한다. 일반 이중그룹과 차이는 이중그룹은 나열된 컬럼들을 합쳐서 그룹화를 하지만 grouping set은 각각 따로 그룹화를 진행한다. 그렇기때문에 다른 컬럼이 그룹화를 진행하여 출력될 값이 없는경우는 null이 함께 출력된다. 어차피 한번씩 출력되기때문에 나열순서는 중요하지 않다.

 

 

rollup

--grouby rollup--
select deptno,job,sum(sal) from emp
group by rollup(deptno,job);
--총계값 자동 반환, 나열순서대로 a그룹, (a,b)그룹화 하기때문에 순서 중요--

rollup의 경우 a,b컬럼을 나열했다면 a그룹따로 그룹화를 하고 (a,b)컬럼을 묶어 이중 그룹화를 진행한다. 그러므로  rollup은 

나열 순서가 중요하다. 또한 rollup은 총계값을 자동반환한다. 총계값은 case when과 grouping구문으로 값을 치환하지 않는다면 자동 null의 이름으로 출력된다

*case when과 groupin으로 치환하는건 null치환함수를 한꺼번에 다룰때 정리할 예정이다..!*

 

 

cube

--group by cube--
select deptno, job from emp
group by cube(deptno, job)order by deptno;
--총계값 자동 반환--
--각각 나열된 컬럼끼리 한번씩 그룹화한다음에 전체 그룹화항 보여줌--

--cube대체--
select deptno,job from emp
group by grouping sets(deptno, job, (deptno,job),()) order by deptno;

cube는 a,b컬럼이 나열되어있다면 a,b각각 그룹화 한 후에 (a,b)컬럼을 묶어 이중 그룹화를 진행한다. 어차피 한번씩 그룹화 된 후 한꺼번에 다시 이중그룹화가 되기때문에 나열순서는 중요하지 않다. 마찬가지로 총계값이 자동 출력된다.

cube의 가장 큰 특징인 같은 그룹함수인 grouping sets절로 대체가 가능하다는것인데, ()괄호를 사용하여 튜플로 두개의 값을 묶고, 빈괄호를 적어주면 총계값과 이중 그룹화가 진행된다.

 

---

 

having

having은 집계함수를 사용하여 쿼리문을 적었을때, 집계함수의 결과로 다시 조건을 걸어 출력해야할때 사용한다.

--having--
select deptno, job, count(job) from emp
where count(job) >3
group by deptno, job order by deptno; --에러!--

select deptno, job, count(job) from emp
group by deptno, job
having count(deptno) >=2 order by deptno;
--집계함수의 결과에따른 조건을 사용하고싶을때 having절을 사용한다--

첫번째 구문과 같이 조건이라고 where절에 적으면 에러가 발생한다. 집계함수의 조건은 where절에서 정의할 수 없으며 반드시 having절에서 작성해야한다.

그럼 일반조건을 having절에 적는다면? 에러는 나지않으나 별로 추천하지 않는다..!

쿼리문에는 실행순서가 있는데, where절이 having절보다 먼저 수행되는데, 만약 having절에 일반 조건을 적는다면 이미 group by까지 진행된 쿼리를 출력했는데 having절에 나열된 일반조건때문에 다시 where절에 해당하는 순서가 다시 되돌아가 실행되어 효율적이지 않다..!

 

 

where절과 having절 모두 사용하기

--having절과 where절 함께 사용하기--
--부서와 직업별 사원수와 평균 연봉구한 뒤, 연봉이2000이상인 사원수가 2명이상인 정보 출력하기--
select deptno, job, count(*) as 사원수 from emp
where sal >=2000
group by deptno, job
having count(*)>=2 ;

이렇게 일반조건과 집계함수 조건을 각각 where절과 having절에 나눠 사용하여 조건을 구체적으로 적어준다면, 출력하고자 하는 데이터의 정보를 확실하게 출력할 수 있다.

 

---

 

학습일기

그룹함수는 학원에서 배웠을 당시에도 엄청 신기했어서 좋아했던(?) 부분이었다. 단순 그룹 함수 만으로도, 총계값과 이중그룹을 함께사용할 수 있다니..! sqld공부 하면서 다시 그룹함수가 등장했을때도 기출문제중에 그룹함수가 나오면 은근 재밌게 풀었었다. 그룹함수의 경우 총계까지 함께 반환되다보니 보통은 grouping sets보다는 rollup이나 cube를 더 많이 사용한다고 했었는데 이 점을 잘 유의해서 실무에서는 어떤식으로 그룹함수가 사용되는지를 생각하며 코드를 짜는 연습을 해야겠다고 느꼈다. 쨋든 내가 좋아하는 파트라 복습이 재밌었음..!🙆🏻‍♀️