[DB] SQL - 집계함수, group by, order by 공부하기

SQL - 집계함수, group by, order by 알기

 

ORDER BY

-특정 속성(attribute)를 기준으로 정렬해서 가져오고 싶을때 사용

-Default 정렬방식은 오름차순

-오름차순 ASC로 표기

-내림차순 DESC로 표기

 

집계함수(aggregate function)

-여러 튜플(데이터, 행)들의 정보를 요약해서 하나의 값으로 추출하는 함수

-대표적으로COUNT,SUM,MAX,MIN,AVG 함수가 있다

-(주로) 관심있는 속성에 사용된다.(AVG(salary), MAX(birth_date), COUNT(sales))

-*NULL값들은 제외하고 요약값을 추출한다

 

Q) 임직원 수를 알고싶다.

SELECT COUNT(*)
FROM employee

COUNT()의 *은 튜플을 의미한다

 

왜 count(salary)등을 쓰지않을까?

집계함수는 NULL을 무시하고 계산하기 때문에 특정 속성에 NULL값이 있는 경우 COUNT()로 임직원의 수(튜플)를 제대로 셀 수 없다.

 

 

Q) 프로젝트 2002에 참여한 임직원 수와 연봉의 최대, 최소, 평균값을 알고싶다.

SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W
JOIN employee E
ON W.empl_id = E.id
WHERE W.proj_id =2002 ;

그외 집계함수는 관심이 있는 속성에 쓴다.

연봉의 최대,최소,평균값에 관심있으므로 MAX(salary), MIN(salary), AVG(salary) 와 같이 Salary 속성에 사용한다.

 

Q) 2002프로젝트가 아니라 각 프로젝트에 대해 임직원수, 연봉의 최대, 최소, 평균값을 알고싶다면?

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W
JOIN employee E
ON W.empl_id = E.id
GROUP BY W.proj_id;

GROUP BY에 proj_id를 추가하여 프로젝트별로 데이터를 그룹화하여 집계하였다.

그런데 집계된 데이터를 보여줄때 어떤 그룹에 대한 정보인 지를 나타내기 위해 GROUP BY에 쓴 proj_id를 SELECT 절에도 써줘야한다.

 

GROUP BY

관심있는 속성(attribute)을 기준으로 그룹을 나눠서 그룹별로 집계함수를 적용하고 싶을 때 사용한다

Grouping attribute: 그룹을 나누는 기준이되는 attribute

Grouping attribute에 NULL값이있을 때는 NULL값을 가지는 튜플끼리 묶인다

 

Q) 프로젝트 참여인원이 7명이상인 프로젝트에 대해서만 각 프로젝트에 참여한 임직원수와 연봉의 최대,최소,평균값을 알고싶다면?

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W
JOIN employee E
ON W.empl_id = E.id
GROUP BY W.proj_id
HAVING COUNT(*) >= 7;

HAVING에 그룹에 대한 조건을 추가하여 그룹 필터링을 했다.

 

HAVING

-GROUP BY와 함께사용

-집계함수의 결과값을 바탕으로 그룹을 필터링하고 싶을 때 사용한다.

-HAVING절에 명시된 조건을 만족하는 그룹만 결과에 포함된다.

 

Q) 각 부서별-성별 인원수를 인원수가 많은 순서대로 정렬해서 알고싶다.

SELECT dept_id,sex,COUNT(*) empl_count 
FROM employee
GROUP BY dept_id, sex
ORDER BY empl_count DESC;

결과

dept_id	sex	empl_count
sale	M	5
sale	F	4
marketing	F	3
marketing	M	2

 

Group by에 2가지 속성(dept_id, sex)을 적었다. 그리고 데이터를 나타낼 때 집계된 그룹을 나타내기 위해 dept_id, sex를 SELECT 절에 적었다. 

부서(dept_id)별로 그룹화하고 난 데이터들을 대상으로 성별(sex)로 그룹화

Q) 회사 전체 평균 연봉보다 평균연봉이 적은 부서들의 평균연봉을 알고싶다.

SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) < (SELECT AVG(salary) 
                      FROM employee);

 

Q) 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고싶다.

SELECT proj_id, COUNT(*), ROUND(AVG(salary),0)
FROM works_on W
JOIN employee E
ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id
ORDER_BY W.proj_id;

 

 

 

Q) 프로젝트에 참여한 인원이 7명 이상인 프로젝트에만 한정해서 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균연봉을 알고싶다.

틀린 예제

SELECT proj_id,
COUNT(*), ROUND(AVG(salary),0)
FROM works_on W
JOIN employee E
ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id
HAVING COUNT (*) >=7 
ORDER_BY W.proj_id;

 

이 SQL은 이미 90년대 직원으로 필터링 된 데이터들을 기반으로  집계된 그룹에 대해 HAVING 조건을 거는 것이므로 90년대생들의 수가 7명 이상인 프로젝트에  대한 데이터를 추출한게 되어버렸다.


정답 예제

SELECT proj_id, COUNT(*), ROUND(AVG(salary),0)
FROM works_on W
JOIN employee E
ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
AND W.proj_id IN ( SELECT proj_id FROM works_on
                   GROUP BY proj_id
                   HAVING COUNT(*)>=7)
GROUP BY W.proj_id
ORDER_BY W.proj_id;

그룹화되기 이전에 WHERE절에 조건을 추가하여 서브쿼리로 전체 인원이 7명이상인 프로젝트 id를 반환도록 하였다.
 그 결과 90년대생의 수와 상관없이 프로젝트 인원이 7명 이상인 프로젝트들을 대상으로 데이터가 집계되었다.