DML: SQL 연산자와 내장함수(2)

- select

- from

- where

- group by

- having

- order by

 

group by

 

 

전체 집계

: 5글자보다 긴 이름을 가진 학과에 소속된 교수들의 평균 급여 출력

select avg(salary) as avg_sal // salary열의 전체 데이터 대상 평균
from instructor
where dept_name like '______%'; //'_' 6개 => 6글자 이상이어야!

전체 평균 출력

 

그룹별 집계

: 5글자보다 긴 이름을 가진 학과들에 대하여 각 학과별 교수들의 평균 급여 출력

select dept_name, avg(salary) as avg_sal
from instructor
where length(dept_name) > 5 // 5글자보다 글자 수 많다는 뜻
group by dept_name; // dept_name 을 기준으로 그루핑해라

그룹별로 평균 출력

having

 

: group by한 후 보고싶은 그룹만 필터링해서 봄

: 집계 함수 사용 가능(<-> where 절에서는 사용 불가)

* 집계함수: count, avg, sum, min, max

 

다음 두 가지 코드는 똑같이 위 결과를 가져온다. 하지만 과정이 다르다.

 

(1) where절 있을 때

=> 성능 더 좋음(where 절에서 1차로 걸러주기 때문에)

select dept_name, avg(salary) as avg_sal
from instructor
where length(dept_name) > 5
group by dept_name
having avg(salary) > 80000;

 

(2) where 절 없을 때

=> 집계함수를 사용해야 할 때(where 절에는 집계함수 사용 불가)

select dept_name, avg(salary) as avg_sal
from instructor
group by dept_name
having length(dept_name) > 5 and avg(salary) > 80000;

 

 

 

 

<예제1>

Q) 5글자보다 긴 이름을 가진 학과들 중에서 소속 교수가 2명 이상인 학과에 대해서 각 학과 별 교수들의 평균 급여 출력

 

A) 5글자보다 긴 이름을 가진 학과들 중(WHERE)

소속 교수가 2명인 학과(COUNT 이므로 Having 절)

각 학과 별 (GROUP BY)

교수들의 평균 급여 출력(SELECT)

select dept_name avg(salary) as avg_sal
from instructor
where length(dept_name) > 5
group by dept_name
having count(*) > 1;

 

 

파란색 소그룹만이 평균 출력 대상이 된다.

 

<예제2>

Q) 5글자보다 긴 이름을 가진 학과들 중에서 소속 교수가 2명 이상이고, 급여를 가장 많이 받는 교수가 90000보다 큰 금액을 받는 학과에 대해서, 각 학과 별 교수들의 평균 급여 출력

 

A) 5글자보다 긴 이름을 가진 학과들 중(WHERE)

소속 교수가 2명 이상(COUNT 집계함수이므로 HAVING)

급여를 가장 많이 받는 교수가 90000보다 큰 금액을 받는(MAX 집계함수이므로 HAVING)

각 학과 별(GROUP BY)

교수들의 평균 급여 출력(SELECT)

select dept_name, avg(salary) as avg_sal
from instructor
where length(dept_name) > 5
group by dept_name
having max(salary) > 90000 and count(*) > 1;

 

<예제3>

Q) 컴퓨터공학과 교수들의 평균 급여 출력

 

A) 컴퓨터공학과 교수들(WHERE)

평균 급여 출력(SELECT)

select avg(salary)
from instructor
where dept_name = 'Comp. Sci.';

 

having을 사용할 수도 있지만 집계함수가 아니기 때문에 where을 사용해 앞에서 빨리 거르는 것이 성능상 낫다.

select avg(salary) as avg_sal
from instructor
group by dept_name
having dept_name = 'Comp. Sci.';

 

‼️group by 쓸 때 주의사항‼️

 

select 집계함수가 아닌 컬럼이 있다면, 그 컬럼은 group by 에도 적어줘야 한다 !!!

∵ group by 할 때 상관 없는 컬럼은 지워지기 때문에 select에서 추출할 수 없기 때문.

 

<오류>

select year, semester, count(course_id) as num_course
from teaches
group by year; //semester 컬럼은 없어짐

 

<정상>

select year, semester, count(course_id) as num_course
from teaches
group by year, semester;

 

=> 무엇을 select할 것인지에 따라 group by에도 제약을 받기 때문에 grouping 기준을 신중하게 선정해야 한다.

 

 

ORDER BY

 

- 디폴트: 오름차순

- desc: 내림차순

select name, dept_name
from instructor
order by name; // name abc 오름차순으로 정리

select name, dept_name
from instructor
order by dept_name, name desc // dept_name은 오름차순, name은 내림차순, 단 앞에 나온 dept_name 먼저

=> dept_name이 abc 순서로 정리된 다음 각 그룹들 안에서 name 내림차순

 

 

GROUP BY 절이 없을 때

 

‼️SELECT는 GROUP BY와 달리 기준으로 선택되지 않은 컬럼이라도 memory에서 없어지지 않음‼️

=> SELECT 절에 등장하지 않은 단일 컬럼도 ORDER BY 정렬 기준으로 사용 가능

select name, dept_name
from instructor
order by salary;

salary 컬럼이 사라지지 않는다는 것이다. 

 

GROUP BY 절과 함께 사용할 때-중요

 

GROUP BY와 함께 사용한다면 등장하지 않은 것은 없어지기 때문에,

ORDER BY 절에서 정렬 기준으로 삼고자 하는 단일 컬럼은 반드시 GROUP BY 절에도 등장해야 한다.

 

<오류>

select dept_name, avg(salary) as avg_sal
from instructor
group by dept_name
order by name; // group by에 name이라는 컬럼이 없었고 select 문의 집계함수도 아니기 때문에 테이블 사라짐->오류

 

<정상>

select dept_name, avg(salary) as avg_sal
from instructor
group by dept_name
order by dept_name; //group by에 있는 컬럼만 사용 가능

 

==> group by에도 없고 select의 집계함수에도 없는 컬럼을 order by 기준으로 쓴다면 오류

 

<문법상 오류 없지만 의미상 무의미>

group by에 name 을 추가하면 문법상 오류는 없어진다. 하지만 전혀 다른 결과가 나오기 때문에 무의미하다.

select dept_name, avg(salary) as avg_sal
from instructor
group by dept_name, name //name 추가해서 오류 해결했지만 의미없는 집계
order by name;