데이터베이스 개론 & SQL - 6

쉬운코드님의 데이터베이스 개론 & SQL 강의가 어느덧 중반부를 넘어갔다. 목소리가 또랑또랑 하셔서 그런가 귀에 쏙쏙 박히는 것 같다. 고급 강의는 따로 없어서 넘 아쉬웠다... 다른 강의도 내주세요. 유튜브를 따로 하시는 것 같아서 언능 구독했다.

https://www.youtube.com/channel/UCReNwSTQ1RqDZDnG9Qz_gyg

쉬운코드

다만 근래는 영상이 없는 것 같아서 아쉽다. 시간 날 때 CS 관련 영상도 확인해볼 예정이다.

 

order by

조회 결과를 특정 attribute 기준으로 정렬하여 조회할 때 사용

default 정렬 방식은 오름차순으로, ASC로 표기하나 생략 가능하다. 내림차순은 DESC로 표기한다.

 

select * from employee order by salary;

위의 예시는 오름차순, 연봉에 따른 정렬이 된다. 만약 내림차순을 하려면 아래처럼 써주면 된다.

select * from employee order by salary desc;

 

select * from employee order by dept_id asc, salary desc;

order by 절은 여러 컬럼을 기준을 두고 정렬해줄 수 있는데, 컬럼의 순서에 따른 정렬이 순차적으로 이루어지기 때문에 컬럼의 순서도 신경 써줘야한다. 위의 예시의 경우 부서 번호로 먼저 정렬이 된 다음, 연봉 정보로 정렬이 될 것이다.

 

---

aggregate function

여러 튜플들을 정보를 요약하여 하나의 값으로 추출하는 함수를 의미한다.

대표적으로 count, sum, max, min, avg 등이 있으며 관심있는 속성에 사용된다. 다만 집합 함수의 특성상 null 값은 제외하고 결과를 반환해준다.

select count(*) from employee;

위의 예시는 count() 함수로, 매개변수로 컬럼명이 들어가게 된다. *을 사용하는 경우 null 을 포함한 테이블 튜플 수를 리턴하지만 컬럼명이 들어가면 해당 컬럼명에서 null 을 제외한 튜플 수만큼을 리턴하므로 주의해서 사용할 필요가 있다.

 

select max(salary) from employee;

당연히 max() 함수는 컬럼에 들어가는 튜플의 값들 중 제일 큰 값을 반환해준다. min() 은 반대의 의미일 것이다.

 

select count(*), max(salary), avg(salary)  from works_on wo  
join employee e on (e.id = wo.empl_id)
where wo.proj_id = 2002;

위의 예시는 프로젝트 2002번에 참여한 직원들의 수와 최대 연봉, 평균 연봉을 조회하는 쿼리문이다. inner join 으로 교집합을 구해주면 된다.

 

group by

group by 는 넣어주는 컬럼으로 그룹핑 시켜주는 명령이다. 위의 예시를 활용하여 각 프로젝트마다 참여한 직원 수와 최대 연봉, 평균 연봉을 구하는 쿼리문을 만들어보면,

select wo.proj_id, count(*), max(salary), avg(salary)  from works_on wo  
join employee e on (e.id = wo.empl_id)
group by wo.proj_id ;

이렇게 될 것이다. 쿼리문이 길어질수록 순서부분에서 많은 어려움들을 겪을 것이다.

차근히 위 쿼리문의 순서를 되짚어보자. 우선 가장 먼저 from절부터 확인해준다. 기준이 되는 테이블과 join 되는 테이블이 합쳐진 결과를 토대로 group by로 묶어준다. 그 다음 select 절 이후의 컬럼들로 분리하여 결과를 추론해준다.

즉 group by는 관심있는 attribute 기준으로 그룹을 나눠서 그룹별로 aggregate function 을 사용하고자 할 때 사용한다.

 

having

group by 의 조건을 추가로 명시해줄 수 있는 명령어로, aggreate function 의 결과 값을 바탕으로 조건을 추가해줄 수 있다. 위의 예시에서 부서명이 5명 이상의 값을 구하고자 할 때 아래처럼 having 키워드를 사용하여 작성해주면 된다.

select wo.proj_id , count(*), max(salary), avg(salary)  from works_on wo  
join employee e on (e.id = wo.empl_id)
group by wo.proj_id 
having count(*) >= 5;

having 절은 당연하게도 group by 와 함께 사용될수밖에 없으므로 따로 사용이 불가하다.

 

select dept_id, count(*) as em_cnt from employee
group by dept_id
order by em_cnt desc;

 

위의 예시는 부서별로 몇 명이 있는지, 많은 직원이 있는 부서순으로 조회한 것이다. 그렇다면 부서별, 성별별 인원수를 조회하고자 한다면? 그룹핑 조건이 2개가 되었더라도 당황하지 말고 group by 뒤 컬럼을 나열해주자. 그런다음 select 절 다음의 컬럼에도 추가해주면 아래처럼 될 것이다.

select dept_id, sex, count(*) as em_cnt
from employee
group by dept_id, sex
order by em_cnt desc;

 

회사 전체 연봉 평균과 부서별 평균 연봉을 비교하여 부서별 평균 연봉이 전체 연봉 평균보다 작은 부서의 평균 연봉을 조회하고자할 때는 having 을 사용한다.

select avg(salary) as tot_sal, dept_id
from employee
group by dept_id 
having tot_sal > (
	select avg(salary) from employee
);

 

select wo.proj_id, count(*), round(avg(e.salary),0)
from works_on wo join employee e on(wo.empl_id=e.id)
where e.birth_date between '1990-01-01' and '1999-12-31'
group by wo.proj_id;

위 쿼리문은 1990 년대 생들을 각 프로젝트 별로 나누고 그 수와 평균 연봉을 구하는 쿼리문이다.

여기에 추가로 프로젝트 참여 인원이 5명 이상인 프로젝트에 한정한다면? 간단하게 having 절로 count 추가해주면 된다고 생각할수 있지만 90년대생 프로젝트 참여 인원이 5명 이상이 아닌, 프로젝트 전체 인원에서 5명 이상을 의미하는 것이므로 아래와 같이 서브쿼리가 추가된다.

select wo.proj_id, count(*), round(avg(e.salary),0)
from works_on wo join employee e on(wo.empl_id=e.id)
where wo.proj_id in (
	select proj_id from works_on
	group by proj_id
	having count(*) >= 5
) and e.birth_date between '1990-01-01' and '1999-12-31'
group by wo.proj_id
order by wo.proj_id;

 

select 문 정리

select attribute/aggregate function
from table
where condition
group by group attribute
having group condition
order by attribute

select 문의 개념적 실행 순서: from( + join) -> where -> group by -> having -> order by ->  select 

다만 실제 실행 순서는 각 RDBMS에서 어떻게 구현했는가에 따라 다르게 된다. (각 RDBMS 옵티마이져에 따라 다름)