[SQL] group by

sql을 이용해서 데이터를 추출할 때 group by는 필수로 사용된다.

이 때 group by 절에 사용된 column이 pk가 되고, group by 에 쓰이지 않은 column은 select에 올 수 없다.(집계 함수에 사용되는 column 제외)

이 형태의 스키마를 가진 데이터가 있을 때 아래 문제들을 풀어보자.

1. emp 테이블에서 부서별(deptno) 평균 급여(sal)를 구해보자.

   select deptno, avg(sal) as avg_sal
   from emp e
   group by e.deptno;

   위 코드처럼 group by를 이용해서 간단하게 구할 수 있다.

2. emp 테이블에서 부서별 평균 급여가 2000 이상 경우만 구해보자.

   select deptno, avg(sal) as avg_sal
   from emp e
   group by e.deptno
   having avg(sal) >= 2000;

   또는 with 구문을 이용하여

   with avgsal as (
   	select deptno, avg(sal) as avg_sal
   	from emp e
   	group by e.deptno)
   select *
   from avgsal
   where avg_sal >= 2000;

이렇게 구할 수 있다. (사실 with 구문은 아직까지 왜 쓰는지 잘 모르겠음)

1. SALES와 RESEARCH 부서 소속 직원별 과거부터 현재까지의 평균 급여

   select esh.empno, avg(esh.sal) as avg_sal
   from dept d
   	join emp e on d.deptno = e.deptno
   	join emp_salary_hist esh on e.empno = esh.empno
   where d.dname in ('SALES','RESEARCH')
   group by esh.empno;

   이렇게 풀면 된다. 여기서 부서 명 dname 을 넣어주고 싶다고 그냥 select에 d.dname을 추가하면 오류가 발생한다.

   왜냐면 d.dname은 group by절에 사용하지 않았기 때문!!

   그래서 dname을 같이 보고 싶다면 집계 함수를 사용해줘야 한다.

   select esh.empno, avg(esh.sal) as avg_sal, max(d.dname) as dname
   from dept d
   	join emp e on d.deptno = e.deptno
   	join emp_salary_hist esh on e.empno = esh.empno
   where d.dname in ('SALES','RESEARCH')
   group by esh.empno;

   위 코드처럼 max를 이용해서 dname을 같이 볼 수 있다.

3번 문제를 풀 때 join을 연속 두 번 사용할 수 있다는 걸 까먹고, 쓸데없이 with 구문 이용해본다고 되게 이상하게 풀었다.

• 내가 처음 푼 방법

  with sr as (
  select e.*, d.dname 
  from hr.emp e 
  left join hr.dept d on e.deptno = d.deptno
  where d.dname in ('SALES','RESEARCH')
  )
  select esh.empno, avg(esh.sal)
  from hr.emp_salary_hist esh
  inner join sr on sr.empno = esh.empno 
  group by esh.empno;

ㅎ 그래도 답은 나왔음 ..

• with 구문을 이용한 다른 방법

  with sr as (
  	select d.dname as dname, esh.empno, esh.sal as sal
  	from dept d
  		join emp e on d.deptno = e.deptno
  		join emp_salary_hist esh on e.empno = esh.empno
  	where d.dname in ('SALES','RESEARCH')
  )
  select empno, avg(sal), max(dname)
  from sr
  group by empno;

뭐~ 대충 비슷한 거같기도?ㅋ